September 10, 2008 at 7:01 am
Is there any issue if I write Insert and Update Operations on a table in same Stored Procedure ? I do the insert operation if primary key parameter passed is a negative value otherwise I do the Update Operation.
What problems it can cause ?
September 10, 2008 at 7:23 am
It's not a huge deal to do this, but it will result in recompiles on the procedure since the execution plan for an UPDATE and one for an INSERT are different.
When I'm writing one of these what I do is write three procs. One is a wrapper that receives all the data and then based on what was received it calls one of the two others either doing an insert or update.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2008 at 8:16 am
Grant Fritchey (9/10/2008)
It's not a huge deal to do this, but it will result in recompiles on the procedure since the execution plan for an UPDATE and one for an INSERT are different.When I'm writing one of these what I do is write three procs. One is a wrapper that receives all the data and then based on what was received it calls one of the two others either doing an insert or update.
I had never thought of the issue of recompiles, but now that you say it, it's kind of obvious.
Thanks!
______
Twitter: @Control_Group
September 10, 2008 at 8:23 am
I always write an upsert statement if it is based on primary key. Blindly update the table with new values (Primary key is the Where Clause). If Rowcount is 0 then I insert.
-Roy
September 10, 2008 at 8:26 am
Yeah, you can do that without recompiles. Does it cause excessive scans or reads in the table since it's searching for a PK that doesn't exist every single time?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2008 at 8:30 am
I havent seen much scan. I saw that the performance was pretty good for it. The main reason I came up with the idea was because the developers were doing an IF Exists to find out if it is an Update or not. That would mean an additional read in the case of on Update statement. Since I am doing a blind update and if it actually updates, that means I will have one less read (If Exist Read). But if it is an Insert, it is doing almost the same read as If Exist I guess.
PS: I did write an article regarding this here. That was my first try at articles. So it is a bit lousy 😛
-Roy
September 10, 2008 at 8:47 am
Without a doubt it's going to be better than reading twice every time, definately. I've had to fix that problem WAY to often myself. I was just curious how many reads you might be getting for trying to find a key that's not there? May be only a little.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2008 at 8:56 am
It is way low. The only thing I did not dig into much is is there will be Locking over head when it is trying the Update even though there is no row.
-Roy
September 10, 2008 at 8:29 pm
I initially wrote my import SPs to use a flag that was set to check existence and then called one SP for inserts and another one for updates. Ultimately it was much faster to do the Upsert. Since each batch of data always has both inserts and updates, I don't check the rowcount of the update before doing the insert. I just updated where the PK exists and then insert where it doesn't.
Gary Johnson
Sr Database Engineer
September 10, 2008 at 9:55 pm
Anybody got 2008 installed to test the new merge command and do a case study to see what
solution(s) seem to be the best?
My first instinct tells me that doing the blind update is not going to be very costly. In my DBs, the data is inserted once, then updated 5-10-15 times overs. Since the update is the first statement, it'll be the only statement to run 80%-90% of the time. In my case recompiles wouldn't be an issu, but I can imagine that the merge command could beat any other alternatives... and be much easier to code and maintain on the long run.
It also gives the advantage of running in a single pass, so blocking, locking and all that crap is not an issue anymore.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply