Thread: 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
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:
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
Attachment
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.