Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Importance of UPSERT Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 11:58 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: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Comments posted to this topic are about the item Importance of UPSERT

-Roy
Post #448700
Posted Tuesday, January 29, 2008 12:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 2,814, Visits: 3,851
Hello Roy,

thanks for the article, definitively a good topic.
I only fear that the explanation of UPSERT itself comes a little too short.
You focus very much on the explanation of the performance impact but do not explain what UPSERT actually means.

The conclusion seems to be the first place where the rowcount check is mentioned (despite in the code) and for the people that are new to this topic, this should be too late.


Best Regards,
Chris Büttner
Post #448704
Posted Tuesday, January 29, 2008 1:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 7:52 PM
Points: 3, Visits: 57
Hi Roy,

a new upsert command introduce in SQL2005 called "MERGE". Check it out ;)
Post #448714
Posted Tuesday, January 29, 2008 1:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:43 AM
Points: 11, Visits: 152
Hello Roy,

My only comment is that in the script at the beginning the table you create is called tbTicket but the table you use is tbTicketing.

Thanks for the article.

PS: for William, according to this article http://blog.benhall.me.uk/2007/06/sql-server-2008-sql-merge-statement.html the Merge statement was pulled of from SQL2005 and moved on to 2008.


I'd cry, but I can't stop laughing.
Post #448717
Posted Tuesday, January 29, 2008 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 14, 2010 6:47 AM
Points: 6, Visits: 47
I think that "UPSERT" and "IF EXISTS" are usefull only when the TicketNumber column is not auto-generated by identity column (as all best practices suggest).
With identity column, you are inserting when parm3 is undefined, updating when parm3 is defined. So I can't see a way to apply an UPSERT in the most applied scenario.

Is there a way to apply usefully an UPSERT when the table has an identity column?
Thanks for attention and for your interesting article

Alessio Bellisomi
Post #448734
Posted Tuesday, January 29, 2008 2:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 4:37 AM
Points: 241, Visits: 173
You conclude that you should do the UPDATE first. "The best strategy is to attempt the update."
But with any SQL Server question, it depends :). In a heavy transaction load system, where you primarily are doing updates or an equal mix of updates and inserts, your conclusion is correct, but if you primarily are doing inserts, then you have a large overhead attempting an update for each insert, say you have a couple of hundred updates for each 100.000 inserts. In that case you should attempt the INSERT first, and then the UPDATE if it already exists, however without using IF EXISTS. You should use a TRY...CATCH block checking for a primary key violation.

TRY
INSERT....
CATCH
IF ERROR_NUMBER() =
UPDATE....
ELSE
RAISERROR....



Post #448736
Posted Tuesday, January 29, 2008 3:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 20, 2009 10:16 AM
Points: 525, Visits: 26
Roy

The concept of using an upsert is interesting. What if the update failed and the insert was executed instead? Then you would have the same read profile whether using upsert of the if exists method, correct? Also, with the use of if exists, one could use a nolock locking hint in order to reduce blocking in thier highly transactional database where when using the method described, we are guaranteed a read lock regardles of whether the updates succeeds or not, correct?

Just some thoughts, and verifiable answers are much appreciated.




Post #448745
Posted Tuesday, January 29, 2008 4:57 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 429, Visits: 3,082
SQL Server 2008 users should definitely consider using MERGE. MERGE will avoid having to do multiple scans/seeks in many cases.

http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx


David
Post #448765
Posted Tuesday, January 29, 2008 5:14 AM


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: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Hi All,

Thanks for all the input you have provided. Since you all read it it means I did something to make you think of different options that is possible.:)
Using of Upserts will have its use in quite a few scenarios, but not all.
I am not sure why you cannot use Upserts when the Primary Key is Identity. You will be updating based on the primary Key but you are not updating the Primary key value. You will update the other Columns in the row that matches the Key.
My sincere apologize for the Type in the Script. (Using tbTicketing)
Please do give your comments and feed back. Then I tool learn. Seems like Merge is something to be checked out in 2008.
One again thanks for all the Inputs.

Roy


-Roy
Post #448770
Posted Tuesday, January 29, 2008 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 10,907, Visits: 12,540
Did you check to see the type of locks being taken by the 2 methods? Were there any differences in locking that would mitigate the improvement based on fewer reads?



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

Add to briefcase 1234»»»

Permissions Expand / Collapse