title: SQL Server query needs performance improvement - left joins on a temp table with groupby rollup
body: <p>I have inherited a huge query/report SP. It's running super-slow and is not scaling well. There is a lot of left joining going on from the way it's written. It's making a temp table and storing a date range as individual dates in a datetime column, then left joining on this table to get the info from several other tables with a groupby/rollup to get totals for the report. It's written in a way unfamiliar to me. I'm used to the dates/params being in the 'WHERE' clause where Indexes are an advantage. </p>
  
  <p>There has to be a better way of doing this. Maybe just some sage guidance would be great. Code would be even better. I've been trying to think through a CTE approach. There are tons of ISNULL checks and nested CONVERTS to scrape off the time and just leave a date for comparison, very sloppy.</p>
  
  <p>I'm new here so I can't tell you much about the DB yet as far as indexes,etc I've heard there aren't indexes where they need to be etc. This system was built years ago and is on a SQL Server 2008 box. Anyway here's the query:</p>
  
  <pre><code>-- these are just sample dates as I have copied this from the SP into a query window since it's all dynamic sql and hard to read as just a red string.   
  DECLARE @FromDate date = '2015-06-01'
  DECLARE @ToDate date = '2015-06-02'
  DECLARE @TempDate date
  DECLARE @TempToDate date
  
  -- from here down is the SP
  CREATE TABLE #SSTempDates (TempDate DATETIME)
  
  IF @FromDate = ''
      SET @TempDate = (SELECT CONVERT(NVARCHAR(10), MIN(ImportDate), 101) FROM OrdersViewWImport)
  ELSE
      SET @TempDate = @FromDate
  
  IF @ToDate = ''
      SET @TempToDate = (SELECT CONVERT(NVARCHAR(10),GETDATE(), 101))
  ELSE
      SET @TempToDate = @ToDate
  
  WHILE @TempDate &lt; @TempToDate
  BEGIN
      INSERT INTO #SSTempDates SELECT @TempDate
      SET @TempDate = DATEADD(D,1,@TempDate)
  END
  
  -- done creating the temp table that holds the dates to join on
  SELECT CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN 'Total' ELSE CONVERT(NVARCHAR(10), TD.TempDate, 101) END AS [Date], 
      SUM(ISNULL(SH.QtyShipped,0)) AS [Units Shipped], 
      SUM(ISNULL(SH.ShippedPrice,0)) AS [Pounds Shipped], 
      SUM(ISNULL(C.QtyCancel,0)) AS [Units Cancelled], 
      SUM(ISNULL(C.CancelledPrice,0)) AS [Pounds Cancelled], 
      SUM(ISNULL(PR.OrdersPrinted,0)) AS [Allocated Orders], 
      SUM(ISNULL(SH.OrdersShipped,0)) + SUM(ISNULL(A.OrdersAssigned,0)) AS [Shipped Orders], 
      SUM(ISNULL(PR.QtyPrinted,0)) AS [Items Allocated], 
      SUM(ISNULL(SH.QtyShipped,0)) + SUM(ISNULL(A.QtyAssigned,0)) AS [Items Shipped], 
      CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN 
          CASE WHEN ISNULL(SUM(PR.QtyPrinted),0) = 0 THEN 0 
              WHEN CAST(CONVERT(DECIMAL,(ISNULL(SUM(SH.QtyShipped),0) + ISNULL(SUM(A.QtyAssigned),0))) / CONVERT(DECIMAL,ISNULL(SUM(PR.QtyPrinted),0)) * 100 AS DECIMAL(19,2)) &gt; 100 THEN 100 
              ELSE CAST(CONVERT(DECIMAL,(ISNULL(SUM(SH.QtyShipped),0) + ISNULL(SUM(A.QtyAssigned),0))) / CONVERT(DECIMAL,ISNULL(SUM(PR.QtyPrinted),0)) * 100 AS DECIMAL(19,2)) END 
          ELSE SUM(CASE WHEN ISNULL(PR.QtyPrinted,0) = 0 THEN 0 
              WHEN CAST(CONVERT(DECIMAL,(ISNULL(SH.QtyShipped,0) + ISNULL(A.QtyAssigned,0))) / CONVERT(DECIMAL,ISNULL(PR.QtyPrinted,0)) * 100 AS DECIMAL(19,2)) &gt; 100 THEN 100 
              ELSE CAST(CONVERT(DECIMAL,(ISNULL(SH.QtyShipped,0) + ISNULL(A.QtyAssigned,0))) / CONVERT(DECIMAL,ISNULL(PR.QtyPrinted,0)) * 100 AS DECIMAL(19,2)) END) END AS [Fill Rate %], 
      CAST(CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN ISNULL(SUM(NP.[ID]) / ISNULL((SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])),0) * 100,0) 
          ELSE CASE WHEN SUM(NP.[ID]) = 0 THEN 0 
              ELSE ISNULL(SUM(NP.[ID]) / (SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])) * 100,0) END END AS DECIMAL(19,2)) AS [Inv Disc %], 
      CAST(CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN ISNULL(SUM(NP.[NG]) / ISNULL((SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])),0) * 100,0) 
          ELSE CASE WHEN SUM(NP.[NG]) = 0 THEN 0 
              ELSE ISNULL(SUM(NP.[NG]) / (SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])) * 100,0) END END AS DECIMAL(19,2)) AS [NG %], 
      CAST(CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN ISNULL(SUM(NP.[SS]) / ISNULL((SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])),0) * 100,0) 
          ELSE CASE WHEN SUM(NP.[SS]) = 0 THEN 0 
              ELSE ISNULL(SUM(NP.[SS]) / (SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])) * 100,0) END END AS DECIMAL(19,2)) AS [SS %],       
      CAST(CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 THEN ISNULL(SUM(NP.[ZO]) / ISNULL((SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])),0) * 100,0) 
          ELSE CASE WHEN SUM(NP.[ZO]) = 0 THEN 0 
              ELSE ISNULL(SUM(NP.[ZO]) / (SUM(NP.[ID])+SUM(NP.[NG])+SUM(NP.[ZO])+SUM(NP.[SS])) * 100,0) END END AS DECIMAL(19,2)) AS [ZO %], 
      CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 
          THEN CAST(AVG(CASE WHEN ISNULL(SH.AvgPrice,0) + ISNULL(A.AvgPrice,0) = 0 THEN 0 ELSE ISNULL(SH.AvgPrice,0) + ISNULL(A.AvgPrice,0) END) AS DECIMAL(19,2)) 
          ELSE CAST(SUM(ISNULL(SH.AvgPrice,0)) + SUM(ISNULL(A.AvgPrice,0)) AS DECIMAL(19,2)) END AS [AOV], 
      CASE WHEN GROUPING(CONVERT(NVARCHAR(10), TD.TempDate, 101)) = 1 
          THEN CAST(AVG(CASE WHEN ISNULL(SH.AvgQty,0) + ISNULL(A.AvgQty,0) = 0 THEN 0 ELSE ISNULL(SH.AvgQty,0) + ISNULL(A.AvgQty,0) END) AS DECIMAL(19,2)) 
          ELSE SUM(ISNULL(SH.AvgQty,0)) + SUM(ISNULL(A.AvgQty,0)) END AS [UPO] 
  
      FROM (SELECT DISTINCT TempDate FROM #SSTempDates) TD 
      LEFT JOIN ( 
          SELECT COUNT(DISTINCT OrderNo) AS OrdersPrinted, SUM(Quantity) AS QtyPrinted, CONVERT(DATETIME,CONVERT(NVARCHAR, PrintBatch, 101)) AS PrintBatchDate 
          FROM PickTicketsOrders WITH (NOLOCK) 
          GROUP BY CONVERT(DATETIME,CONVERT(NVARCHAR, PrintBatch, 101))) PR ON PR.PrintBatchDate=CONVERT(NVARCHAR(10), TD.TempDate, 101) 
      LEFT JOIN ( 
          SELECT COUNT(DISTINCT OI.OrderNo) AS OrdersShipped, SUM(OI.Quantity) AS [QtyShipped], SUM(OI.ExtPrice) AS ShippedPrice, CONVERT(DATETIME,CONVERT(NVARCHAR, S.ShipBatch, 101)) AS ShipDate, 
              CAST(AVG(OI.ExtPrice) AS DECIMAL(19,6)) AS AvgPrice, CAST(AVG(OI.Quantity) AS DECIMAL(19,6)) AS AvgQty 
          FROM Shipments S WITH (NOLOCK) 
          inner join (SELECT OrderNo, GroupNum, SUM(Quantity) AS Quantity, SUM(ExtPrice) AS ExtPrice FROM OrderItems GROUP BY OrderNo, GroupNum) OI  ON OI.OrderNo = S.OrderNo AND OI.GroupNum = S.GroupNum 
          GROUP BY CONVERT(DATETIME,CONVERT(NVARCHAR, S.ShipBatch, 101))) SH ON SH.ShipDate=CONVERT(NVARCHAR(10), TD.TempDate, 101) 
      LEFT JOIN ( 
          SELECT COUNT(DISTINCT OI.OrderNo) AS OrdersAssigned, SUM(OI.Quantity) AS [QtyAssigned], CONVERT(DATETIME,CONVERT(NVARCHAR, OA.ActDate, 101)) AS ActDate, 
              CAST(AVG(OI.ExtPrice) AS DECIMAL(19,6)) AS AvgPrice, CAST(AVG(OI.Quantity) AS DECIMAL(19,6)) AS AvgQty 
          FROM OrderActions OA WITH (NOLOCK) 
          INNER JOIN PndShipments P WITH (NOLOCK) ON P.OrderNo=OA.OrderNo AND P.GroupNum=OA.grouplinenum 
          INNER JOIN OrderItems OI WITH (NOLOCK) ON OI.OrderNo=P.OrderNo AND OI.GroupNum=P.GroupNum 
          INNER JOIN Orders O WITH (NOLOCK) ON O.OrderNo=OA.OrderNo 
          WHERE OA.Type=31 
          GROUP BY CONVERT(DATETIME,CONVERT(NVARCHAR, OA.ActDate, 101))) A ON A.ActDate=CONVERT(NVARCHAR(10), TD.TempDate, 101) 
      LEFT JOIN ( 
          SELECT SUM(OI.Quantity) AS QtyCancel, SUM(OI.ExtPrice) AS CancelledPrice, CONVERT(DATETIME,CONVERT(NVARCHAR, CC.CancelDate, 101)) AS CancelDate 
          FROM OrderItems OI WITH (NOLOCK) 
          INNER JOIN Cancels CC WITH (NOLOCK) ON CC.OrderNo=OI.OrderNo AND CC.GroupNum=OI.GroupNum 
          GROUP BY CONVERT(DATETIME,CONVERT(NVARCHAR, CC.CancelDate, 101))) C ON C.CancelDate=CONVERT(NVARCHAR(10), TD.TempDate, 101) 
      LEFT JOIN ( 
          SELECT CONVERT(DATETIME,CONVERT(NVARCHAR, PrintBatch, 101)) AS TotalNonPickDate, 
              SUM(CASE WHEN NoPickReason Like 'ID%' THEN 1.0 ELSE 0.0 END) AS [ID], 
              SUM(CASE WHEN NoPickReason Like 'NG%' THEN 1.0 ELSE 0.0 END) AS [NG], 
              SUM(CASE WHEN NoPickReason Like 'SS%' THEN 1.0 ELSE 0.0 END) AS [SS], 
              SUM(CASE WHEN NoPickReason Like 'ZO%' THEN 1.0 ELSE 0.0 END) AS [ZO] 
              FROM PickTickets WITH (NOLOCK) 
              WHERE PickBatch&lt;&gt;PrintBatch AND NoPickReason IN ('NG*','NG','ID*','ID','SS*','SS','ZO*','ZO') 
              GROUP BY CONVERT(DATETIME,CONVERT(NVARCHAR, PrintBatch, 101))) NP ON NP.TotalNonPickDate=CONVERT(NVARCHAR(10), TD.TempDate, 101)
  
      WHERE (ISNULL(C.QtyCancel,0) &gt; 0 OR ISNULL(PR.QtyPrinted,0) &gt; 0 OR ISNULL(SH.QtyShipped,0) + ISNULL(A.QtyAssigned,0) &gt; 0 OR 
          ISNULL(NP.[ID],0) &gt; 0 OR ISNULL(NP.[NG],0) &gt; 0 OR ISNULL(NP.[SS],0) &gt; 0 OR ISNULL(NP.[ZO],0) &gt; 0 ) 
  
      GROUP BY CONVERT(NVARCHAR(10), TD.TempDate, 101) WITH ROLLUP ORDER BY [Date]
  
  drop table #SSTempDates
  </code></pre>