Query cost

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.