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.
By SQLkiwi ♦ 6.6k answered Mar 18, 2014 at 07:33 PM
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...
A filtered index may help, if many rows exist where
If there are very few rows where
Both are worth trying on a test system, as well as moving
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.
By Aaron Bertrand ♦ 1.7k answered Mar 18, 2014 at 08:05 PM