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 Tuesday, January 29, 2008 5:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
Newbie of a newbie here, I'm working on a web development degree, so not close to being a dba or anything, but wanted to hang out around people who know what they're doing to pick up tidbits.

When I took a SQL class for work, the instructor said that using SELECT * actually made the query look at the table twice, once to find out what was in it, then second to pull values for your query. He suggested picking a column, since in most cases, you're looking for existence anyway. Does that affect anything in your transaction counts? Or was he full of it?


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #449136
Posted Tuesday, January 29, 2008 6:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 322, Visits: 830
Roy, I neglected to say "nice article" in my previous post.... Thanks, I did enjoy it!

jcrawf02, that was true a long time ago, but for many years now SQL Server's optimizer can tell when a query is just checking for existence and makes no attempt to retrieve all the column values if it doesn't have to. You can see for yourself by checking query plans, they'll be identical for IF EXISTS(SELECT * from ...) and for IF EXISTS (SELECT col1 from ...) and for IF EXISTS (SELECT 1 from ...). If you're doing something else beyond simply checking for existence, however, I think your instructor's position is correct.



Post #449138
Posted Tuesday, January 29, 2008 7:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:36 AM
Points: 2,372, Visits: 6,768
Thanks Mike. Although I have around 12+ yrs of experience as DBA, I have only 3 yrs of experience in MS SQL. I was an Oracle DBA. So I am a newbie. I am learning every day. Especially when I hang around in the forums. Lots of good suggetions and ideas.
This is a new filed for me. ie writing articles. :) I hope to learn more from this discussion we are having here.
I dont remember who stated it, But even if you give a NO LOCK hint, it still has to look at the table to find out if there is a row or not. So it will have the addtional read. And Yes, you are right, if it is an Insert, I will have the same number of reads like I have for If Exists.
One more thing, Maybe I am mistaken, but when you are tyrying to do an Update, the engine will not do any exclusive locks till it finds a record that matches the criteria (Shared Lock might be what the engine does till then) and if it finds the record/records then it decided if it should escalate the lock. Am I right here? Can somebody could clarify please..


-Roy
Post #449143
Posted Tuesday, January 29, 2008 7:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Good article - great discussion :D When reading the article I thought "that makes sense". But the locking problems, coupled with the systems with which I'm associated not having millions of inserts, I'll probably stick with the IF exists check first. Can't wait for the MERGE keyword - it has so much potential for doing the insert / update / delete synchronisation of two tables splendidly. I like that instead of where you might expect a delete (when the record's not in the source table) you could, instead of coding a delete statement, code an update that sets a deleted flag instead. Of course it's possible without MERGE but I suspect it will be more efficient and, as a construct, it's quite elegant.
Shame I won't be able to use it for about 5 years since we have to wait for customers to upgrade (some are still on SQL 2k - arrrgh!)

Select * certainly is bad to return data to the client - lazy more than anything I suppose. But using select * in an exists doesn't hurt, although you'll still see many veterans argue that it does hurt and instead do things like select 1 (ie, 1 being a constant). :D



Post #449151
Posted Tuesday, January 29, 2008 11:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 31, 2008 12:28 AM
Points: 11, Visits: 5
The importance, off course, is that you should know, in advance, what it is you are trying to do. Only the business logic knows that in normal scenarios. If not, I think you're UPSERT is a nice allternative.
I myself don't think the concept of UPSERT needs further explanation. The word itself says what it is supposed to do, even the order of things to happen are OK.
Good article, keep them coming
Post #449187
Posted Wednesday, January 30, 2008 7:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:36 AM
Points: 2,372, Visits: 6,768
Thank You Ivan and Ian. I am glad you apreciate the article. It is very encouraging to get feed backs when writing articles.



-Roy
Post #449771
Posted Thursday, January 31, 2008 10:00 PM


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: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
Great article. As others have mentioned we are pessimistic here in SQL Serverville. We must always be very cautious of locking. I like to pass that decision on the app. Let the app get a result set to determine an update or an insert and call the appropriate code. My Database server is always more taxed than the app server, let them do something every once and a while
Post #450303
Posted Tuesday, February 12, 2008 1:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2008 10:33 AM
Points: 44, Visits: 24
One question I've always had on this topic is: Is there a chance of an execution plan being cached which works well for the insert but then creates problems when the update is run (or vice versa)?

For example, the first time the query is run it evaluates to an insert and the plan is cached, the next time the query is run it evaluates to an update but SS tries to use the cached "insert" plan and performance suffers.



Post #454660
Posted Sunday, March 30, 2008 10:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114
I liked the article.

Whe I am working on performance I am primarily concerned with physical I/O, not logical I/O when it comes to performance. [OK, someone tear me a new one. There ARE few occassions where logical becomes important, but not in a conventional OLTP design.]

Secondly, it is the optimizer's job to interpret the code. It should not matter whether the statement is: 1. read/insert or read/update, 2. merge, or 3. some other future variant. ALL of these should get interpreted and compiled in the exact same manner.
Post #476706
Posted Tuesday, May 11, 2010 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
As this article says many programmers do check for the existence of the record before inserting is true but its done by only those sql programmers either who are lazy to learn or they are brand new.

But the experience programmers, try to do the UPDATE first based on the key then check the updated rowcount. if its equal to zero or less than 1 based on your update query, you do the INSERT.

When I ready about the UPSERT's underlying commands it seems like its doing the same thing.

By all means, this article is a great one and let hope this will help lot of programmers as well as DBA help increasing their performance.

One note that lot of people beginners as well as experience programmers think that reading the DB for record existence doesn't cost that much but if you do look at the statistics you will come to know how much its costs.

Once again thanks for sharing this information. Great job!
Post #919585
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse