Which of the 2 queries is better in terms of performance
-
select c.custid,c.companyname from sales.MyCustomers c left join sales.MyOrders o on c.custid=o.custid
where o.orderid is null
Or
select custid,companyname from sales.MyCustomers where custid not in (select custid from sales.MyOrders)
Hard to see from Query Plan. First query has hash join
Thanks
Mohsin Ladha
-
Do they give the same query plan?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Run them both in the same batch and check the relative costs.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Try this version too:
SELECT custid, companyname
FROM sales.MyCustomers c
WHERE NOT EXISTS ( SELECT 1
FROM sales.MyOrders o
WHERE o.CustId = c.CustId );
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil Parkin (10/7/2016)
Run them both in the same batch and check the relative costs.^THIS
As an example I ran similar queries against AdventrureWorks2012.
SELECT*
FROM
Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader soh ON soh.customerID = c.CustomerID
WHERE
soh.CustomerID IS NULL
GO
SELECT
*
FROM
Sales.Customer c
WHERE
c.CustomerID NOT IN (SELECT CustomerID FROM Sales.SalesOrderHeader)
Make sure you select "Include Actual Execution Plan" then looking at the plan you can get a good indication. Keep in mind this example is wholly unique to AdventureWorks as I have no idea what your tables and indexes look like.
SELECT quote FROM brain WHERE original = 1
0 rows returned -
You can also try this:
select
mc.custid,
mc.companyname
from
sales.MyCustomers mc
where
not exists(select 1 from sales.MyOrders mo where mo.custid = mc.custid);
-
Lynn Pettis (10/7/2016)
You can also try this:select
mc.custid,
mc.companyname
from
sales.MyCustomers mc
where
not exists(select 1 from sales.MyOrders mo where mo.custid = mc.custid);
Looks rather similar to my suggestion! 😛
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
You should be ashamed, Lynn. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Phil Parkin (10/7/2016)
Lynn Pettis (10/7/2016)
You can also try this:select
mc.custid,
mc.companyname
from
sales.MyCustomers mc
where
not exists(select 1 from sales.MyOrders mo where mo.custid = mc.custid);
Looks rather similar to my suggestion! 😛
Yep. I got distracted while writing my post. Work happens.
-
The Dixie Flatline (10/7/2016)
You should be ashamed, Lynn. 😀
Except I am the one who gives out the cards. :w00t:
-
Not today. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Although, let's be cautious about comparing the estimated costs on execution plans and saying that's the same as comparing performance. It absolutely is not. Even with actual plans, those costs are still just estimates based on the operations within the plan, not measures of performance. They are in fact, the exact same costs that you'll see in estimated plans, no different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
And seconding Grant, plan costs are NOT durations and not a good indication of duration
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for all the replies. I get it! One cannot rely on just Query Execution Plan to determine if a query will run efficiently
-
mohsinladha (10/10/2016)
Thanks for all the replies. I get it! One cannot rely on just Query Execution Plan to determine if a query will run efficientlyNot exactly. It's just that the query plan isn't a measure of performance. The query plan does show what types of operations are being done to satisfy the query, and that will allow you to determine if it's efficient or not. But to measure performance, you need to measure performance. Two different things.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply