insert/update operation in same Stored Procedure is preferred or not ?

Question

insert/update operation in same Stored Procedure is preferred or not ?

anjali.vishwakarma

Ten Centuries

Points: 1112
More actions
September 10, 2008 at 7:01 am

#193335

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 ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 1

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

Matt Cherwin Ten Centuries Points: 1313 More actions · Answer 2

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 4

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 6

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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.