#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
У меня есть простой select, который выполняется очень медленно, и я сузил его до одного конкретного where оператора.
Я не уверен, нужно ли вам просматривать весь запрос целиком, или, возможно, вы сможете помочь мне понять, почему случай так сильно влияет на производительность. Я чувствую, что нашел проблему, но, похоже, не могу ее решить. Я работал с оператором case раньше и никогда не сталкивался с такими огромными проблемами с производительностью.
Для этого конкретного примера. объявление выглядит следующим образом: Declare @lastInvOnly as int = 0
далее следует заявление о проблеме where , которое выполняется около 20 секунд:
AND ird.inventorydate = CASE WHEN @lastinvonly=0 THEN
-- get the last reported inventory in respect to the specified parameter
(SELECT MAX(ird2.inventorydate)
FROM irdate ird2
WHERE ird2.ris =r.ris AND
ird2.generateddata!='g' AND
ird2.inventorydate <= @inventorydate)
END
Удаление case запускает его за 1 секунду, что является ОГРОМНОЙ разницей. Я не могу понять, почему.
AND ird.inventorydate =
(SELECT MAX(ird2.inventorydate)
FROM irdate ird2
WHERE ird2.ris = r.ris AND
ird2.generateddata! = 'g' AND
ird2.inventorydate <= @inventorydate)
Комментарии:
1. План выполнения необходим для устранения проблемы с производительностью. Не могли бы вы добавить это, пожалуйста?
2. Вероятно, у вас проблема с плохим планом, вызванным выражением CASE. Что произойдет, если @lastinvonly =1?
3. но объявлен @lastinvonly, и я жестко кодирую его как 0. почему это влияет на план выполнения? спасибо за ответы!
4. @MadamZuZu . , , Во-первых,
caseвwhereпредложении затрудняет оптимизатор и может снизить производительность. Во-вторых, вероятно, есть лучшие способы выразить эту логику с помощью оконных функций, но вы не предоставляете достаточного контекста для внесения предложения.5. спасибо, Гордон, теперь окно поиска в Google работает.
Ответ №1:
Почти наверняка это должна быть производная таблица, и вам следует присоединиться к ней вместо этого. Подвыборки, как правило, имеют низкую производительность, а при использовании условно — еще хуже. Попробуйте это вместо:
INNER JOIN (
select
ris
,max(inventorydate) AS [MaxInvDate]
from irdate
where s and generateddata!='g'
and inventorydate <= @inventorydate
GROUP BY ris
) AS MaxInvDate ON MaxInvDate.ris=r.ris
and ird.inventorydate=MaxInvDate.MaxInvDate
and @lastinvonly=0
Я не уверен на 100%, что это логически работает со всем запросом, поскольку ваш вопрос предоставляет только небольшую часть.
Комментарии:
1. я думаю, что это сработало… мне нужно будет проверить цифры и посмотреть, как применить этот подход к другим операторам case, которые я прокомментировал во время тестирования. я отмечу это как правильное завтра, когда у меня будет время пройти через все и, что более важно, понять, как и почему это работает 🙂 еще раз спасибо!
Ответ №2:
Я не могу сказать наверняка, не видя плана выполнения, но ветвь в вашем фильтре, вероятно, является причиной проблем с производительностью. Теоретически оптимизатор может использовать версию без регистра и применить оптимизацию, которая преобразует подзапрос в вашем фильтре в объединение; когда добавляется оператор case, эта оптимизация больше невозможна, и подзапрос выполняется для каждой строки. Можно реорганизовать код, чтобы помочь оптимизатору, что-то вроде этого должно работать:
outer apply (
select max(ird2.inventorydate) as maxinventorydate
from irdate ird2
where ird2.ris = r.ris
and ird2.generateddata <> 'g'
and ird2.inventorydate <= @inventorydate
and @lastinvonly = 0
) as ird2
where ird.inventorydate = ird2.maxinventorydate