Оператор сортировки требует больше затрат на запрос

Таблица расписания существует каждый день, в эту таблицу будет вставлен набор расписаний для многих клиентов, Структура таблицы будет примерно такой

Create table factSchedule (factID bigint identity (1,1) primary key, custId char(10), scheduleDate date, amount money, LoadedOn date)

скажем,

На одного клиента будет более 100 записей в расписании.

В день будет добавлено 10 000 клиентов с соответствующими записями в расписании.

Эта таблица будет обновляться ежедневно путем вставки обновленных записей расписания, скажем, для первой загрузки на дату будет 100 записей для клиента, а на второй день загрузки на дату будут те же 100 записей, но с разными суммами, поэтому в конечном итоге будет ~ 200 записей в два дня на одного покупателя, так что вы можете представить, насколько большой будет этот стол ...

Теперь у меня есть SP, который будет возвращать некоторые вычисленные поля корзины на основе самого первого scheduleDate для заданной даты ввода в таблице factSchedule. Итак, сначала мне нужна таблица cte, которая будет содержать только первую дату расписания каждого custId для данного поля LoadedOn в качестве входного параметра, как показано ниже;

;with cteSchedule as
( SELECT result.*
     (select custId, scheduleDate, Amount,@inputDate as compareDate, row_number ()over ( partition by loadedon, custId order by loadedon, custId, scheduleDate)rownum From factSchedule Where LoadedOn = @inputDate and scheduleDate >= @ inputDate)
) as result
WHERE result.rownum = 1

Теперь у меня есть еще одна таблица, называемая Customertable, в этой таблице будет одна запись для каждого custId, поэтому в этой таблице примерно 10 000 клиентов.

так вот мой SP идет,

    ;with cteSchedule as
( SELECT result.*
     (select custId, scheduleDate, Amount,@inputDate as compareDate, row_number ()over ( partition by loadedon, custId order by loadedon, custId, scheduleDate)rownum From factSchedule Where LoadedOn = @inputDate and scheduleDate >= @ inputDate)
) as result
WHERE result.rownum = 1

