November 24, 2009 at 5:04 am
Hi,
We've been doing some store analysis and found on some stores the following code:
IF EXISTS (SELECT 1 FROM table WHERE col0 = @col0 AND col1 = @col1)
UPDATE table SET colN = @colN, colM = @colM WHERE col0 = @col0 AND col1 = @col1
It's "beautiful" for the eye... See if first exists and only then do the update... (typical from c# programmers... see if a object exists before updating or the code will throw an exception)
But isn't this "redundant" in SQL?
Shouldn't the UPDATE be enough, since it does the same index seek than the SELECT and if the record exists updates the values?
From what I see from the statistics io result
Table 'main'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'main'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
from the IF / UPDATE it's heavier... The UPDATE only has one line equals to those... The IF / UPDATE also has just 1 line if the key doesn't exist on the table....
So from where I stand (DB point of view) the just UPDATE is better...
IF/UPDATE | UPDATE
EXISTS 4 reads 2 reads
NOT EXISTS 2 reads 2 reads
Thanks,
Pedro
November 24, 2009 at 5:29 am
The UPDATE is enough. There's no need for the EXISTS test, it's just doing things twice and slowing down operations.
-- Gianluca Sartori
November 24, 2009 at 5:41 am
Yep,
We are already "sending out" the message to everyone... We are building a document on "good practices".
The only case when IF EXISTS should be used, in our case, is when we have to check on a table and perform operations on multiple tables.
Or when performing an INSERT or UPDATE instead of using MERGE
Pedro
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply