Nested Loop Output Estimate Low when expanding dataset

Question

I have a stored procedure that is taking some header and details information of a tactic, that goes on to expand the data from a date range (Start/End) to the day level. This is then used further on in the procedure to calculate some day-level weightings. As I've been performance tuning, I've noticed one specific issue that I can't seem to solve.

If I have a "Tactic" that covers a year's worth of dates and the tactic item has 10,000 combinations that all need to expand out to the day level, I seem to get a low row estimate on the output of the nested loop. Investigating my plan, I believe it seems to be estimating the correct number of iterations at the nested loop, but at the output and subsequent inserts seem to be low. Is there something that I'm missing to gain better cardinality estimates?

Here's an extract of the behaviour:

DROP TABLE IF EXISTS dbo.Calendar
DROP TABLE IF EXISTS dbo.Tactic
DROP TABLE IF EXISTS dbo.TacticItems
CREATE TABLE dbo.Calendar (
 ddate DATE NOT NULL
 , Day_Idx INT 
 , PRIMARY KEY (Day_Idx)
)
CREATE TABLE dbo.Tactic (
 TacticId INT NOT NULL PRIMARY KEY
, From_Day_Idx INT NOT NULL
, To_Day_idx INT NOT NULL
)
CREATE TABLE dbo.TacticItems (
 TacticId INT NOT NULL
, ItemKey_A INT NOT NULL
, ItemKey_B INT NOT NULL
, From_Day_Idx INT NOT NULL
, To_Day_idx INT NOT NULL
, PRIMARY KEY (TacticId, ItemKey_A, ItemKey_B)
)
/*
 Prime Dates For the Year
*/
INSERT INTO dbo.Calendar (ddate, Day_Idx)
SELECT ddate = DATEADD(DAY, A.Increment, A.Base_Date)
 , Day_Idx = CONVERT(INT,CONVERT(CHAR(8),DATEADD(DAY, A.Increment, A.Base_Date),(112))) 
FROM (
 SELECT TOP 366
 Base_Date = CONVERT(DATE, '2019-12-31')
 , Increment = ROW_NUMBER() OVER (ORDER BY Object_Id)
 FROM sys.objects
 ) A
INSERT INTO dbo.Tactic 
 (TacticId , From_Day_Idx, To_Day_idx)
VALUES (29 , 20200101 , 20200630)
INSERT INTO dbo.TacticItems
 (
 TacticId
 , ItemKey_A
 , ItemKey_B
 , From_Day_Idx
 , To_Day_idx
 )
SELECT 1
 , A.ItemKey_A
 , B.ItemKey_B
 , 20200101
 , 20201231
FROM (SELECT TOP 63 ItemKey_A = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) A
CROSS JOIN (SELECT TOP 162 ItemKey_B = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) B
DROP TABLE IF EXISTS #Results
SELECT *
INTO #Results
FROM dbo.TacticItems TI
INNER JOIN dbo.Calendar CAL
ON CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

Here is the plan that i'm getting: enter image description here

And the nested loop input that seems to be correct: enter image description here

• Martin Smith: The bad estimates coming out the join probably don't really affect anything here anyway as the only operator afterwards is inserting to a heap but you may well find OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) improves things

Answer 1

That's the optimal plan for that query. So you should consider storing the result. One easy way is to use an Indexed View.

create view TacticItemsByDay
with schemabinding
as
SELECT [TacticId], [ItemKey_A], [ItemKey_B], [From_Day_Idx], [To_Day_idx], [ddate], [Day_Idx] 
--INTO #Results
FROM dbo.TacticItems TI
INNER JOIN dbo.Calendar CAL
ON CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx
go
create unique clustered index ix_Results on TacticItemsByDay(TacticId, ItemKey_A, ItemKey_B, Day_Idx)

• Brendan: Thank you for the response that my execution plan is fine. I've tested integrating the indexed view in to the stored procedure that my example was extracted from, but the overheads of deletion and insertion in to the view are greater than bringing it in to a temporary table in the procedure and then using it.