Select Maintable.CustID,
(select case when scheduleDate < dateadd (day,7,compareday)
             then ‘Customertable.someAmount ’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_0to7_days,

  (select case when ScheduleDate >= DATEADD (DAY,7,compareday) AND ScheduleDate <= DATEADD (MONTH,1,compareday)
             then ‘Customertable.someAmount’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_7to30_days,

    (select case when ScheduleDate >= DATEADD (MONTH,1,compareday) AND ScheduleDate <= DATEADD (MONTH,3,compareday)
             then ‘Customertable.someAmount’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_1to3_Months,

 -- "like wise it will have another five buckets."

 From Customertable 

А ниже приведен план выполнения для этого SP, он показывает, что оператор сортировки в моей первой таблице cteSchedule берет 10% стоимости запроса для каждого сегмента, поэтому всего 8 сегментов и 80% затрат на запрос, это занимает так много времени (подробнее чем 1 час). Также что означает предупреждающий знак (оператор использовал tempDb для разлива данных во время выполнения с уровнем порога 1) в операторе сортировки? Оператор сортировки требует больше затрат на запрос

Есть ли другой способ уменьшить стоимость запроса этого оператора сортировки или как-нибудь переписать логику? в любом случае помощь будет очень признательна. спасибо заранее

Актуальный код:

declare @RunDateLocal date = '20180709' 


;with cteSchedule AS
(SELECT  schedule.*
    FROM (select ContractIDKey,ScheduleDateKey, @RunDateLocal as compareDay,row_number ()over ( partition by loadedon,ContractIDKey order by loadedon,ContractIDKey,ScheduleDateKey)rownum
    from FactSchedules
    where  LoadedOn = DATEADD(D,1,@RunDateLocal) and ScheduleDateKey>= @RunDateLocal) as schedule
 WHERE schedule.rownum = 1
)

select FB.ContractIDKey,

(select case when ScheduleDateKey < DATEADD (DAY,7,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_0To7_Days,

(select case when ScheduleDateKey >= DATEADD (DAY,7,compareday) AND ScheduleDateKey <= DATEADD (MONTH,1,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_7To30_Days,

(select case when ScheduleDateKey > DATEADD (MONTH,1,compareday) AND ScheduleDateKey <= DATEADD (MONTH,3,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_1To3_Months,

(select case when ScheduleDateKey > DATEADD (MONTH,3,compareday) AND ScheduleDateKey <= DATEADD (MONTH,6,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_3To6_Months,

(select case when ScheduleDateKey > DATEADD (MONTH,6,compareday) AND ScheduleDateKey <= DATEADD (MONTH,12,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_6To12_Months,

(select case when ScheduleDateKey > DATEADD (YEAR,1,compareday) AND ScheduleDateKey <= DATEADD (YEAR,3,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_1To3_Years,

(select case when ScheduleDateKey > DATEADD (YEAR,3,compareday) AND ScheduleDateKey <= DATEADD (YEAR,5,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_3To5_Years,

(select case when ScheduleDateKey > DATEADD (YEAR,5,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_5Over_years

from  FactBalances FB 
LEFT JOIN vAllDeposists_AIAAmount AIA ON AIA.ContractIDKey = FB.ContractIDKey

Сортировка, как правило, - дорогостоящая операция, но я бы не стал доверять предполагаемой стоимости. Вы тестировали время выполнения с сортировкой / без сортировки? Вам действительно нужна сортировка в SQL или приложение может это сделать?

Jacob H 10.08.2018 13:30

Этот SQL даже недействителен. В вашем самом первом CTE нет пункта FROM. Также есть FROM с предложением AND: From factSchedule and cteSchedule.custId =.... В этом нет никакого смысла. Отправьте действительный SQL. Если вы не можете предоставить нам это, мы не можем надеяться на его отладку. (Кстати, ; - это терминатор оператора, а не "начальный указатель". Он идет в конце вашего оператора, а не в начале. :))

Larnu 10.08.2018 13:31

@JacobH стоимость запроса связана с порядком, используемым в функции row_number в cteSchedule, и мне нужен этот cte, чтобы выбрать самую первую запись расписания, которую нужно сравнить в более поздней части SP, поэтому я не мог игнорировать этот row_number используется в cte. Я выложу актуальный код

Mar1009 10.08.2018 13:43

@Larnu Я добавил сам код, пожалуйста, посмотрите.

Mar1009 10.08.2018 13:44

У меня все еще возникают синтаксические ошибки. Например, в строке from cteschedule where rnk = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)) end вторая правая скобка не имеет соответствующей левой скобки. Кроме того, начало выражения CASE - это скобка внутри, но END находится снаружи. Кажется, это повторяется в вашем коде. Этот SQL все еще пронизан синтаксическими ошибками.

Larnu 10.08.2018 13:48

@ Mar1009 это не sort дорого. Вы более или менее повторяете запрос такой же снова и снова. Применяя DATEADD() к compareday, вы сила SQL Server сканируете всю таблицу или индекс, вычисляете результаты и затем сортируете, чтобы их можно было объединить с внешней таблицей.

Panagiotis Kanavos 10.08.2018 13:49

@ Mar1009 вы могли бы упростить запрос много, если бы вы использовали таблицу Calendar и присоединились к ней вместо того, чтобы пытаться вычислять даты на лету. Вы можете сгруппировать данные по годам, месяцам, семестрам или любому другому периоду календарной таблицы. Используйте WITH CUBE или WITH ROLLUP для расчета агрегатов более высокого уровня и т. д.

Panagiotis Kanavos 10.08.2018 13:52

@Larnu Извините за синтаксические ошибки, я исправил эти синтаксические ошибки, посмотрите, пожалуйста.

Mar1009 10.08.2018 13:53

Кажется, что создание кластерного индекса на FactSchedules (loadedon, ContractIDKey, ScheduleDateKey) может помочь

Denis Rubashkin 10.08.2018 13:58

Загрузите свой фактический план выполнения showplan xml в brentozar.com/pastetheplan и добавьте эту ссылку в свой вопрос.

Dan Guzman 10.08.2018 14:14

Спасибо @PanagiotisKanavos, это сработало, я поместил этот cte в таблицу temp и использовал его в моем SP, и теперь оператор сортировки давно исчез.

Mar1009 11.08.2018 16:40
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
11
209
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы пробовали материализовать свой набор данных в таблице temp вместо использования cte? Общее табличное выражение будет запускать sql каждый раз, когда оно используется, тогда как временная таблица запускает его только один раз и повторно использует тот же набор результатов. Такой подход должен сократить количество сортировок (до 1).

Разливы часто связаны с плохой статистикой. Проверьте предполагаемый и фактический планы выполнения, чтобы убедиться в этом. Отказ от cte мог тоже помогает, так как мог перестраивает статистику. Трудно сказать наверняка, не потрудившись.

Да, это сработало, когда я использовал временную таблицу вместо CTE, и теперь оператор сортировки исчез. Также спасибо за заметку о разливах

Mar1009 11.08.2018 16:42

Для запроса в вашем cte, поскольку LoadedOn всегда будет одинаковым, нет необходимости разбивать или упорядочивать по этому столбцу. Кроме того, при разбиении с помощью ContractIDKey вам не нужно упорядочивать по этому столбцу.

Нет необходимости запрашивать CTE или временную таблицу 8 раз. Запрос, который вы использовали для исходного cte, уже вернул только 1 запись на ContractIDKey, поэтому достаточно (внешнего) присоединиться к cte один раз. Предлагаю протестировать такой запрос (с cte или с временной таблицей):

DECLARE @RunDateLocal date = '20180709';

WITH cteSchedule AS
(SELECT  ContractIDKey, ScheduleDateKey
    FROM (select ContractIDKey, ScheduleDateKey, row_number() over (partition by ContractIDKey ORDER BY ScheduleDateKey) rownum
    from FactSchedules
    where  LoadedOn = DATEADD(D,1,@RunDateLocal) and ScheduleDateKey>= @RunDateLocal) as schedule
 WHERE schedule.rownum = 1
)

SELECT 
  FB.ContractIDKey,

  case
    when cte.ScheduleDateKey < DATEADD(DAY,7,@RunDateLocal)
    then ISNULL(AIA.Closingbalance, 0)
    else 0
  end as Interest_0To7_Days,

  case
    when cte.ScheduleDateKey >= DATEADD(DAY,7,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,1,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_7To30_Days,

  case
    when ScheduleDateKey > DATEADD(MONTH,1,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,3,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_1To3_Months,

  case
    when ScheduleDateKey > DATEADD(MONTH,3,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,6,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0 end Interest_3To6_Months,

  case
    when ScheduleDateKey > DATEADD(MONTH,6,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,12,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_6To12_Months,

  case
    when ScheduleDateKey > DATEADD(YEAR,1,@RunDateLocal) AND ScheduleDateKey <= DATEADD(YEAR,3,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_1To3_Years,

  case
    when ScheduleDateKey > DATEADD(YEAR,3,@RunDateLocal) AND ScheduleDateKey <= DATEADD(YEAR,5,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_3To5_Years,

  case
    when ScheduleDateKey > DATEADD(YEAR,5,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_5Over_years

FROM  FactBalances FB 
  LEFT JOIN cteSchedule cte ON cte.ContractIDKey = FB.ContractIDKey
  LEFT JOIN vAllDeposists_AIAAmount AIA ON AIA.ContractIDKey = FB.ContractIDKey

Другие вопросы по теме