我有财务申请。我有ViewHistoricInstrumentValue这样的行

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

我的观点很复杂，但数据库本身很小（4000个事务）。ViewHistoricInstrumentValue在我将下一个CTE添加到视图之前，它在不到1秒的时间内执行了。之后需要26秒。ActualEvaluationPrice是dateX的instrumentX价格。如果HistoricPrice表中缺少该值，那么我会找到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
)

我的问题是执行时间不必要地增加了。现在，HistoricPrice表没有缺失值，因此ActualEvaluationPrice永远不会为null，因此永远都不能确定先前的价格。

ViewHistoricInstrumentValue返回1815行。另一个奥秘是，第一个查询需要26秒，而第二个查询只有2秒。

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

附录

执行计划：https : //www.dropbox.com/s/5st69uhjkpd3b5y/IsNull.sqlplan?dl=0

相同的计划：https : //www.brentozar.com/pastetheplan/?id=rk9bK1Wiv

风景：

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