SQLTeam.com | Weblogs | Forums

Sub Query's optimization



Hi , how can optimize the follow queries ?

SELECT pc.Name
, ppsc.Name
, ( SELECT COUNT(*) FROM Production.Product pp
WHERE pp.ProductSubcategoryID = ppsc.ProductSubcategoryID) as NumberOfProducts
FROM Production.ProductCategory pc
INNER JOIN Production.ProductSubCategory ppsc
ON pc.ProductcategoryID = ppsc.ProductcategoryID


SELECT pw.ProductID
, pp.Name
, ppsc.Name
, SUM(pw.OrderQty) as Quantity
FROM Production.Product pp
LEFT JOIN Production.ProductSubCategory ppsc
ON pp.ProductSubcategoryID = ppsc.ProductSubcategoryID
INNER JOIN Production.WorkOrder pw
ON pw.ProductID = pp.ProductID
WHERE pw.DueDate = '2003-06-03 00:00:00.000'
GROUP BY pw.ProductID, pp.Name, ppsc.Name


What do you want to optimize? Are they poor performers? General advice: make sure that join and where predicates are covered by indexes


Thks about your rapid reply. I would like to know if i can do that query without the subselect and other way to do the sum () function on second case.


Show us the execution plans in xml format. Also add SET STATISTICS IO ON to your script and post the stats output.


Why? If the queries work, they are simple enough that I wouldn't look for other approaches.


The queries likely are slow and the OP thinks that refactoring the code is the solution. I think the solution is adding or modifying indexes.


OIC, then your idea is the one to pursue