473,225 Members | 1,250 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

table constraints and performance

Could someone provide me with some information in regards to the
performance implications of applying constraints on a table? Are there
any safe guards?

The basic scenario is that there is a table that has 80% updates and
inserts and 20% selects. I would like to restrict duplicate inserts
from user double clicking or other user behavior such as refresh.

I am after advice on what the performance implications would be on
placing restraints on a table vs. writing business logic code.

Thanks,
Brian



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
2 1837
Besides the performance issues, I think it's correct to detect refreshes
before sending data to the database.

In our applications, we ignore refreshes from the begining.
We do it by sending a serial number, which we keep on a session
variable.
Every time we send a page to a client, we increment this number, update
the session variable, and send it as a hidden within the page.
When the user submits the page (and the hidden value), we check the
serial number submited is equal to the session's serial number. If it
is, we write to the database, if it's not, we just skip the writing
code.

This way, when the user refreshes the page, the session variable always
gets incremented at the server, but the value submited is always the
same.
You can implement it in an easy generic way, and you don't waste time
sending updates to the database that you know will fail anyway.

Hope it helps :)
On Mon, 2003-08-25 at 14:44, Brian Maguire wrote:
Could someone provide me with some information in regards to the
performance implications of applying constraints on a table? Are there
any safe guards?

The basic scenario is that there is a table that has 80% updates and
inserts and 20% selects. I would like to restrict duplicate inserts
from user double clicking or other user behavior such as refresh.

I am after advice on what the performance implications would be on
placing restraints on a table vs. writing business logic code.

Thanks,
Brian







---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Smp321dVnhLsBV0RAjBFAJ4tYpdHCnccUNU6FsooaN7X/ladmQCeLdW/
KOUgn/3FYuHRqPNIPhR6Bvw=
=9ICO
-----END PGP SIGNATURE-----

Nov 11 '05 #2
What Franco Bruno Borghesi says is a goos idea.
You can prevent the user from duplicate submits.

If you want to keep some fields unique in a table, please
rely on the DBMS. PostgreSQL support primary key,
foreign key, unique index and other facilities to keep
data integrity. And I think it can do things better most
of the time.
Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: martin | last post by:
Hi, We have a heavily used production server and a table which logs every hit on a web site. This table has grown large over time and we want to clear it down as efficiently as possible. We would...
4
by: Dmitri | last post by:
I just looked at a coworker's stored procedure and this person is dropping 4 Foreign key constraints and then re-adding them after processing the required logic (updating rows in the 4 tables in...
1
by: Good Man | last post by:
Hi there I'm developing a large web application. Part of this web application will be storing numerical chart data in a MySQL table - these numbers will be already calculated, and are just being...
17
by: Dr NoName | last post by:
Help! I have a table that multiple processes must be able to write to concurrently. However, it for some reason gets locked in exclusive mode. I narrowed it down to one SQL statement + some...
4
by: maricel | last post by:
I have the following base table structure - DDL: CREATE TABLE "ADMINISTRATOR"."T1" ( "C1" INTEGER NOT NULL ) IN "TEST_TS" ; ALTER TABLE "ADMINISTRATOR"."T1" ADD PRIMARY KEY
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
3
by: das | last post by:
Hello all, Can someone help me with this SQL? 1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and other columns 2) EMPLOYEE_BENEFITS table has a column called employee_entity, this...
3
by: Jeff Kish | last post by:
Hi. I'm getting errors like this when I try to run an upgrade script I'm trying to write/test: altering labels to length 60 Server: Msg 5074, Level 16, State 4, Line 5 The object...
5
by: yeoj13 | last post by:
Hello, I have a db2load script I'm using to populate a large table. Ideally, my target table is required to have "Not Null" constraints on a number of different columns. I've noticed a ...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.