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 ««12345»»»

Best Practice Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 5,916, Visits: 8,166
davidr (9/4/2008)
side note: we shouldn't really be doing "select *" to check for existence. If that's a LOONNNNGGGGG row there could be a lot of overhead to no good purpose. It may be more self-documenting to code "select 'exists' from ....."


Hi David,

I a stand-alone SELECT statement, I would agree. But in the context of a [NOT] EXISTS subquey, it really doesn't matter a bit what you put behind the SELECT - whatever you write there, SQL Server will interpret it as checking for existance of a row, and perform the check in the most efficient way.

Personally, I prefer SELECT * as it indicates checking for a row, not checking for a specific value or so. But that's just personal preference. From a performance viewpoint, there is absolutely no difference.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #563788
Posted Thursday, September 4, 2008 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 5,916, Visits: 8,166
Christian Buettner (9/4/2008)
I have adjusted my post above to remove the wrong information.


.... that I seem to have overlooked thus far. And now I find myself wondering what it is that you have written.

Sad, isn't it? :D



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #563791
Posted Thursday, September 4, 2008 7:41 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
Thanks guys for the feedback.

davidr (9/4/2008)
side note: we shouldn't really be doing "select *" to check for existence. If that's a LOONNNNGGGGG row there could be a lot of overhead to no good purpose. It may be more self-documenting to code "select 'exists' from ....."


Long time ago there was a big discussion on T-SQL forum about using SELECT * in EXIST checks.
Despite I use always SELECT 1 (just habit) it was proven with many tests that beginning from version 2000 there is no difference what to put into SELECT.
When it's for existence check SQL Server does not actually do the SELECT itself. Only FROM and WHERE.
Post #563807
Posted Thursday, September 4, 2008 7:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 1,182, Visits: 1,970
Hugo Kornelis (9/4/2008)

Personally, I prefer SELECT * as it indicates checking for a row, not checking for a specific value or so. But that's just personal preference. From a performance viewpoint, there is absolutely no difference.

I prefer to use SELECT NULL vs. SELECT * or SELECT 'x' or SELECT 1 for my existance tests. Selecting a NULL is a bit more indicative that nothing is being selected.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #563808
Posted Thursday, September 4, 2008 7:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:40 AM
Points: 1,938, Visits: 208
Hi Chris and Hugo,

Thanks for your responses!

Joe
Post #563818
Posted Thursday, September 4, 2008 7:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 1,182, Visits: 1,970
AJ (9/4/2008)
Good question, but I think it is being confused, including by me, with an UPSERT (as the previous posts' referral to MERGE indicates too).

This question needs more information to answer correctly: How is the action dealt with if the INSERT is not performed? An UPDATE instead? Maybe we actually want a unique constraint on the Name column to report an error if a name is attempted to be inserted twice? If we do need to update instead if the record already exists, how do we control concurrency in that case?

In high concurrency designs these and many more questions must be answered first and I completely agree a more deterministic approach would be required.

A big misconception I keep coming across is that there must be a perfect singleton solution for all situations.... but why not perfect the solution for each singleton situation? A welcome side-effect of this is that it keeps us all in jobs!


I agree. I think that the question should have stated "what is the most efficient way..."

Depending upon what is to happen if the value already exists, would also possibly influence the choice of method to be used. In this simple example with only one column, I note the following (performance and locking issues aside):
1. The first option (IF statement) has the capability of returning information back to the UI to inform the user that the value already exists.
2. Option 2 would have to check the ROWCOUNT from the insert to see if the row was inserted and then pass the appropriate information back. Otherwise, the application would [wrongly] assume that the row was added. So we're still coding an IF statement.

And I wouldn't, in this simple case, use the new SQL Server 2008 MERGE statement to update a value to itself.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #563826
Posted Thursday, September 4, 2008 8:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
A GREAT QoD! I got it wrong, then read how obvious the answer was. It's amazing the things you don't take time to think about. This will change my practice.

I've done SELECT 1 for years out of habit. I never saw SELECT NULL used. That's pretty cool. I might switch.

Thanks for the tidbit Sergiy.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #563856
Posted Thursday, September 4, 2008 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 1,182, Visits: 1,970
Tom Garth (9/4/2008)
... I never saw SELECT NULL used. That's pretty cool. I might switch.

I discovered the SELECT NULL syntax by observing the internal SQL statements issued by Oracle when it is checking FK constraints. So if it was good syntax for the engine, then it must be good for us programmers.


Side note: Both DB2 and Oracle, both architected from the same original System R specification, use SQL to run the engine. This was a good thing, in that it forced optimization as well as enhancements to the SQL language.

One of the first things that DB2 encountered was heavy lock contention and deadlocking on its own internal dictionary tables as it was taking page-level locks. The next release (circa 1984) had row-level locking.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #563874
Posted Thursday, September 4, 2008 8:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:15 AM
Points: 1,426, Visits: 332
I'm not completly agree with the answer, because depends of the context:
If you are looking for the best execution plan the answer is #1
If you are looking for less deadlock the answer is #2
But if the target is INSERT a new record, both solutions works.
Post #563883
Posted Thursday, September 4, 2008 9:03 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 684, Visits: 617
Great question! I have seen many co-workers create queries like this that work really well when all of the data can be copied onto memory on their test but when it get to the big production server it brings the system to a halt locking huge numbers of records and if let run long enough causing a huge rollback.
Post #563890
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse