SQL Query Tuning

Question

I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can this be stored so it only does it once?) (taking it out shaves 23 seconds, anything under 10 will be brilliant) but i'm unsure, is there any better ways of writing this?

Thanks in advance!

Dave

DECLARE @site INT = 4
DECLARE @langid INT = 9
DECLARE @checkID INT = 0
DECLARE @minstockQTY INT = 0
DECLARE @branchCount INT = (SELECT COUNT(*) 
                            FROM Branch 
                            WHERE ApplicationID = @site)

if @site = 4 BEGIN
    SET @checkID = 4
end

select p.*,
       stuff((select ',' + cast(BrandID as nvarchar(max))
              from Product_Brand_Mapping d 
              where d.ProductCode = p.ProductCode 
                    AND d.ApplicationID = @site for xml path('')) , 1, 1, '') as BrandIDs,
       stuff((select '|' + cast(Name as nvarchar(max))
              from Brands e 
              where e.ID IN (SELECT BrandID 
                             FROM Product_Brand_Mapping f 
                             WHERE f.ProductCode = p.ProductCode 
                                   AND f.ApplicationID = @site) for xml path('')), 1, 1, '') as BrandNames,
       stuff((select ',' + cast(DepartmentID as nvarchar(max))
              from Product_Department_Mapping e 
              where e.ProductCode = p.ProductCode 
                    and e.ApplicationID = @site for xml path('')), 1, 1, '') as DepartmentIDs,
       stuff((select ',' + cast(DepartmentID as nvarchar(max)) + '|' + cast(DisplayOrder as nvarchar(max)) 
              from Product_Department_DisplayOrder e 
              where e.ProductCode = p.ProductCode 
                    and e.ApplicationID = @site for xml path('')), 1, 1, '') as DisplayOrders,
       stuff((select '|' + cast(Size as nvarchar(max)) 
              from Products e 
              where e.ProductCode = p.ProductCode 
                    and e.Sell > 0 
                    AND e.Department > 0 
                    AND e.ApplicationID = @site 
                    AND e.Published = 1 
                    AND e.PrimaryPLU IN (select SKU 
                                         FROM Stock 
                                         WHERE BranchID IN (1,2,11,12,32,31,13,14,15,0,96) 
                                               AND ApplicationID = @site 
                                         GROUP BY SKU 
                                         HAVING(SUM(CASE WHEN Stock < 0 
                                                              AND BranchID = 31 
                                                         THEN Stock 
                                                         WHEN BranchID = 31 
                                                         THEN Stock END)) - p.MinStockQty > 0) for xml path('')), 1, 1, '') as Sizes,
       stuff((select '|' + cast(Colour as nvarchar(max)) 
              from Products e 
              where e.ProductCode = p.ProductCode 
                    and e.Sell > 0 
                    AND e.Department > 0 
                    AND e.ApplicationID = @site 
                    AND e.Published = 1 
                    AND e.PrimaryPLU IN (select SKU 
                                         FROM Stock 
                                         WHERE BranchID IN (1,2,11,12,32,31,13,14,15,0,96) 
                                               AND ApplicationID = @checkID 
                                               GROUP BY SKU 
                                               HAVING(SUM(CASE WHEN Stock < 0 
                                                                    AND BranchID = 31
                                                               THEN Stock 
                                                               WHEN BranchID NOT IN (31) 
                                                               THEN Stock END)) - p.MinStockQty > 0) for xml path('')), 1, 1, '') as Colours,
       (select MAX(pr.Sell) 
        from Products pr 
        where pr.ProductCode = p.ProductCode 
              AND pr.ApplicationID = @site) as SellTo, 
       (select MAX(pr.WholeSale) 
        from Products pr 
        where pr.ProductCode = p.ProductCode 
              AND pr.ApplicationID = @site) as WasTo,
       (select MAX(Department) 
        from Products pr 
        where pr.ProductCode = p.ProductCode 
              AND pr.ApplicationID = @site) as Department
from ProductDescription p
WHERE p.ApplicationID = @site 
      AND p.LanguageID = @langid 
      AND p.Published = 1 
      AND p.Deleted = 0
      AND p.ProductCode IN (SELECT Distinct pr.ProductCode 
                            FROM Products pr 
                            WHERE pr.Sell > 0 
                                  AND pr.Department > 0 
                                  AND pr.ApplicationID = @site 
                                  AND pr.Published = 1)
      AND ((SELECT SUM(st.Stock) 
            FROM Stock st 
            WHERE st.ApplicationID = 4 
            AND st.SKU IN (SELECT DISTINCT pr.PrimaryPLU 
                           FROM Products pr 
                           WHERE p.ProductCode = pr.ProductCode 
                                 AND pr.ApplicationID = @site)
            AND st.BranchID IN (1,2,11,12,32,13,14,15,0,96))
           + ISNULL((SELECT SUM(st.Stock) 
                     FROM Stock st 
                     WHERE st.ApplicationID = 4 
                           AND st.SKU IN (SELECT DISTINCT pr.PrimaryPLU 
                                          FROM Products pr 
                                          WHERE p.ProductCode = pr.ProductCode 
                                                AND pr.ApplicationID = @site)
                           AND st.BranchID = 31
                           AND st.Stock < 0), 0)) - p.MinStockQty > 0

Answer 1

Subquery should not be used.Please prefer a join with Table\view

Select * from ProductDescription pinner join Product PR on P.ProductCode=PR.ProductCode  on AND pr.Department > 0 
                              AND pr.ApplicationID = @site 
                              AND pr.Published = 1 Left join Stock st on PR.SKU=pr.PrimaryPLU  WHERE  p.ApplicationID = @site 
  AND p.LanguageID = @langid 
  AND p.Published = 1 
  AND p.Deleted = 0

This is not complete sql but might help you to refactor you code

Answer 2

Use the Display Estimated Execution plan and Show Execution Plan on the Query Analyzer menu.

Pay special attention to the 'Percentage of Overall Query' numbers. You can make significant improvements by focusing on any single step that is taking the most time.

Number one no brainier to look for is Table Scan of any large table. Table Scan of small ( less than 100 rows or so ) is perfectly ok.

You can hover highlight the connecting lines between the steps to show the amount of data that is being transferred in each filtering step. This can give you a clue as to what could be optimized. The more data that is being moved around and joined with other large amounts of data means a slower query.

Be aware of the kinds of join strategy being selected by the engine. The lack of appropriate indexes can force the engine to pick a very slow join strategy. As you add indexes, monitor the choices made by the engine each time you redo the execution plan. Watch for table scan to disappear and index seeks to show up. Watch for join strategies to change and perhaps the overall data flow to be rearranged in a more efficient way once appropriate indexes are added. Also once you develop a baseline for the approximately correct plan for this type of data pull, you will have an easier time if performance problems come up in the future.