<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Prakriti Agrawal
»
Query cost
85 posts, Page 1 of 9
1
2
3
4
5
»
»»
Query cost
Rate Topic
Display Mode
Topic Options
Author
Message
agrawal.prakriti
agrawal.prakriti
Posted Tuesday, January 26, 2010 9:40 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 27, 2010 1:31 PM
Points: 132,
Visits: 101
Comments posted to this topic are about the item
Query cost
Post #854122
SQLRNNR
SQLRNNR
Posted Tuesday, January 26, 2010 10:25 PM
SSCoach
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733,
Visits: 12,332
Based on your provided reference, I would agree. However, 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.
Here is a nice resource on the topic:
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #854147
Saurabh Dwivedy
Saurabh Dwivedy
Posted Wednesday, January 27, 2010 12:10 AM
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, January 05, 2012 2:05 AM
Points: 488,
Visits: 335
I had chosen - "both are equally cost-effective" simply because I felt that the correlated subquery still needs to check for the existence of the equality condition for which the passes through the student and teacher tables will have to be made for every outer row. Whereas the simple subquery will have to be evaluated only once and then the IN operator would kick in. Somehow - without having any rigourous fundamentals backing my theory - I felt the queries would perform equally well and so I chose the third option.
Even though I knew that the question was testing the usage of EXISTS - I wasn't convinced that the 2nd query would perform appreciably better than the first query.
Anyway - I stand corrected.
I had not confirmed the execution plan in SSMS. Now that Cirque has confirmed what I felt intuitively I will go ahead and look at it.
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 #854194
honza.mf
honza.mf
Posted Wednesday, January 27, 2010 12:11 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 1,103,
Visits: 1,199
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...
See, understand, learn, try, use efficient
© Dr.Plch
Post #854195
SQLRNNR
SQLRNNR
Posted Wednesday, January 27, 2010 12:21 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733,
Visits: 12,332
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.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #854199
Rune Bivrin
Rune Bivrin
Posted Wednesday, January 27, 2010 12:25 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 2,659,
Visits: 720
This used to be true, and I have often used this knowledge to optimise slow-running queries. But since SQL 2005 it no longer matters from a performance point of view. It's still good practice to use EXISTS, though, as it expresses the intent more clearly.
Just because you're right doesn't mean everybody else is wrong.
Post #854202
honza.mf
honza.mf
Posted Wednesday, January 27, 2010 12:37 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 1,103,
Visits: 1,199
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.
See, understand, learn, try, use efficient
© Dr.Plch
Post #854206
Carlo Romagnano
Carlo Romagnano
Posted Wednesday, January 27, 2010 1:41 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,972,
Visits: 1,821
Volumes of data returned from tables may vary (test or production database), so a well performing query may slow down or give an error.
FROM BOL:
Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.
I prefer EXISTS instead of IN + subquery because of performance.
Post #854227
Toreador
Toreador
Posted Wednesday, January 27, 2010 2:23 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:52 AM
Points: 1,356,
Visits: 4,761
I recently optimised a query and tried exactly these two constructs. The query plan for both was identical.
So the correct answer is either "they are equal", or (more likely I suspect) "it depends on the data".
Post #854240
vk-kirov
vk-kirov
Posted Wednesday, January 27, 2010 2:23 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 3,191,
Visits: 4,149
Rune Bivrin (1/27/2010)
This used to be true, and I have often used this knowledge to optimise slow-running queries. But since SQL 2005 it no longer matters from a performance point of view.
I checked this on SQL Server 2000 (SP4, 8.00.2039) and it produced fully identical execution plans. Seems like the database engine 2000 is smart enough
Maybe this was one of the improvements in Service Pack 4.
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.
Post #854241
« Prev Topic
|
Next Topic »
85 posts, Page 1 of 9
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
