Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query cost


Query cost

Author
Message
mark.ross
mark.ross
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 37
sistemas 95572 (1/29/2010)
Assuming that:
* the inner select returns a reasonable amount of rows - so it fits in memory

shouldn't the IN statement be better since the inner query executes only once, storing in memory all those returned int values (using some kind of ordered array), and then perform a bin search for each value returned by the outer select?




Well, that assumes that the inner query would only execute once. I'm not sure we can guarantee that. In fact, I'm such a pessimist that I would expect the worst case, that the inner query would execute once for each row returned by the outer query. Now, I'm quite sure that the optimizer would handle a good bit of this overhead and give me a pleasant surprise, but maybe not.

You may be right in this case. I simply can't tell.
Jeff Welcome-130556
Jeff Welcome-130556
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 47
I have to say this was the easiest question to date for me!

When I logged in to view the question, there were 3 answer choices.

1. Wrong Answer
2. Right Answer
3. Wrong Answer

Althought tempted by 1 & 3, I decided to go for #2
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36174 Visits: 18751
Jeff,

Did you get it right?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Welcome-130556
Jeff Welcome-130556
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 47
Yes. Yes I did Smile
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 965
Hugo Kornelis (1/27/2010)

<snip>

Other, minor issues are the unneeded brackets around [name] (name is not on the list of reserved keywords, so no delimitation required); the strangely popular but really rather odd EXISTS 1 instead of EXISTS * (EXISTS checks for rows, not values, so what you put there is immaterial - except that * is the standard thhat anyone understands immediately while EXISTS(SELECT 1 makes everybody pause to think); and the broken link in the explanation (the two links both point to the same page).

Bottom line - the only truly correct answer is "it depends". Of the options give, "both are equal" is almost correct. The other two options are plain nonsense.


I actually got into the habit of using ...EXISTS(SELECT 1...) because if you use the construct in an object that will be schema-bound, the "*" isn't allowed (even though it's only used for the EXISTS predicate). Seems an oversight on MS's part (or the SQL standard's).

TroyK



Jamie-2229
Jamie-2229
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 831
This is a great question.

It took C# (formerly a vb programmer here) to teach me that collections work quite efficiently using delegates and predicates and, bearing this concept in mind, it makes far more sense (to me) that an "exists" functionality will provide a faster and more efficient query than one searching the index for a value (IN statement). In this respect the query simply records the rows that are true... or well, at least, not being fully aware of the inner workings, it makes sense to me that counting and returning items that are true is more efficient than searching for them by name and returning them.

Jamie
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 8064
Jamie Longstreet-481950 (2/6/2010)
it makes far more sense (to me) that an "exists" functionality will provide a faster and more efficient query than one searching the index for a value (IN statement).


It does indeed make more sense - and the SQLServer optimiser knows it too, which is why it treats both versions the same :-)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
kaspencer (1/28/2010)
However, if you look at an un answered question (next time), you will see that it is actually quite difficult (no doubt deliberately) to read a thread on a question before answering it.

Well, actually no, it's easy. And straightforward. And obvious. If the people who designed the website had been deliberately making it difficult, it probably would have been difficult, but it isn't. Here's how to do it: note the author of the QOTD (or enough of the name to be useful - in this case "Prakriti" is plenty, you don't need the rest of the name) and the topic of the question (in this case query cost). Then go to any of the discussion forums. Click on "Article Discussions by Author" in the heading (and wait for the page to load it will take a few 10s of second if you have a slow connection); then search for the query author's name; then in the set of discussion topics listed click on the one which ois the topic of the QOTD - bingo, you are there.

I know that you can do this because I saw the two wrongs and a right and concluded something pretty weird was going on (I could make a guess why - the QOTD in the email was clearly one of "those" QOTDs), wanted to know what that was before clicking on an answer, so thought about it for about 20 seonds and then went there. I hadn't ever done it, or even thought about it, before. So I know it is not difficult (thos big block headers make it pretty obvious how to do it).

Tom

paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1272 Visits: 1765
Tom.Thomson (2/15/2010)
kaspencer (1/28/2010)
However, if you look at an un answered question (next time), you will see that it is actually quite difficult (no doubt deliberately) to read a thread on a question before answering it.

Well, actually no, it's easy. And straightforward. And obvious.


No it isn't, not if you're not clicking a link from an email. The emails contain the author of the question but the question page itself does not, see the attachment of today's question as an example. Of course it is easier if you have that information but I didn't, and I would think kaspencer didn't. Personally I don't have time to answer a question a day when i get the email, i go on once a week or so and answer the ones i've missed. In a situation like that being able to check why a question might look odd isn't really very easy to accomplish.

Paul
Attachments
QOTD.GIF (3 views, 9.00 KB)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
paul.goldstraw (2/15/2010)
No it isn't, not if you're not clicking a link from an email. The emails contain the author of the question but the question page itself does not, see the attachment of today's question as an example. Of course it is easier if you have that information but I didn't, and I would think kaspencer didn't. Personally I don't have time to answer a question a day when i get the email, i go on once a week or so and answer the ones i've missed. In a situation like that being able to check why a question might look odd isn't really very easy to accomplish.

Paul

Good point, but if you don't have the author you can try typing the QOTD topic into the search box at the top right of a formum page and hope that either there are few enough hits to sort through or that what you are looking for shows up on the first page. As I haven't a clue how the search works (ie how the results are determined by what's typed in the box) other than that at least some of the time typing more broadens the search, it doesn't narrow it, and that quotation marks try to match the whole pohrase including the marks, I don't generally that search for anything - I spent some time trying to search for articles when I first met SQLS C, but gave up on it pretty quickly. So when I saw your response, I decided to try it to see how good it is for finding a QOTD discussion. A search on Query cost turned up the current discussion as 7th item on the first page of results - so perhaps I should have done that rather than going back to the email to get the author - but I'd be surprised if it were that good every time.
I too can't generally keep up with SQLSC articles and QOTD on a daily basis, but I catch up by going therough the emails when I have time, since I want to catch up on articles and news as well as on QOTD.

Tom

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