Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roy Ernest » Importance of UPSERT
Add to briefcase
37 posts, Page 2 of 4
««1234»»»

Importance of UPSERT Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Mike Good
Posted Tuesday, January 29, 2008 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:09 PM
Points: 265, Visits: 636
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.


Post #448835
bitbucket-25253
Posted Tuesday, January 29, 2008 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220
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
Post #448840
Charles Kincaid
Posted Tuesday, January 29, 2008 9:01 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825
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.

ATB

Charles Kincaid
Post #448923
Paul Lach-281977
Posted Tuesday, January 29, 2008 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 09, 2010 8:11 AM
Points: 12, 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
Post #448943
sqlvogel
Posted Tuesday, January 29, 2008 9:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:00 PM
Points: 406, Visits: 2,854
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).

David
Post #448949
Jack Corbett
Posted Tuesday, January 29, 2008 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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

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
Post #448954
DBA_Rob
Posted Tuesday, January 29, 2008 9:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:26 PM
Points: 469, Visits: 256
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.
Post #448956
Charles Kincaid
Posted Tuesday, January 29, 2008 9:48 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825
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. 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.

ATB

Charles Kincaid
Post #448968
Jerry Hung
Posted Tuesday, January 29, 2008 12:35 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:16 AM
Points: 768, Visits: 1,161
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
Post #449047
Roy Ernest
Posted Tuesday, January 29, 2008 2:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 3,280, Visits: 6,623
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. :)
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. ;)

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
Post #449097
« Prev Topic | Next Topic »

Add to briefcase
37 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.