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

Query cost Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 2:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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
Post #854247
Posted Wednesday, January 27, 2010 2:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 5,801, Visits: 8,018
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
Post #854249
Posted Wednesday, January 27, 2010 2:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 12, 2010 2:42 AM
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?
Post #854250
Posted Wednesday, January 27, 2010 2:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 5,801, Visits: 8,018
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
Post #854254
Posted Wednesday, January 27, 2010 3:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 1,659, Visits: 6,008
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...
Post #854256
Posted Wednesday, January 27, 2010 3:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 1,890, Visits: 1,190
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
Post #854270
Posted Wednesday, January 27, 2010 4:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
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!
Post #854278
Posted Wednesday, January 27, 2010 4:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
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!
Post #854284
Posted Wednesday, January 27, 2010 4:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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


Kelsey Thornton
MBCS CITP
Post #854291
Posted Wednesday, January 27, 2010 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 7:47 AM
Points: 13, Visits: 35
This appears to be based on the silly assumption that your machine costs something and your time costs nothing!!!
Post #854342
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse