Report for a restaurant showing hourly sales information for a given timespan I have been tasked with creating a report for a high-volume restaurant that shows hourly sales information for a given timespan. When generating this report for a single day, it's very quick, but when generated for even two days, the length of time grows tremendously.

I was originally doing subqueries to get the data that are shown as TempGiftCard and TempPayment in the WITH block at the top of this query. After doing some research online, I was under the impression that placing them in the WITH blocks will make them only execute once and the results would be reused. However, when I look at the Actual Execution Plan that SSMS produces, it doesn't appear that that is the case. According to the execution plan, about 60% of the time for this query to run is in the TempGiftCard portion of the query.

Running the full query for a single day takes about 1 second, but when running it for two days, it takes about 90-100 seconds.

Given this information, I know this is a query issue and not a data issue, but I'm not sure what else to try.

WITH TempGiftCard AS
(SELECT OrderTrackDate, OrderNum, SUM(ItemPrice) AS GiftCardsSold
    FROM RMArchive.dbo.OrderItems
    WHERE ItemName = 'GIFT CARD'
        AND OrderTrackDate BETWEEN '12/7/2015' AND '12/8/2015'
    GROUP BY OrderTrackDate, OrderNum),
TempPayment AS
(SELECT OrderNum, SUM(CASE WHEN PaymentCoupon = - 1 THEN PaymentAmount ELSE 0 END) AS Coupons,
                  SUM(CASE WHEN PaymentPromo = - 1 THEN PaymentAmount ELSE 0 END) AS Promo,
                  OrderTrackDate
    FROM RMArchive.dbo.Payment
    WHERE OrderTrackDate BETWEEN '12/7/2015' AND '12/8/2015'
    GROUP BY OrderNum, OrderTrackDate)
SELECT DATEPART(hour, OrderCloseTime) AS Hour,
       OrderType,
       SUM(OrderGuests) AS GuestCount,
       ROUND(CASE WHEN SUM(OrderGuests) = 0 THEN 0 ELSE SUM(OrderSubTotal) / SUM(OrderGuests) END, 2) AS AvgPerGuest,
       SUM(OrderSubTotal) AS Subtotal,
       ISNULL(SUM(Coupons), 0) * - 1 AS Coupons,
       ISNULL(SUM(Promo), 0) * - 1 AS Promo,
       SUM(OrderSubTotal) - (SUM(CASE WHEN OrderTaxExempt = - 1 THEN OrderSubTotal ELSE 0 END) + SUM(OrderNonTaxableAmount)) AS Taxable,
       SUM(CASE WHEN OrderTaxExempt = - 1 THEN OrderSubTotal ELSE 0 END) + SUM(OrderNonTaxableAmount) AS NonTaxable,
       ROUND(SUM(CASE WHEN OrderTaxExempt = 0 THEN OrderDueTax ELSE 0 END), 2) AS TaxCollected,
       SUM(OrderTaxableInc) AS TaxIncludedSales,
       SUM(OrderTaxInc) AS TaxIncluded,
       ROUND(SUM(OrderSubTotal) + SUM(OrderTaxableInc), 2) AS TotalNetSales,
       ROUND(SUM(CASE WHEN OrderTaxExempt = 0 THEN OrderDueTax ELSE 0 END) + SUM(OrderTaxInc), 2) AS TotalTax,
       ROUND(SUM(OrderSubTotal) + SUM(CASE WHEN OrderTaxExempt = 0 THEN OrderDueTax ELSE 0 END), 2) AS GrossSales,
       ISNULL(SUM(GiftCardsSold), 0) AS GiftCardsSold
FROM RMArchive.dbo.OrderInfo
LEFT JOIN TempPayment ON OrderInfo.OrderNum = TempPayment.OrderNum AND OrderInfo.OrderTrackDate = TempPayment.OrderTrackDate
LEFT JOIN TempGiftCard ON OrderInfo.OrderNum = TempGiftCard.OrderNum AND OrderInfo.OrderTrackDate = TempGiftCard.OrderTrackDate
WHERE OrderInfo.OrderTrackDate BETWEEN '12/7/2015' AND '12/8/2015' AND OrderType IS NOT NULL
GROUP BY DATEPART(hour, OrderCloseTime), OrderType
ORDER BY DATEPART(hour, OrderCloseTime), OrderType
OPTION (RECOMPILE);
Style

First, I like the compact style of the CTEs, and putting the closing
parentheses on the same line. I would say, though, that I find the
select statements a bit hard to read.  Especially the main one.

