February 21, 2012 at 7:48 pm
Hi,
I'm new to execution plan..
select PO.PurchaseOrderID,PO.VendorID,PO.OrderDate,PO.ShipDate ,V.Name,SM.Name,PV.OnOrderQty from Purchasing.PurchaseOrderHeader PO Inner join Purchasing.PurchaseOrderDetail POD on PO.PurchaseOrderID=POD.PurchaseOrderID Inner Join Purchasing.Vendor V on PO.VendorID=V.VendorID Inner join Purchasing.ShipMethod SM on SM.ShipMethodID=PO.ShipMethodID Inner Join Purchasing.ProductVendor PV on PO.VendorID=PV.VendorID and POD.ProductID=PV.ProductID
Here I need to get Vendor name, ship method name and current Orderqty from Vendor,Ship method and Productvendor tables respectively.. In the above query i used Inner join...
For the same scenario I used sub queries.. which is the best way to do it..
select PO.PurchaseOrderID,PO.VendorID,PO.OrderDate,PO.ShipDate ,(Select name from purchasing.vendor v where PO.VendorID=V.VendorID),(Select name from Purchasing.ShipMethod S where PO.ShipMethodID=S.ShipMethodID ),(Select PV.OnOrderQty from Purchasing.ProductVendor PV where PO.VendorID=PV.VendorID and POD.ProductID=PV.ProductID ) from Purchasing.PurchaseOrderHeader PO Inner join Purchasing.PurchaseOrderDetail POD on PO.PurchaseOrderID=POD.PurchaseOrderID
I had attached the execution plan for both scenarios..
Thanks in advance
Regards
Guru
February 22, 2012 at 12:15 am
First of all, these are not the same queries as they may produce different results !
Refactored they look like this :
select PO.PurchaseOrderID
, PO.VendorID
, PO.OrderDate
, PO.ShipDate
, V.Name
, SM.Name
, PV.OnOrderQty
from Purchasing.PurchaseOrderHeader PO
Inner join Purchasing.PurchaseOrderDetail POD
on PO.PurchaseOrderID = POD.PurchaseOrderID
Inner Join Purchasing.Vendor V
on PO.VendorID = V.VendorID
Inner join Purchasing.ShipMethod SM
on SM.ShipMethodID = PO.ShipMethodID
Inner Join Purchasing.ProductVendor PV
on PO.VendorID = PV.VendorID
and POD.ProductID = PV.ProductID
vs
select PO.PurchaseOrderID
, PO.VendorID
, PO.OrderDate
, PO.ShipDate
, (
Select name
from purchasing.vendor v
where PO.VendorID = V.VendorID
)
, (
Select name
from Purchasing.ShipMethod S
where PO.ShipMethodID = S.ShipMethodID
)
, (
Select PV.OnOrderQty
from Purchasing.ProductVendor PV
where PO.VendorID = PV.VendorID
and POD.ProductID = PV.ProductID
)
from Purchasing.PurchaseOrderHeader PO
Inner join Purchasing.PurchaseOrderDetail POD
on PO.PurchaseOrderID = POD.PurchaseOrderID
To make them alike, you would have to switch to using left joins for all nested selects.
The joined equivalent would be:
select PO.PurchaseOrderID
, PO.VendorID
, PO.OrderDate
, PO.ShipDate
, V.Name as VendorName
, SM.Name as ShipMethodName
, PV.OnOrderQty as ProductVendorOnOrderQty
from Purchasing.PurchaseOrderHeader PO
Inner join Purchasing.PurchaseOrderDetail POD
on PO.PurchaseOrderID = POD.PurchaseOrderID
Left Join Purchasing.Vendor V
on PO.VendorID = V.VendorID
Left Join Purchasing.ShipMethod SM
on SM.ShipMethodID = PO.ShipMethodID
Left Join Purchasing.ProductVendor PV
on PO.VendorID = PV.VendorID
and POD.ProductID = PV.ProductID
So the bottom question for your query is: are you using the correct join method ?
From a "how to write the query" point of view:
- Which one is the easiest to read ( consider maintenance by newbe )
I always try to avoid column level nested selects as they turn out to make ( large ) queries harder to read and interpret and are more prone to semantic errors.
Whenever I use a column level nested select, I always document the performance reason for it. Why did I code it in stead of using a classic left joined query.
btw the plan compilation of your inner join query timed out ! ( meaning the engine couldn't come up with an optimal solution for it within a time frame. {same player shoot again, hit ball when lights are on})
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 22, 2012 at 7:00 am
A more useful statistic for comparing two queries is the number of reads. If you SET STATISTICS IO, TIME ON and then run both queries, you will be able to compare the number of reads for each query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply