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


Importance of UPSERT


Importance of UPSERT

Author
Message
Roy Ernest
Roy Ernest
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4778 Visits: 6870
Comments posted to this topic are about the item Importance of UPSERT

-Roy
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4085 Visits: 3889
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
William Leung-294978
William Leung-294978
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 57
Hi Roy,

a new upsert command introduce in SQL2005 called "MERGE". Check it out Wink
Marian Chicu
Marian Chicu
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 231
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.
abellix
abellix
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 61
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
chrismj
chrismj
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 241
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 Smile. 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....



okie_greg
okie_greg
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 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.



sqlvogel
sqlvogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1040 Visits: 3706
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
Roy Ernest
Roy Ernest
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4778 Visits: 6870
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.Smile
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
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23876 Visits: 14905
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
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
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