2

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

1
  • 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 Commented Mar 13, 2020 at 18:14

1 Answer 1

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)
1
  • 1
    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.
    – Brendan
    Commented Mar 15, 2020 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.