CTE очень медленно при входе - программирование

Я опубликовал что-то подобное раньше, но сейчас я приближаюсь к этому с другого направления, поэтому я открыл новый вопрос. Я надеюсь, что все в порядке.

Я работаю с CTE, который создает сумму сборов на основе родительской платы. SQL и детали можно увидеть здесь:

Я не думаю, что я что-то пропускаю в CTE, но у меня возникает проблема, когда я использую его с большой таблицей данных (3,5 миллиона строк).

В таблице tblChargeShare содержится другая информация, которая мне нужна, например InvoiceID, поэтому я поместил CTE в представление vwChargeShareSubCharges и присоединил его к таблице.

Я сохранил планы выполнения и загрузил их в SQL Sentry. Дерево для быстрого запроса выглядит следующим образом:

Я попытался переиндексировать, выполнив запрос через советник по настройке и различные комбинации подзапросов. Всякий раз, когда соединение содержит что-либо, кроме ПК, запрос выполняется медленно.

Какие используемые функции выполняют summimg дочерних строк вместо CTE. Это переписать с помощью CTE, чтобы попытаться избежать той же проблемы, с которой я сейчас сталкиваюсь. Я прочитал ответы в этом ответе, но я не мудрее - я прочитал некоторую информацию о подсказках и параметрах, но я не могу заставить его работать. Я думал, что переписывание с использованием CTE решит мою проблему. Запрос выполняется быстро при запуске на tblCharge с несколькими тысячами строк.

Я сконфигурировал запрос в один оператор, но та же проблема сохраняется:

Изменить: Больше играть, я просто не понимаю этого.

Даже если я помещаю кучи чисел в "В", запрос все еще мгновен:

Я могу воспроизвести это с нуля, используя данные этого примера:

Я создал некоторые фиктивные данные для репликации проблемы. Это не так важно, так как я добавил только 100 000 строк, но плохой план выполнения все еще выполняется (выполняется в режиме SQLCMD):

Ответы

Ответ 1

Лучший SQL Server для вас может сделать это, чтобы нажать фильтр на ChargeID вниз в анкерную часть рекурсивного CTE внутри представления. Это позволяет искать единственную строку, из которой вы должны построить иерархию. Когда вы предоставляете параметр как постоянное значение, SQL Server может сделать эту оптимизацию (используя правило, называемое SelOnIterator, для тех, кто заинтересован в таких вещах):

Когда вы используете локальную переменную, она не может этого сделать, поэтому предикат на ChargeID застревает вне представления (который строит полную иерархию, начиная со всех NULL ids):

Один из способов получить оптимальный план при использовании переменной - заставить оптимизатор компилировать новый план при каждом выполнении. Полученный план затем адаптируется к конкретному значению переменной во время выполнения. Это достигается добавлением подсказки OPTION (RECOMPILE):

Declare @ChargeID int = 60900;

-- Produces a fast execution plan, at the cost of a compile on every execution
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = @ChargeID
OPTION (RECOMPILE);

Второй вариант - изменить представление на встроенную функцию таблицы. Это позволяет явно указать положение предиката фильтрации:

CREATE FUNCTION [dbo].[udfChargeShareSubCharges]
(
    @ChargeID int
)
RETURNS TABLE AS RETURN
(
  WITH RCTE AS
  (
  SELECT  ParentChargeID, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,   ISNULL(TaxAmount, 0) as TaxAmount,  
  ISNULL(DiscountAmount, 0) as DiscountAmount,  ChargeID as MasterChargeID
  FROM tblChargeTest 
  Where ParentChargeID is NULL 
  AND ChargeID = @ChargeID -- Filter placed here explicitly

  UNION ALL

  SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0),    ISNULL(rh.TaxAmount, 0),  ISNULL(rh.DiscountAmount, 0) 
  , rc.MasterChargeID 
  FROM tblChargeTest rh
  INNER JOIN RCTE rc ON rh.ParentChargeID = rc.ChargeID --and rh.CustomerID =  rc.CustomerID 
  )

  Select MasterChargeID,  ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
  FROM  RCTE r 
)

Используйте его следующим образом:

Declare @ChargeID int = 60900

select *
from dbo.udfChargeShareSubCharges(@ChargeID)

Запрос также может извлечь выгоду из индекса на ParentChargeID.

create index ix_ParentChargeID on tblChargeTest(ParentChargeID)

Вот еще один ответ о аналогичном правиле оптимизации в аналогичном сценарии. Оптимизация планов выполнения для параметризованных запросов T-SQL, содержащих функции окна

Ответ 2

Далее, чтобы найти решение, я бы рекомендовал ВЫБРАТЬ В CTE во временную таблицу и присоединиться оттуда. Из личного опыта, связанного с CTE, мой запрос возвращался в течение 5 минут, в то время как простая вставка данных, сгенерированных CTE, во временную таблицу снизила их до 4 секунд. Я фактически соединял два CTE вместе, но я думаю, что это применимо ко всем долго выполняющимся запросам, когда CTE присоединяется к таблице LONG (особенно к внешним соединениям).


    --temp tables if needed to work with intermediate values
    If object_id('tempdb..#p') is not null
    drop table #p

    ;WITH cte as ( 
    select * from t1
    )

    select * 
    into #p
    from cte

    --then use the temp table as you would normally use the CTE
    select * from #p