February 10, 2011 at 4:34 am
Hi All,
Can any body please explain ,While Checking the existance of records using "exists". Which method will be more sufficient and WHY ?
“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.
Thanks & Regards
Deepak.A
February 10, 2011 at 4:48 am
In Theory, it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.)
I seem to remember someone doing a test in SQL2000 which showed that SELECTing a constant was marginally quicker than SELECT * as SQL2000 seemed to look up the column names. I have no idea if this is still true with SQL2008.
A lot of development shops specify that you should use SELECT 1, in EXISTS sub-queries, so they can easily check for lazy developers putting SELECT * in the rest of their code.
February 10, 2011 at 5:00 am
http://www.sqlservercentral.com/Forums/Topic786650-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx
Marginal differences, it would appear.
BrainDonor.
February 10, 2011 at 5:45 am
No difference. No matter what you use, it's removed early in the parsing stage. I use SELECT 1 to make it extremely clear that the exists is not returning any columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2011 at 6:18 am
Hi GilaMonster,
Thanks for reply,if we use columns or * will it make any performance difference? and also
using top 1 1 instead 1 will give better peroformance ?
Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)
using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?
Thanks & regards
Deepak.A
February 10, 2011 at 6:29 am
There are the same , count(1) get converted to count(*) by the optimizer / parser.
See this post on my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx
February 10, 2011 at 6:58 am
deepak.a (2/10/2011)
Thanks for reply,if we use columns or * will it make any performance difference? and alsoLike ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)
No and no
using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?
Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.
Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2011 at 2:28 am
Dave Ballantyne (2/10/2011)
There are the same , count(1) get converted to count(*) by the optimizer / parser.See this post on my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx
thanks dave for your reply and sharing nice article
February 11, 2011 at 2:29 am
GilaMonster (2/10/2011)
deepak.a (2/10/2011)
Thanks for reply,if we use columns or * will it make any performance difference? and alsoLike ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)
No and no
using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?
Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.
Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.
Thanks a lot Gail Shaw for your reply
February 11, 2011 at 7:23 am
My two cents worth: I use 'SELECT Null FROM ...' when using an EXISTS clause. As Gail has pointed out, the speed difference is probably trivial at best, more likely non-existent, but it emphasizes (to me, anyway) that I'm not looking for any data in such a phrase, just the bare existence of at least one record matching certain conditions.
February 11, 2011 at 1:48 pm
I also use SELECT NULL.
Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 11, 2011 at 4:31 pm
February 12, 2011 at 1:17 am
luckysql.kinda (2/11/2011)
Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1) should help
Not really. Firstly there's no cost analysis there, no testing, no numbers, nothing other than the blogger's untested opinion, and one of the queries he lists is logically different from the others.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2011 at 1:20 am
amenjonathan (2/11/2011)
I also use SELECT NULL.Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.
The point is, it's not significantly faster, it's a couple of nanoseconds at best. It's micro-optimisation, it's a waste of time for no value.
Use EXISTS (Select *, EXISTS (Select 1, EXISTS (Select NULL, or any other form as they are not going to make anything resembling a noticeable difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 9:25 am
GilaMonster (2/12/2011)
amenjonathan (2/11/2011)
I also use SELECT NULL.Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.
The point is, it's not significantly faster, it's a couple of nanoseconds at best. It's micro-optimisation, it's a waste of time for no value.
Use EXISTS (Select *, EXISTS (Select 1, EXISTS (Select NULL, or any other form as they are not going to make anything resembling a noticeable difference.
If I'm typing it out for the first time and I type SELECT NULL instead of SELECT *, I don't see how that wastes any time. Yes it does not provide a lot of value, but hey a tiny amount of value for free is still better than no value for free. Also, again this is just the way I do things, it helps me stay vigilant with optimization.
Again my motto is why not do what's most optimal if it doesn't require any additional work?
-------------------------------------------------------------------------------------------------
My SQL Server Blog
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply