Much more efficient to do this without having to go back and join to the periods table.
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.
Best Answer
This is a little broad but I think I understand the True Question and will answer accordingly. Just going to talk about table vs index spool though. I don't think it's quite correct to view there as being a choice between table and index spools. As you know, it's possible in a single subtree to get an index spool, a table spool, or both an index spool and a table spool. I believe it's generally correct to say that you get an index spool under the following conditions:
You can see most of these with simple demos. Start by creating a pair of heaps:
For the first query, there's nothing to seek on:
So there's no reason for the optimizer to transform the join into an apply. You end up with a table spool due to costing reasons. So this query fails the first test.
For the next query, it's fair to expect that the optimizer has a reason to consider an apply:
But it's not meant to be:
This query fails the second test. A complete explanation is here. Quoting the most relevant part:
I can rewrite the query to encourage the optimizer consider an apply:
But there's still no index spool:
This query fails the third test. In SQL Server 2014 there was an index key length limit of 900 bytes. This was extended in SQL Server 2016 but only for nonclustered indexes. The index for a spool is a clustered index so the limit remains at 900 bytes. In any case, the index spool rule can't be applied because it could lead to an error during query execution.
Reducing the data type length to 800 finally provides a plan with an index spool:
The index spool plan, not surprisingly, is costed significantly cheaper than a plan with no spool: 89.7603 units vs 598.832 units. You can see the difference with the undocumented
QUERYRULEOFF BuildSpool
query hint:This isn't a complete answer, but hopefully it's some of what you were looking for.