January 15, 2015 at 12:51 pm
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it (a search feature on there would be so helpful). Anybody know where such an article disproving that myth can be found? My google-fu is being elusive today.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
I believe that is what you are seeking. Links to other articles are there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 15, 2015 at 12:56 pm
SQLRNNR (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it (a search feature on there would be so helpful). Anybody know where such an article disproving that myth can be found? My google-fu is being elusive today.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
I believe that is what you are seeking. Links to other articles are there.
Padwan sends thanks to all you with better google-fu than I.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2015 at 8:00 pm
... Mark one off, 43 days on the calendar to go. 43 days on the calendar to go, 43 days to go, ...
January 15, 2015 at 11:43 pm
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)
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
January 16, 2015 at 5:27 am
GilaMonster (1/15/2015)
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)
I've always used SELECT 1 inside and know that EXISTS didn't use any columns, but I admit I'm a bit surprised that works. That's proof that only the presence of rows are used and the value isn't even calculated. Yes, I had to try it. Thanks, Gail.
January 16, 2015 at 5:32 am
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)
I don't get the "1/0" part. That can't be a divisor, can it? Unless the fact that the Divide by Zero error is ignored is the point???
January 16, 2015 at 5:36 am
Brandie Tarvin (1/16/2015)
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)I don't get the "1/0" part. That can't be a divisor, can it? Unless the fact that the Divide by Zero error is ignored is the point???
Yep...that's the point. I wouldn't have thought it would work either, but I tried it and voila.
January 16, 2015 at 5:39 am
Ed Wagner (1/16/2015)
Brandie Tarvin (1/16/2015)
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)I don't get the "1/0" part. That can't be a divisor, can it? Unless the fact that the Divide by Zero error is ignored is the point???
Yep...that's the point. I wouldn't have thought it would work either, but I tried it and voila.
WOW. I just tried it from a table that doesn't have a corresponding value in another table that was in the WHERE EXISTS subquery.
WOW. Interesting facts I learn every day.
EDIT: You know, I suddenly realized this explains some of the odd behavior I've seen with the EXISTS keyword and why I stopped using it in certain circumstances. Suddenly a whole new world opens up and I GET IT! (Thanks, Gail.)
January 16, 2015 at 5:42 am
Brandie Tarvin (1/16/2015)
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)I don't get the "1/0" part. That can't be a divisor, can it? Unless the fact that the Divide by Zero error is ignored is the point???
The point is that the select list is completely ignored in an EXISTS. If the select list was evaluated, that would cause a divide by zero error, but no such error occurs. Hence it's a easy 'proof' that the select list for an EXISTS is not evaluated and hence it doesn't matter what's in there.
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
January 16, 2015 at 6:39 am
GilaMonster (1/16/2015)
Brandie Tarvin (1/16/2015)
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her blog but I can't find it
That's because I don't have one.
I have on on using top(1) in EXISTS, that's all.
Easiest way to prove, other than with tests, is to show that SQL completely ignores the column list in an exists.
WHERE EXISTS (SELECT 1/0 FROM ....)I don't get the "1/0" part. That can't be a divisor, can it? Unless the fact that the Divide by Zero error is ignored is the point???
The point is that the select list is completely ignored in an EXISTS. If the select list was evaluated, that would cause a divide by zero error, but no such error occurs. Hence it's a easy 'proof' that the select list for an EXISTS is not evaluated and hence it doesn't matter what's in there.
This is useful to know, thanks Gail. There was a post earlier this week which would have been easier to close had I known this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2015 at 9:46 am
Koen Verbeeck (1/14/2015)
Steve Jones - SSC Editor (1/14/2015)
Boy, it seems like my questions are getting worse lately. Taking a beating in the QoD.The question of today wasn't bad at all. A bit simple maybe (because there was this big discussion last week about aggregates and NULL, so I am amazed people still got it wrong). People just being pedantic over rows vs nulls.
How sad. I've written a few questions, many of them have had something wrong with them, and I've always been willing to accept that and try to correct them. Those who can't admit that they have made a mistake in formulating a question or an explanation are fools, and perhaps those who encourage people to take that attitude when they have displayed no such attitude and no inclination to adopt it, as you did here, are even worse fools.
Your comment (not Steve's) displays an attitude that says something like "let's not care if the questions and answers are misleading, we don't care if they mislead people who are still learning as long as they don't bother us experienced guys". I think that's disgraceful.
Your insult to those who point out the problems in the explanation is pretty disgraceful too, and based on your arrogance not on any facts, at least as far as I can see. I objected to Steve's explanation because it is likely to make people fail to understand what "exists(select(*))" means, not because I got the question wrong (I got it right, as will anyone with any knowledge of SQL SERVER) and I imagine that the people agreeing with my comment did so for the same reasons I had. Your unwarranted slur and your cavalier attitude to those who may be misled by a bad explanation have diminished my view of you.
edit: By the way, congrats. Well done - well deserved award.
Tom
January 16, 2015 at 1:05 pm
I would appreciate the opinions of the people here on this question
http://www.sqlservercentral.com/Forums/Topic1652059-391-1.aspx
I'm stuck.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 16, 2015 at 5:02 pm
So, something good is finally going to happen on Monday.
Well, not good for the wallet, but good overall, which is the important thing...
I'm going to get my home lab back!
A couple months back, we moved into a new house (much bigger than our old house, and a BASEMENT!) and I planned to set up my home servers in said basement. Except, we forgot to get extra outlets wired in before we closed. And I decided after moving in to chicken out on working in the electric panel to put in additional breakers...
So, Monday morning an electrician is coming out to put in a couple new circuits and outlets. One for my (eventual) workshop, one for the servers, and one for a dehumidifier. Once he's done, I'll be hooking my server PCs back in, fixing up their IPs, and probably spending the rest of the day updating the OSes...
They have been unhooked for quite a few months now...
They're a bit behind...
Jason
January 16, 2015 at 5:16 pm
jasona.work (1/16/2015)
So, something good is finally going to happen on Monday.Well, not good for the wallet, but good overall, which is the important thing...
I'm going to get my home lab back!
A couple months back, we moved into a new house (much bigger than our old house, and a BASEMENT!) and I planned to set up my home servers in said basement. Except, we forgot to get extra outlets wired in before we closed. And I decided after moving in to chicken out on working in the electric panel to put in additional breakers...
So, Monday morning an electrician is coming out to put in a couple new circuits and outlets. One for my (eventual) workshop, one for the servers, and one for a dehumidifier. Once he's done, I'll be hooking my server PCs back in, fixing up their IPs, and probably spending the rest of the day updating the OSes...
They have been unhooked for quite a few months now...
They're a bit behind...
Jason
Sounds cool. I am looking at putting together a home lab when I return from Afghanistan, which by the way, I leave on February 28th if all goes as planned.
January 16, 2015 at 5:17 pm
Sometimes trying to get answers from people is like pulling chicken teeth.
Viewing 15 posts - 46,951 through 46,965 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply