0

Im working on an application which access a Sybase ASE 15.0.2 ,where the current code access a remote database (CIS) to insert a row using a proxy table definition (the destination table is a DOL - DRL table - The PK row is defined as identity ,and is always growing). The current code performs a select to check if the row already exists to avoid duplicate data to be inserted.

Since the remote table also have a PK definition on the table, i do understand that the PK verification will be done again prior to commiting the row. Im planning to remove the select check since its being effectively done again by the PK verification, but im concerned about if when receiving a file with many duplicates, the table may suffer some unecessary contention when the data is tried to be commited.

Its not clear to me if Sybase ASE tries to hold the last row and writes the data prior to check for the duplicate. Also, if the table is very big, im concerned also about the time it will spend looking the whole index to find duplicates.

I've found some documentation for SQL anywhere, but not ASE in the following link http://dcx.sybase.com/1200/en/dbusage/insert-how-transact.html

The best i could find is the following explanation

https://groups.google.com/forum/?fromgroups#!topic/comp.databases.sybase/tHnOqptD7X8

But it doesn't enlighten in details how the row is locked (and if there is any kind of optimization to write it ahead or at the same time of PK checking) , and also if it will waste a full PK look if im positively inserting a row which the PK positively greater than the last row commited

Thanks

Alex

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
Alexandre Hadjinlian Guerra
  • 11
  • 3
  • Have you looked at the docs here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase15.cisug/html/omni_ug/BABFDDHD.htm – Mike Gardner Jun 03 '13 at 13:37

2 Answers2

0

Unlike SqlAnywhere there is no option for ASE to set wait_for_commit. The primary key constraint is checked during the insert and not at the commit time. The problem as I understand from your post I see is if you have a mass insert from a file that may contain duplicates is to load into a temp table , check for duplicates, remove the duplicates and then insert the unique ones. Mass insert are lot faster though it still checks for primary key violations. However there is no cost associated as there is no rolling back. The insert statement is always all or nothing. Even if one row is duplicate the entire insert statement will fail. Check before insert in more of error free approach as opposed to use of constraint to the verification because it is going to fail and rollback is going to again be costly.

kristian
  • 22,731
  • 8
  • 50
  • 78
Raju
  • 1
0

Thanks Mike The link does have a very quick explanation about the insert from the CIS perspective. Its a variable to keep an eye on given that CIS may become a representative time consumer if its performing data and syntax checking if it will be done again when CIS forwards the insert statement to the target server. I was afraid that CIS could have some influence beyond the network traffic/time over the locking/PK checking

Raju I do agree that avoiding the PK duplication by checking if the row already exists by running a select and doing in a batch, but im currently looking for a stop gap solution, and that may be to perform the insert command in batches of about 50 rows and leave the duplicate key check for the PK. Hopefully the PK check will be done over a join of the 50 newly inserted rows, and thus avoid to traverse the index for each single row...

Ill try to test this and comment back

Alex

Alex
  • 1
  • 1