Таблица расписания существует каждый день, в эту таблицу будет вставлен набор расписаний для многих клиентов, Структура таблицы будет примерно такой
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 даже недействителен. В вашем самом первом CTE нет пункта FROM
. Также есть FROM
с предложением AND
: From factSchedule and cteSchedule.custId =...
. В этом нет никакого смысла. Отправьте действительный SQL. Если вы не можете предоставить нам это, мы не можем надеяться на его отладку. (Кстати, ;
- это терминатор оператора, а не "начальный указатель". Он идет в конце вашего оператора, а не в начале. :))
@JacobH стоимость запроса связана с порядком, используемым в функции row_number в cteSchedule, и мне нужен этот cte, чтобы выбрать самую первую запись расписания, которую нужно сравнить в более поздней части SP, поэтому я не мог игнорировать этот row_number используется в cte. Я выложу актуальный код
@Larnu Я добавил сам код, пожалуйста, посмотрите.
У меня все еще возникают синтаксические ошибки. Например, в строке from cteschedule where rnk = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)) end
вторая правая скобка не имеет соответствующей левой скобки. Кроме того, начало выражения CASE
- это скобка внутри, но END
находится снаружи. Кажется, это повторяется в вашем коде. Этот SQL все еще пронизан синтаксическими ошибками.
@ Mar1009 это не sort
дорого. Вы более или менее повторяете запрос такой же снова и снова. Применяя DATEADD()
к compareday
, вы сила SQL Server сканируете всю таблицу или индекс, вычисляете результаты и затем сортируете, чтобы их можно было объединить с внешней таблицей.
@ Mar1009 вы могли бы упростить запрос много, если бы вы использовали таблицу Calendar и присоединились к ней вместо того, чтобы пытаться вычислять даты на лету. Вы можете сгруппировать данные по годам, месяцам, семестрам или любому другому периоду календарной таблицы. Используйте WITH CUBE
или WITH ROLLUP
для расчета агрегатов более высокого уровня и т. д.
@Larnu Извините за синтаксические ошибки, я исправил эти синтаксические ошибки, посмотрите, пожалуйста.
Кажется, что создание кластерного индекса на FactSchedules (loadedon, ContractIDKey, ScheduleDateKey) может помочь
Загрузите свой фактический план выполнения showplan xml в brentozar.com/pastetheplan и добавьте эту ссылку в свой вопрос.
Спасибо @PanagiotisKanavos, это сработало, я поместил этот cte в таблицу temp и использовал его в моем SP, и теперь оператор сортировки давно исчез.
Вы пробовали материализовать свой набор данных в таблице temp вместо использования cte? Общее табличное выражение будет запускать sql каждый раз, когда оно используется, тогда как временная таблица запускает его только один раз и повторно использует тот же набор результатов. Такой подход должен сократить количество сортировок (до 1).
Разливы часто связаны с плохой статистикой. Проверьте предполагаемый и фактический планы выполнения, чтобы убедиться в этом. Отказ от cte мог тоже помогает, так как мог перестраивает статистику. Трудно сказать наверняка, не потрудившись.
Да, это сработало, когда я использовал временную таблицу вместо CTE, и теперь оператор сортировки исчез. Также спасибо за заметку о разливах
Для запроса в вашем 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
Сортировка, как правило, - дорогостоящая операция, но я бы не стал доверять предполагаемой стоимости. Вы тестировали время выполнения с сортировкой / без сортировки? Вам действительно нужна сортировка в SQL или приложение может это сделать?