EXISTS

Question

EXISTS

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 1

handkot (2/21/2010)
it's my solution
first step - we try to update record
secod step - if no record have been updated then we insert record
update Table
set field1 = @field
where field2 = @key
if @@rowcount=0
begin
insert into Table()field1, field2) Values(@field, @key)
end

If the row gets inserted between the UPDATE and the INSERT then this code will cause a key violation. The point of using MERGE is that it doesn't require multiple statements. MERGE is a single atomic unit of work - despite its rather ugly syntax.

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

Id definitely be on the MERGE bandwagon if I could. Problem is, while working through this issue, I discovered I'm going to have to deploy it on SQL 2005, so MERGE will not work for me.

(I know, I should have posted this to the 2005 forum, but I didn't figure that part out until Id already started this thread).

I think Paul's EXISTS with locking hints is my best bet.

Thanks!!

.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

handkot (2/21/2010)
it's my solution
first step - we try to update record
secod step - if no record have been updated then we insert record
update Table
set field1 = @field
where field2 = @key
if @@rowcount=0
begin
insert into Table()field1, field2) Values(@field, @key)
end

This is what I used to call the "Hammerhead Technique", ie, do it first, then see if it worked, keep trying (possibly different ways) until it does work.

Unfortunately, your example is not 100%, because it is still possible that someone else executing the same code would get to their INSERT first, and then yours would fail. There are two ways that you could fix it:

1) Reverse the order of your UPDATE and INSERT statements.

As long as there are no deletes, this will always work. However, it may be inefficient, if UPDATES are more commonly correct than INSERTS. Let's say that INSERTs are needed 20% of the time and UPDATEs are needed the other 80%. Then, since UPDATEs count as two operations (a DELETE, then an INSERT), the average operations cost of this approach would be:1.0*1(for attempted INSERT) + 0.8*2(for UPDATE) = 2.6 operations.

or

2) Add another UPDATE attempt at the end.

This may be more efficient. Using the same assumptions as above, plus assuming that an INSERT collision will only occur 1% of the time, then its average cost would be:

1.0*2(for attempted UPDATE) + 0.2*1(for INSERT) + 0.01*2(for collisions) = 2.42 ops

Of course, testing & measuring might be better...:-)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 4

Barry,

How would reversing the statements work? An INSERT might throw an exception if a key value already exists. Wrapping it in TRY...CATCH would work, but you still have to be very careful with concurrency issues, and TRY...CATCH is not for free. Writing code that relies on exceptions always grates on me too.

Adding another UPDATE seems perverse to me - and it could still fail, of course. Handling the locking and concurrency issues properly seems infinitely preferable. See my previous code using EXISTS.

Another problem with the posted method is in the use of @@rowcount. Triggers can really mess this logic up!

I would encourage anyone who thinks the @@rowcount method is a good idea to read Alex Kuznetsov's blog entry that I linked to earlier.

My preference is for the EXISTS method. MERGE has some subtleties to it, and is not bullet-proof. Most times, unless there is a clear advantage to using MERGE, I go with EXISTS.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Paul White (2/21/2010)
Barry,
How would reversing the statements work? An INSERT might throw an exception if a key value already exists. Wrapping it in TRY...CATCH would work, but you still have to be very careful with concurrency issues, and TRY...CATCH is not for free. Writing code that relies on exceptions always grates on me too.

Heh. Well, I'm not recommending it, I'm just noting it as an option, personally I prefer explicit transactions for data and applications that I care about. You're right though, I did forget about needing to catch the exception, my bad. As for the additional cost of a TRY..CATCH, I confess that I do not know exactly what it is, but I thought it was normally a small amount of CPU & memory, which should be much less time than physical IOs.

And relying on exceptions? Well, that's Hammer-heading in a nutshell, plow ahead now and deal with the problems later.

Adding another UPDATE seems perverse to me - and it could still fail, of course. Handling the locking and concurrency issues properly seems infinitely preferable. See my previous code using EXISTS.

Yeah, Hammer-heading seems to get that reaction from contemporary developers, but it was a thing back in the day. Probably because good facilities to check things ahead of time were either too spotty or too slow and the non-exception paths were not so much faster than the exception paths (its a staggering difference for .Net).

Another problem with the posted method is in the use of @@rowcount. Triggers can really mess this logic up!
I would encourage anyone who thinks the @@rowcount method is a good idea to read Alex Kuznetsov's blog entry that I linked to earlier.

Ouch! I feel really bad about this one, I really should have caught that, probably posting too much today. 🙁 Guess I made a right pig's breakfast of it ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 6

Barry,

I understand where you're coming from - and you certainly didn't make a pig's breakfast of it - I'm just conscious that not everyone reading the thread has your level of knowledge and expertise. Sometimes I post for the general audience, even though it looks like I am responding to an individual.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Thanks Paul. Still, I got a chance to use some empire slang... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 8

True.

Is it just me and you on SSC tonight?

Or are we both suffering from post-itis?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Just us I think. But someone's got to keep the lights on, right? 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 10

Right. 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi