The actual execution plan for my Azure SQL Database stored procedure indicated the following warning:
So I went and added a persisted calculated column in dbo.Interest_rate_changes like this:
[Effective_date] AS (CONVERT([date],CONVERT([nchar](8),[Effective_date_int]),(112))) PERSISTED NOT NULL
I then replaced all references to Interest_date_int in the stored procedure with references to Interest_date.
I ran the query again but the same warning appeared in the actual execution plan, even though no reference to Effective_date_int now exists in the stored procedure.
I tried to clear the cache with DBCC FREEPROCCACHE but Azure SQL Database will not allow that.
What am I doing wrong?
In response to comments, the actual execution plan is here.
The query is this:
EXEC TEST_PopulateCachedDailyInterest 11,'2017-12-31'
The stored procedure is this (apologies I am not a pro):
CREATE PROCEDURE [dbo].[TEST_PopulateCachedDailyInterest] (@entityid int = 0, @enddate date)
AS
SET NOCOUNT ON;
TRUNCATE TABLE TEST_CachedDailyInterest;
WITH A AS
(
SELECT
MyEntity AS Entity,
MyInstrument AS Instrument,
Accrual_date AS AccrualDate,
Balance_x_Par_value_x_Effective_rate AS AccrualNumerator,
Yearfrac_reciprocal AS AccrualDenominator
FROM
(SELECT
MyEntity,
MyInstrument,
Instrument_currency,
Interest_convention,
Yearfrac_date_shift,
Calendar_date AS Accrual_date,
(SELECT SUM(Units) FROM Unit_transactions_indexed WITH (NOEXPAND) WHERE Entity = MyEntity AND Instrument = MyInstrument AND DATEADD(day,Opening_balance_date_shift,Transaction_date) < Calendar_date) * Par_value *
(SELECT TOP 1 Interest_rate FROM Interest_rate_changes WHERE Instrument = MyInstrument AND DATEADD(day,Interest_date_shift, Effective_date) <= Calendar_date ORDER BY Effective_date DESC) AS Balance_x_Par_value_x_Effective_rate
FROM
(SELECT MyEntity, MyInstrument, Min_date, Max_date, Opening_balance_date_shift, Interest_date_shift, Interest_convention, Yearfrac_date_shift, Par_value, Instrument_currency FROM
(SELECT MyEntity, MyInstrument, DATEADD(day,-3,MIN(Transaction_date)) AS Min_date, IIF(SUM(Units)<>0, @enddate, IIF(DATEADD(day,3,MAX(Transaction_date))>=@enddate, @enddate, DATEADD(day,3,MAX(Transaction_date)))) AS Max_date
FROM
(SELECT MyEntity, MyInstrument, Transaction_date, Units FROM
(SELECT Entity AS MyEntity, Instrument AS MyInstrument, Transaction_date, Units FROM Unit_transactions_indexed WITH (NOEXPAND)
WHERE Entity = @entityid AND Units <> 0 AND Units IS NOT NULL) AS A
INNER JOIN
(SELECT ID FROM Instruments WHERE Interest_type>0) B
ON A.MyInstrument = B.ID) C
GROUP BY MyEntity, MyInstrument) D
INNER JOIN Instruments ON D.MyInstrument = Instruments.ID
INNER JOIN Interest_types ON Interest_type = Interest_types.ID) AS D
CROSS JOIN Calendar_dates WHERE Calendar_date BETWEEN Min_date AND Max_date) AS F
INNER JOIN Yearfracs_reciprocal WITH (NOEXPAND) ON Balance_x_Par_value_x_Effective_rate<>0 AND
Balance_x_Par_value_x_Effective_rate IS NOT NULL AND
Yearfrac_reciprocal IS NOT NULL AND
DATEADD(day,-F.Yearfrac_date_shift,F.Accrual_date) = Yearfracs_reciprocal.Calendar_date AND
Interest_convention = Convention_ID
)
INSERT INTO TEST_CachedDailyInterest
SELECT
Entity AS EntityId,
Instrument AS InstrumentId,
AccrualDate,
AccrualNumerator,
AccrualDenominator
FROM A
WHERE ISNULL(AccrualNumerator,0)<>0
The table schema for dbo.Interest_rate_changes is this:
CREATE TABLE [dbo].[Interest_rate_changes](
[Instrument] [int] NOT NULL,
[Effective_date_int] [int] NOT NULL,
[Interest_rate] [decimal](9, 7) NOT NULL,
[Effective_date] AS (CONVERT([date],CONVERT([nchar](8),[Effective_date_int]),(112))) PERSISTED NOT NULL,
CONSTRAINT [PK_Instrument_Date] PRIMARY KEY CLUSTERED
(
[Instrument] ASC,
[Effective_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
