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

avatar image By accidentaldba 1 asked Mar 18, 2014 at 06:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered Mar 18, 2014 at 07:33 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Mar 18, 2014 at 08:05 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117

asked: Mar 18, 2014 at 06:51 PM

Seen: 237 times

Last Updated: Mar 18, 2014 at 08:39 PM