Which is the best choice in delete-insert vs if-update else-insert?

Question

Update:

My bad...I have an primary key on those tables..I meant no further indexing currently on the tables. We might have it in the future after seeing the performance and since we have too many filters on the data in retrieving data it did not show much improvement on indexing last time we ran database tuning.

I have a 4 huge tables over millions of records. Now there is stored procedure which is called frequently and updates these table. Here is the scenario -

Now if entry exists for today I need to update it for today and else if entry is not there for the user I need to go ahead and insert an entry for the user. Now there are two ways to go about carrying out these since there is a single proc that does this -

First Way -

IF EXISTS(TABLE1)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE2)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE3)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE4)
--UPDATE where condn
ELSE
--INSERT

Second Way -

DELETE from TABLE1 where condn
DELETE from TABLE2 where condn
DELETE from TABLE3 where condn
DELETE from TABLE4 where condn

INSERT TABLE1 ENTRY
INSERT TABLE2 ENTRY
INSERT TABLE3 ENTRY
INSERT TABLE4 ENTRY

Now the second way looks simpler but it might be more time consuming ...I am not sure which way is the best here. Can anyone please help or guide me out here..thanks!

As MERGE is unavailable in SQL2005 (thx to those who commented on that), I would prefer the first way. Worst case scenario for second way coult be that you have an identity as a clustered index, resulting in at least two write operations for each change. — Lieven Keersmaekers, Dec 02 '10 at 19:41
OP says: `There is no indexing currently on he tables. We might have it in the future.` and `4 huge tables over millions of records`. Please note that a Primary key gives you an index. If you have no PK, then I say, **the future is now!** in the name of performance PLEASE add an index that will help your `where condn`! — KM., Dec 02 '10 at 20:07
If you delete and insert, SQL Server needs to modify the Primary Key index every time. If you only update, the index can be left alone. That's four write operations (delete row, delete index entry, create row, create index entry) vs one (update row). — Tomalak, Dec 03 '10 at 09:54

score 8 · Answer 1 · edited May 23 '17 at 11:54

8

If you expect mostly inserts, try this

...
BEGIN TRY
   INSERT table1
END TRY
BEGIN CATCH
   IF ERROR_NUMBER = 2627
       UPDATE table1
   ELSE
       --process real error
END CATCH
...

Mostly updates...

...
BEGIN TRY
   UPDATE table1 ... WHERE ...
   IF @@ROWCOUNT = 0
      INSERT Table1
END TRY
BEGIN CATCH
   --optional. if someone manages to insert before here, do we update it? or just ignore it?
   IF ERROR_NUMBER = 2627
       UPDATE table1
   ELSE
       --process real error
END CATCH
...

EXISTS isn't concurrent enough under high loads. If you are going to scan the table with EXISTS, you may as well just try the INSERT anyway.

Other answers: One, Two, Three

Edit: I call this the JFDI pattern...

edited May 23 '17 at 11:54

Community

1
1

answered Dec 02 '10 at 19:39

gbn

422,506
82
585
676

I get this error when inserting first :( "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The statement has been terminated." – andrecarlucci Sep 27 '17 at 18:11
@andrecarlucci you maybe have SET XACT_ABORT ON or a trigger that "dooms" the transaction. You should use MERGE in later versions of SQL – gbn Sep 28 '17 at 08:25
@gbn Thanks a lot! – andrecarlucci Sep 29 '17 at 13:02

Tomalak · Accepted Answer · 2010-12-03T10:21:37.850

The if-exists-then-update-else-insert approach may be more code than delete-insert, but (depending on how many and what kind of indexes are defined on the tables) it is a lot less work for the server.

A DELETE or INSERT operation requires every index to be modified, period.
An UPDATE operation only requires these indexes modified whose fields have been updated in this instance.

So unless you modify every indexed field of the record with your updates, the longer approach is the more efficient one.

EDIT: Your question update says that currently you do not have any indexes apart from the primary key (which I assume is a clustered key). So in comparison:

When the row already exists, it's 1 clustered index seek (assuming you find records by their PK) and:

delete-insert: 4 write operations (delete row, delete PK index row, insert row, insert PK index row)
check-update/insert: 1 write operation (update row)

When the row does not yet exist, it's 1 clustered index seek and:

delete-insert: 2 write operations (insert row, insert PK index row)
check-update/insert: 2 write operations (insert row, insert PK index row)

The more indexes exist, the worse will delete/insert perform for rows that already exist. Plus it can cause non-clustered indexes to become unnecessarily fragmented due to avoidable write operations.

score 1 · Answer 3 · answered Dec 02 '10 at 19:39

try this:

UPDATE TABLE1 where condn
IF @@ROWCOUNT=0
    INSERT TABLE1....

UPDATE TABLE2 where condn
IF @@ROWCOUNT=0
    INSERT TABLE2....

UPDATE TABLE3 where condn
IF @@ROWCOUNT=0
    INSERT TABLE3....

UPDATE TABLE4 where condn
IF @@ROWCOUNT=0
    INSERT TABLE4....

not sure if this would work (question does not provide enough info):

UPDATE TABLE1 where condn
IF @@ROWCOUNT=0
BEGIN
    INSERT TABLE1....
    INSERT TABLE2....
    INSERT TABLE3....
    INSERT TABLE4....
END
ELSE
BEGIN
    UPDATE TABLE2 where condn
    UPDATE TABLE3 where condn
    UPDATE TABLE4 where condn
END

Which is the best choice in delete-insert vs if-update else-insert?

3 Answers3

Linked