Use prefix aliases for columns

This is not standard SQL, but t-sql can do this: prefix aliases.
In general, when writing long expressions, it gets harder to read
the query and to find what you are looking for.  So:

SELECT DATEPART(hour, OrderCloseTime) AS Hour,
       OrderType,
       SUM(OrderGuests) AS GuestCount,
       ROUND(CASE WHEN SUM(OrderGuests) = 0 THEN 0 ELSE SUM(OrderSubTotal) / SUM(OrderGuests) END, 2) AS AvgPerGuest,
       SUM(OrderSubTotal) AS Subtotal,
       ISNULL(SUM(Coupons), 0) * - 1 AS Coupons,


can be more clear when you write it as

SELECT Hour = DATEPART(hour, OrderCloseTime)
     , OrderType
     , GuestCount = SUM(OrderGuests)
     , AvgPerGuest = ROUND(CASE WHEN SUM(OrderGuests) = 0 THEN 0 ELSE SUM(OrderSubTotal) / SUM(OrderGuests) END, 2)
     , Subtotal = SUM(OrderSubTotal)
     , Coupons = ISNULL(SUM(Coupons), 0) * - 1


You can even make it a bit more fancy and align the = signs, which
could improve readability if the expressions are much alike
(differences stand out more clearly), but would be harder to maintain.

Order columns consistently

Not just in the GROUP BY, but also in the SELECT list.  This
especially makes sense when you join on them: list the primary
key(s) in a consistent order.  This may be a minor pick, but it
helps when reviewing; when you see them at the top, you won't miss
them.  For now, I'm guessing that OrderTrackDate is a combined
key with OrderNum and that neither of them is unique by itself.

   WITH TempGiftCard AS
(SELECT OrderTrackDate
      , OrderNum
      , GiftCardsSold = SUM(ItemPrice)
   FROM RMArchive.dbo.OrderItems
  WHERE ItemName = 'GIFT CARD'
    AND OrderTrackDate BETWEEN '12/7/2015' AND '12/8/2015'
  GROUP BY OrderTrackDate, OrderNum)
      , TempPayment AS
(SELECT OrderTrackDate
      , OrderNum
      , Coupons = SUM(CASE WHEN PaymentCoupon = - 1 THEN PaymentAmount ELSE 0 END)
      , Promo   = SUM(CASE WHEN PaymentPromo  = - 1 THEN PaymentAmount ELSE 0 END)
   FROM RMArchive.dbo.Payment
  WHERE OrderTrackDate BETWEEN '12/7/2015' AND '12/8/2015'
  GROUP BY OrderTrackDate, OrderNum)


Use the ISO 8601 date format

At first when I read the query, I thought you accidentally queried
data for a whole month, but I guess I am used to the DD-MM-YYYY
format.  You might want to check whether the date format settings
of the database are correct, so that SQL interprets the strings
correctly.

If that setting may vary, you can always fall back on the ISO
notation, which will be interpreted correctly.

AND OrderTrackDate BETWEEN '2015-12-07' AND '2015-12-08'


If, for whatever reason, the OrderTrackDate column is
a char(10) instead of a date, you will certainly have queried a
whole month.  In that case, you would probably need to do something
with convert.  And as that does not index well, you might need to
SELECT the result of that convert into another table, first.

Use variables as constants or parameters

Both dates are specified three times.  If you declare two variables
at the top, you need to change them only in one place.  You could
also more easily use them as parameters if you go and implement the
query as a stored procedure or as a script you call from another
client (a client application you might write or maintain later).

DECLARE @startDate date = '2015-12-07';
DECLARE @endDate date = '2015-12-08';
-- ...
   AND OrderTrackDate BETWEEN @startDate AND @endDate


CTEs are not per definition more efficient

See also this excellent answer.  Syntactically, it looks so much
nicer to write a CTE instead of a temp table (which also needs
cleanup), but they are quite different to the SQL Server that has to
execute them.

In this case, it would probably help to rewrite both of the CTEs to
temp tables first, then join on them in the final query.

IF OBJECT_ID(N'tempdb..#TempGiftCard') IS NOT NULL DROP TABLE #TempGiftCard;
SELECT OrderTrackDate
     , OrderNum
     , GiftCardsSold = SUM(ItemPrice)
  INTO #TempGiftCard
  FROM RMArchive.dbo.OrderItems
-- ...


I cannot test this without table definitions, but I would not be
surprised if this helped performance.