Performance issue using IsNull function in the Select statement

I have a financial application. I have ViewHistoricInstrumentValue which has rows like this

instrument1, date1, price, grossValue, netValue
instrument2, date1, price, grossValue, netValue
...
instrument1, date2, price, grossValue, netValue
...

My views are complicated but the db itself is small (4000 transactions). ViewHistoricInstrumentValue was executed in less than 1 second before I added the next CTE to the view. After that it takes 26s. ActualEvaluationPrice is the price for instrumentX at dateY. If this value is missing from HistoricPrice table then I find the previous price for instrumentX.

, UsedEvaluationPriceCte AS (
SELECT *
    , isnull(ActualEvaluationPrice, 
        (select top 1 HistoricPrice.Price -- PreviousPrice
           from HistoricPrice JOIN ValidDate 
            on HistoricPrice.DateId = ValidDate.Id 
                and HistoricPrice.InstrumentId = StartingCte.InstrumentId
                and ValidDate.[Date] < StartingCte.DateValue
            order by ValidDate.[Date])) 
       as UsedEvaluationPrice
FROM StartingCte
)

My problem is that the execution time increased needlessly. Right now the HistoricPrice table has no missing value so ActualEvaluationPrice is never null, so the previous price should be never determined.

ViewHistoricInstrumentValue returns 1815 rows. One other mystery is that the first query takes 26s, but the second only 2s.

SELECT * FROM [ViewHistoricInstrumentValue]
SELECT top(2000) * FROM [ViewHistoricInstrumentValue]

Appendix

The execution plan: https://www.dropbox.com/s/5st69uhjkpd3b5y/IsNull.sqlplan?dl=0

The same plan: https://www.brentozar.com/pastetheplan/?id=rk9bK1Wiv

The view:

ALTER VIEW [dbo].[ViewHistoricInstrumentValue] AS 
WITH StartingCte AS (
    SELECT
        HistoricInstrumentValue.DateId
        , ValidDate.Date as DateValue
        , TransactionId
        , TransactionId AS [Row]
        , AccountId
        , AccountName
        , ViewTransaction.InstrumentId
        , ViewTransaction.InstrumentName
        , OpeningDate
        , OpeningPrice
        , Price AS ActualEvaluationPrice
        , ClosingDate
        , Amount        
        , isnull(ViewTransaction.FeeValue, 0) as FeeValue
        , HistoricInstrumentValue.Id AS Id
    FROM ViewBriefHistoricInstrumentValue as HistoricInstrumentValue 
    JOIN ValidDate on HistoricInstrumentValue.DateId = ValidDate.Id
    JOIN ViewTransaction ON ViewTransaction.Id = HistoricInstrumentValue.TransactionId
    left JOIN ViewHistoricPrice ON ViewHistoricPrice.DateId = HistoricInstrumentValue.DateId AND
        ViewHistoricPrice.InstrumentId = ViewTransaction.InstrumentId
)
, UsedEvaluationPriceCte AS (
    SELECT *
        , isnull(ActualEvaluationPrice, 
            (select top 1 HistoricPrice.Price -- PreviousPrice
               from HistoricPrice JOIN ValidDate 
                on HistoricPrice.DateId = ValidDate.Id 
                    and HistoricPrice.InstrumentId = StartingCte.InstrumentId
                    and ValidDate.[Date] < StartingCte.DateValue
                order by ValidDate.[Date])) 
           as UsedEvaluationPrice
    FROM StartingCte
)
, GrossEvaluationValueCte AS (
    SELECT *
        , Amount * UsedEvaluationPrice AS GrossEvaluationValue
        , (UsedEvaluationPrice - OpeningPrice) * Amount AS GrossCapitalGains
    FROM UsedEvaluationPriceCte
)
, CapitalGainsTaxCte AS (
    SELECT *
        , dbo.MyMax(GrossCapitalGains * 0.15, 0) AS CapitalGainsTax
    FROM GrossEvaluationValueCte    
)
, IsOpenCte AS (
    SELECT
        DateId
        , DateValue
        , TransactionId
        , [Row]
        , AccountId
        , AccountName
        , InstrumentId
        , InstrumentName
        , OpeningDate
        , OpeningPrice
        , ActualEvaluationPrice
        , UsedEvaluationPrice
        , ClosingDate
        , Amount
        , GrossEvaluationValue 
        , GrossCapitalGains
        , CapitalGainsTax 
        , FeeValue
        , GrossEvaluationValue - CapitalGainsTax - FeeValue AS NetEvaluationValue
        , GrossCapitalGains - CapitalGainsTax - FeeValue AS NetUnrealizedGains
        , CASE WHEN ClosingDate IS NULL OR DateValue < ClosingDate
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
        END 
        AS IsOpen
        , convert(NVARCHAR, DateValue, 20) + cast([Id] AS NVARCHAR(MAX)) AS Temp
        , Id    
    FROM CapitalGainsTaxCte
)
Select * from IsOpenCte

Answer

I have no idea what your query is supposed to be doing. But this process:

ActualEvaluationPrice is the price for instrumentX at dateY. If this value is missing from HistoricPrice table then I find the previous price for instrumentX.

is handled easily with lag():

select vhiv.*
       coalesce(vhiv.ActualEvaluationPrice,
                lag(vhiv.ActualEvaluationPrice) over (partition by vhiv.InstrumentId order by DateValue)
               ) as UsedEvaluationPrice
from ViewHistoricInstrumentValue vhiv;

Note: If you need to filter out certain dates by joining to ValidDates, you can include the JOIN in the query. However, that is not part of the problem statement.

Advertisement

Answer