subqueries performances - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subqueries performances

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-16 : 17:57:46

Hello,

I would like to know which of the following query is the fastest:

select * from customers C
where exists (select 0 from ORDERS O
              where C.Name like 'A%' and O.Charged = 1  and O.Customers_Id = C.Id)

select * from customers C
where exists (select 0 from ORDERS O
              where O.Charged = 1 and O.Customers_Id = C.Id) and  C.Name like 'A%'

Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?

Thank you in advance for any advices,

regards,

mathmax

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 18:04:06

The easiest way to find this out is to check the execution plan of both in one batch. In Query Analyzer or Management Studio, select the option in the Query menu to include the actual execution plan. Put both queries in the same query window. Hit F5 to execute them. Go to the execution plan tab and notice the batch cost. What does it say? I'm betting it'll say 50% for each.

I don't see any benefit to adding the C.Name in the exists query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources