SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importance of UPSERT


Importance of UPSERT

Author
Message
Mike Good
Mike Good
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1691 Visits: 1042
I agree with Jack Corbett's post, you need to look at the locking impact. Many years ago a team I worked with came to the opposite conclusion due to the extra locks we encountered with update 1st, insert 2nd approach. That was SQL7, I'm sure the analysis is no longer valid. Easy to check in profiler (be sure to add the extra profiler columns ObjectID, ObjectID2, IntegerData2, Type). Such analysis might support the author's claim, but definitely needs to be considered.

I don't like the READUNCOMMITTED hint. There is no TRY/CATCH used here, so if two connections attempt to upsert same new row at approximately the same time, 1st one will succeed, and 2nd one will abort on failed insert. Either need to CATCH that failed insert, or else take steps to ensure 2nd connection is blocked from working on the same row that the 1st connection is working on.

In old days before TRY/CATCH, a robust solution would have mandated that we wrap the upsert code within a transaction. Seems like classic case that calls for a transaction, right? Multiple sequential SQL stmts that form a single unit of work. Now with TRY/CATCH it might be better to attempt the INSERT first, as chrismj's post points out, and I believe this may eliminate the necessity for the explicit transaction? I haven't fully absorbed impacts of TRY/CATCH, and old habits die hard...I'll have to play around with this. Also, I wonder if there is a cost of failing into the CATCH block that needs to be considered? I've never read about this or how to measure it...maybe a stopwatch would suffice.

MERGE should render all this moot, wish it were already here.



bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15653 Visits: 25280
Roy,
Thanks for waking most of us up, and getting us to think and review the habits we have fallen into .. not only with the use of IF EXISTS

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3275 Visits: 2384
I'm not a big fan of MERGE. I much prefer REPLACE INTO. You code everything one way. It looks just like a regular INSERT statement except for the word REPLACE. The match is done on the primary key (you don't use auto number fields as primary key, right?). Rows affected comes back 1 if it worked, 0 otherwise. Neat and clean.

If I know that I want to update I use UPDATE and check Rows Affected. If I know this is a new record I use INSERT and let the collision prevent duplicates. There are times when I have to update and/or insert a stack of data and I don't want to mess around about it. REPLACE INTO cuts my work load.

ATBCharles Kincaid
Paul Lach-281977
Paul Lach-281977
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 170
I believe your UPSERT analysis is interesting, but too simplistic for 2 major reasons:

1. the effect of locks (UPDATE uses a more restrictive lock than IF EXISTS)
2. the effect of triggers (an UPDATE will fire the trigger(s) even if no record is updated

Paul Lach
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 3706
Interesting. MERGE is standard SQL whereas REPLACE INTO is product-specific (MySQL. Any others?).

As far as I understand it, the most important difference is that REPLACE INTO automatically matches on the column(s) declared as PRIMARY KEY. I definitely don't like the idea of treating the PRIMARY KEY differently from other candidate keys. Put it another way, I prefer features that can work equally well with ALL keys - without unnecessary restrictions.

MERGE also has some other nice features such as extra predicates in matching clause and the DELETE clause (see my blog for an example).
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44343 Visits: 14925
Charles Kincaid (1/29/2008)
I'm not a big fan of MERGE. I much prefer REPLACE INTO. You code everything one way. It looks just like a regular INSERT statement except for the word REPLACE. The match is done on the primary key (you don't use auto number fields as primary key, right?). Rows affected comes back 1 if it worked, 0 otherwise. Neat and clean.

If I know that I want to update I use UPDATE and check Rows Affected. If I know this is a new record I use INSERT and let the collision prevent duplicates. There are times when I have to update and/or insert a stack of data and I don't want to mess around about it. REPLACE INTO cuts my work load.


REPLACE INTO would be great, but it doesn't exist in SQL Server. This is what MERGE is supposed to do.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
DBA_Rob
DBA_Rob
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 354
Certainly this is a good option, but as several others stated there is a MERGE statement coming out in SS 2008 which may make this obsolete. An example of MERGE can be found on SSC at http://www.sqlservercentral.com/articles/Advanced+Querying/3122/

I can say that the MERGE on Oracle performs very well so I'm looking forward to the SQL Server implementation.
Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3275 Visits: 2384
REPLACE INTO would be great, but it doesn't exist in SQL Server. This is what MERGE is supposed to do.


I'm just moaning about what I can't have. Sad As a developer/DBA I do as much as I can to take the work off of the server. Some times it's nice to have SQL take the work off of me. I like to preprocess data and funnel the data through one pipe. That way I have fewer places to look when things go wrong.

I get into projects that interface with lots of databases. I like MySQL REPLACE INTO and hate the way they are so picky about string to date conversions. SQL Server will take pretty near anything for a date string without complaint but does not support REPLACE INTO.

I'm not saying take MERGE away, I would just like the other goody added.

ATBCharles Kincaid
Jerry Hung
Jerry Hung
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3438 Visits: 1208
Interesting

More interesting if you did a batch insert test and IF EXISTS vs. UPSERT
1 statement isn't enough to prove the point

If UPSERT can reduce 50% read and 50% overall time, say in a million records operation, WOO HOO!
Well, I doubt I'll see MERGE in our Production anytime soon (since SQL 2008 isn't official anytime soon either)

SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8559 Visits: 6891
Thanks again for all the input. This is my first article. So it makes me happy that it is being read and is also being discussed. Smile
I will have to check the Locking in both the cases and make a comparison. I never thought about that.
I read about Merge. I have not used it and unfortunately I wont be using it for another 2 yrs at least I think.
Just for clarification, I wrote a simple Upsert command for just showing the idea. I did not do any error handling or transactions.
I know that lots of people do not like using with (readuncommitted). But for us, we use it religiously. That is basically to reduce the Locking that SQL Server has to do. We know the risk of getting incomplete transactions on our queries. But we are willing to look the other way. Speed is our main concern. Wink

Once again Thanks for all the input and keep it coming. With every input coming from you, it opens my mind to different ideas and I learn more.

Roy

-Roy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search