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


Query cost


Query cost

Author
Message
Kelsey Thornton
Kelsey Thornton
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 282
CirquedeSQLeil (1/26/2010)

if one examines execution plans for these two queries and run them together, the query optimizer treats them as the same execution plan and equates both queries to the same cost.


Hmmmm. Wonders if they are optimised to a "(s)lowest common denominator" :-)

(Being a bit of a lightweight here I don't know how to examine the execution plans. Is that part of the SQL Profiler?)

Kelsey Thornton
MBCS CITP
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11550
If I critisize a QotD, I always try to maintain a positive tone. Especially since, after having submitted some of my own, I know how hard it is to create one, and how impossible it is to satisfy everyone.

This question makes it very hard for me to stay positive, because it actually shows a severe lack of understanding of the subject matter by the question author. I'll just enumerate my the issues.

1. The schema of the tables has not been supplied. In questions like this, that may be of the utmost importance. For isntance, had the question been about NOT IN versus NOT EXISTS, than the queries would only have been equivalent if student.teacher_id is not nullable. I must admit that after trying some schema variations, I have not yet found one where the query performance of these particular queries is affected by the schema, but I only tried a few ones so I can't exclude the possibility.

2. The author obviously has not bothered to check his ideas. I did (as indicated above). And with all the schema variation I tried, the two queries were executed using the EXACT SAME execution plan. The query optimizer obviously sees that these two queries are equivalent, so they are processed the same. And as a result, there can never be any performance difference. "Both are equal" is the correct answer, as based on this evidence - but there is no guarantee whatsoever that the same holds on all versions of SQL Server, on all possible variations of hardware, and with all possible data distributions.

3. Performance related questions are always disputable because, as indicated above, there are so many factors involved in query optimization that it is almost impossible to predict what the optimizer will do with a query. And it will not always be the same either. Even on the same system, results may change overnight for no apparent reason (happened to me yesterday in the DB I'm working on - a stored proc that suddenly took many minutes to complete).

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
mike.anderson 52709
mike.anderson 52709
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 35
honza.mf (1/27/2010)
CirquedeSQLeil (1/27/2010)
honza.mf (1/27/2010)
I answered OK, but...
The first variant with IN can be more effective, it allways depend on data. Compare two queries without any knowledge about structure of tables, indexes, possible data distributions...


This is a very valid point - the answer really is an It Depends kind of answer.


Thanks.


I must say, I think you can infer that that teacher_id is a PK / FK, but I'm not sure that it makes any difference. Both queries boil down to an inner join between the whole of both tables.

Since all of the data in each table is involved, and the same columns are used in both cases, could you suggest some examples in which the structure / data / indexes would make a difference to these queries?
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11550
vk-kirov (1/27/2010)
Carlo Romagnano (1/27/2010)
FROM BOL:
Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632.

This may happen only when a list of values is used in the IN statement. The author used a subquery, so it's O.K.

Exactly. The query processor will not first execute the subquery to create at a list of teacher_id values and then insert that in the outer query; the optimizer will produce one integral plan to execute the whole query. Which, in my case, consists of scanning the teacher table, and then for each row looking for the first matching value in student.teacher_id (where the exact method of looking depends on whether and how this column is indexed).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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: 2413 Visits: 8062
Hugo Kornelis (1/27/2010)
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).


I must admit I always use EXISTS (SELECT 'X' which is probably even more confusing! It's from my days with Oracle version 5 or 6 - in which we showed that * was slower than 1, by enough that it should be avoided, and that 1 was slightly slower than 'X' so we used the latter in preference. Old habits die hard...
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
CirquedeSQLeil (1/26/2010)
... if one examines execution plans for these two queries and run them together, the query optimizer treats them as the same execution plan and equates both queries to the same cost.


I agree, so it comes down to what is meant by 'cost effective'. I got the answer wrong, but I was using the above meaning of 'cost effective'.....so I feel hard done by Sad
Saurabh Dwivedy
Saurabh Dwivedy
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 340
Kelsey Thornton (1/27/2010)
CirquedeSQLeil (1/26/2010)

if one examines execution plans for these two queries and run them together, the query optimizer treats them as the same execution plan and equates both queries to the same cost.


Hmmmm. Wonders if they are optimised to a "(s)lowest common denominator" :-)

(Being a bit of a lightweight here I don't know how to examine the execution plans. Is that part of the SQL Profiler?)


You can click on the Include Actual Execution Plan Icon in Management Studio before running your query. You will get the actual execution plan in the result.

Clicking on the Display Estimated Execution Plan displays the Estimated Execution Plan.

Thanks

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
Saurabh Dwivedy
Saurabh Dwivedy
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 340
Hugo Kornelis (1/27/2010)
If I critisize a QotD, I always try to maintain a positive tone. Especially since, after having submitted some of my own, I know how hard it is to create one, and how impossible it is to satisfy everyone.

This question makes it very hard for me to stay positive, because it actually shows a severe lack of understanding of the subject matter by the question author. I'll just enumerate my the issues.

1. The schema of the tables has not been supplied. In questions like this, that may be of the utmost importance. For isntance, had the question been about NOT IN versus NOT EXISTS, than the queries would only have been equivalent if student.teacher_id is not nullable. I must admit that after trying some schema variations, I have not yet found one where the query performance of these particular queries is affected by the schema, but I only tried a few ones so I can't exclude the possibility.

2. The author obviously has not bothered to check his ideas. I did (as indicated above). And with all the schema variation I tried, the two queries were executed using the EXACT SAME execution plan. The query optimizer obviously sees that these two queries are equivalent, so they are processed the same. And as a result, there can never be any performance difference. "Both are equal" is the correct answer, as based on this evidence - but there is no guarantee whatsoever that the same holds on all versions of SQL Server, on all possible variations of hardware, and with all possible data distributions.

3. Performance related questions are always disputable because, as indicated above, there are so many factors involved in query optimization that it is almost impossible to predict what the optimizer will do with a query. And it will not always be the same either. Even on the same system, results may change overnight for no apparent reason (happened to me yesterday in the DB I'm working on - a stored proc that suddenly took many minutes to complete).

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.


Presence of NULL values in the columns being compared can make a difference to the execution plans if the query were comparing NOT EXISTS and NOT IN constructs.

In case the TeacherID column contained a NULL Value - Not IN and Not Exists would have showed different execution plans (this is of course assuming that TeacherID allows for NULLS - a schema dependent condition). However since the query is checking for plain Exists versus IN, this condition is inapplicable from the start. But I still wanted to make this point.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
Kelsey Thornton
Kelsey Thornton
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 282
Saurabh Dwivedy (1/27/2010)
Kelsey Thornton (1/27/2010)


(Being a bit of a lightweight here I don't know how to examine the execution plans. Is that part of the SQL Profiler?)


You can click on the Include Actual Execution Plan Icon in Management Studio before running your query. You will get the actual execution plan in the result.

Clicking on the Display Estimated Execution Plan displays the Estimated Execution Plan.

Thanks


Thanks - I've learned my something for today - I can go back to sleep :-D

Kelsey Thornton
MBCS CITP
balex 14155
balex 14155
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 37
This appears to be based on the silly assumption that your machine costs something and your time costs nothing!!!
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