Sub Query's optimization

Transact-SQL
1

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

AND

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

2

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

3

Hi,
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.

4

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

5

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

6

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.

7

OIC, then your idea is the one to pursue