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

Query cost Expand / Collapse
Author
Message
Posted Friday, January 29, 2010 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 12, 2010 8:31 AM
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.
Post #856177
Posted Monday, February 1, 2010 10:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 4:09 PM
Points: 1,384, 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
Post #857367
Posted Monday, February 1, 2010 10:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:31 AM
Points: 33,155, Visits: 15,286
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
Post #857377
Posted Monday, February 1, 2010 10:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 4:09 PM
Points: 1,384, Visits: 47
Yes. Yes I did :)
Post #857380
Posted Wednesday, February 3, 2010 1:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:42 AM
Points: 1,322, Visits: 793
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



Post #859004
Posted Saturday, February 6, 2010 5:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #861072
Posted Monday, February 8, 2010 2:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 1,733, Visits: 6,324
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
Post #861481
Posted Monday, February 15, 2010 7:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
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
Post #865537
Posted Monday, February 15, 2010 7:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,051, Visits: 1,415
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


  Post Attachments 
QOTD.GIF (2 views, 9.38 KB)
Post #865541
Posted Monday, February 15, 2010 12:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
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
Post #865663
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse