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 Tuesday, January 26, 2010 9:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, January 26, 2010 10:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 21,642, Visits: 15,312
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


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
Posted Wednesday, January 27, 2010 12:10 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
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
Posted Wednesday, January 27, 2010 12:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:43 AM
Points: 1,356, Visits: 1,313
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
Posted Wednesday, January 27, 2010 12:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 21,642, Visits: 15,312
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


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
Posted Wednesday, January 27, 2010 12:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 3,014, Visits: 912
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
Posted Wednesday, January 27, 2010 12:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:43 AM
Points: 1,356, Visits: 1,313
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
Posted Wednesday, January 27, 2010 1:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 2,501, Visits: 2,383
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
Posted Wednesday, January 27, 2010 2:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,739, Visits: 6,359
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
Posted Wednesday, January 27, 2010 2:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse