6
\$\begingroup\$

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);
\$\endgroup\$
1
  • \$\begingroup\$ Could you add a minimal definition of the tables/views OrderItems, Payment and OrderInfo? Knowing the column types and keys/constraints can make it easier to spot possible bottlenecks. \$\endgroup\$
    – joranvar
    Jan 2, 2016 at 16:21

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.