Блог

У меня есть финансовое приложение. У меня есть ViewHistoricInstrumentValue , в котором есть такие строки

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

Мои представления сложны, но сама база данных невелика (4000 транзакций). ViewHistoricInstrumentValue был выполнен менее чем за 1 секунду до того, как я добавил следующий CTE в представление. После этого требуется 26 секунд. ActualEvaluationPrice это цена для 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
 

Я понятия не имею, что должен делать ваш запрос. Но этот процесс:

ActualEvaluationPrice — это цена для instrumentX на сегодняшний день. Если это значение отсутствует в таблице исторических цен, я нахожу предыдущую цену для instrumentX.

легко обрабатывается с помощью lag() :

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

Примечание: Если вам нужно отфильтровать определенные даты путем присоединения к ValidDates , вы можете включить JOIN их в запрос. Однако это не является частью инструкции о проблеме.