Why is it doing a Eager Spool followed by a Lazy Spool?

accidentaldba 2014-03-18 18:51:13

/storage/attachments/807-tmp64E9.queryanalysis
SQLkiwi 2014-03-18 19:33:33
The eager spool builds a temporary index on the PeriodCalendar_Weeks table. This happens eagerly, and just once.

The lazy spool caches the result of the subtree below it, in case the correlated parameter of the nested loop join (SalesDate) does not change between nested loop iterations.

Reading a cached result from the lazy spool will be cheaper than reevaluating the Filter and re-seeking the index built by the eager spool.

Aaron Bertrand 2014-03-18 20:05:14
Paul has explained the title of your question, but if your goal is to improve the query, much more efficient to do this without having to go back and join to the periods table. Once you know the range, it is easy enough to interpolate all of the potential dates in between…

DECLARE @StartDate DATE, @EndDate DATE;
 
Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate) 
from dbo.PeriodCalendar_Weeks pcw
where (pcw.Year = @Year and pcw.Period < @Period) 
  or  (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week) 
  or (pcw.Year = @Year -1 and pcw.Period >= @Period);
 
SELECT 
  WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)), 
  Store, 
  DeliveryChargesTotal = dct
FROM 
(
  SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, 
    SUM(DeliveryChargesTotal)
  FROM dbo.Daily_GC_Headers
  WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0
  GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store
) AS x (SalesWeek, Store, dct)
ORDER BY WeekEndDate, Store;

A filtered index may help, if many rows exist where isCanceled = 1 (these are just possible suggestions, depending on cardinality of Store, and may not be the most optimal):

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, DeliveryChargesTotal)
  WHERE isCanceled = 0;

If there are very few rows where isCanceled = 1, this may be better:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);

Both are worth trying on a test system, as well as moving Store into the key in either case, or moving IsCanceled to the INCLUDE list in the latter case. On my system, I found the best results with everything but the date in the INCLUDE list:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);

Again, you will need to test if any of these work out, or if the query above gives a different/better recommendation directly from SQL Server.

March 18, 2014
No Comments