Оптимизация запросов SQL IN
У меня есть этот запрос в SQL Server, но он занимает много времени из-за предложения IN. Как я могу это оптимизировать?
DECLARE @PriorityIn TABLE ([PriorityId] [BIGINT] NOT NULL);
DECLARE @GatewayId BIGINT = 4;
INSERT INTO @PriorityIn
SELECT PriorityId
FROM PriorityProviders
WHERE ProviderId = @GatewayId AND Type = 0
SELECT COUNT(*)
FROM MessageIn
WHERE ScheduleDate < GetDate()
AND CurrentStatus IN (0, 3, 6)
AND ((ProviderId IS NULL
AND PriorityId IN (SELECT PriorityId FROM @PriorityIn))
OR ProviderId = @GatewayId);
Когда я смотрю на этот запрос, он выполняется очень быстро:
SELECT COUNT(*)
FROM MessageIn
WHERE ScheduleDate < GetDate()
AND CurrentStatus IN (0, 3, 6)
AND ((ProviderId IS NULL AND PriorityId IN (1, 2))
OR ProviderId = @GatewayId);
Это план его выполнения:
Как я могу решить эту проблему?
Также попробуйте загрузить отдельные значения из PriorityProviders, возможно, вы загружаете слишком много повторяющихся значений.
Есть ли индекс в столбце PriorityId в таблице MessageIn?
Да, индекс уже есть в столбце PriorityId в таблице MessageIn. В таблице PriorityProvider всего 2 записи. Но в таблице MessageIn 8 миллионов записей.
Почему вы делаете это (используя табличную переменную для хранения PriorityID), когда единственное возможное значение для PriorityID - 4? Это может быть проблема XY.
@TabAlleman 4 предназначен для провайдера, и я выбираю приоритеты на его основе.
Я вижу, что "providerId" и "priorityId" выглядели одинаково, когда я быстро сканировал.
Ответы 2
Табличные переменные почти всегда работают хуже, чем временные таблицы, из-за плохих оценок количества элементов (отсутствие статистики), сериализованных модификаций и т. д. Есть много блогов об этом, вот один, а здесь Брент Озар обсуждает некоторые различия.
Итак, измените это на временную таблицу и посмотрите, поможет ли это для начала. Чем больше строк в @PriorityIn, тем больше вероятность, что это поможет.
Я бы также заменил @GatewayId на INT, если только ProviderId не был также BIGINT. В противном случае нет причин для неявного преобразования. Это должно относиться и к PriorityId.
Кроме того, вот хороший пост в блоге о запросах и производительности кухонной мойки может ускорить его
когда я использую временные таблицы, он дает мне ответ через 11 секунд, но в случае табличных переменных он дает мне ответ через 4 секунды.
Так что это 4 секунды, когда вы используете табличную переменную, что это такое, когда вы используете статические значения? Можете вставить план выполнения: brentozar.com/pastetheplan
Когда я использую статические значения, он возвращает результат через 0 секунд. Я добавил в свой вопрос.
вы можете наклеить план выполнения всех трех?
План выполнения добавляется к самому вопросу.
Изображения не очень помогают, поскольку мы не можем щелкнуть по ним и посмотреть, что такое prdicate, строки и т. д. Вы можете воспользоваться этой ссылкой? brentozar.com/pastetheplan
да, я уже сделал это, ниже URL: brentozar.com/pastetheplan/?id=Hk02K4L7m
@mayurRathod, у меня нет. Большая часть ваших затрат исходит из подсчета (*), но я не понимаю, почему это будет быстрее, если вы укажете буквальные значения.
это то, что я вижу за последние 2 дня. кстати, спасибо за ответ на вопрос
Извините, я не смог больше помочь @mayurRathod
Я только что изменил свой запрос на приведенный ниже, и теперь я получаю быстрый ответ, как будто раньше это занимало 4 секунды, а теперь это занимает 1 секунду, оба результата с 8 миллионами записей в таблице MessageIn. Но все же ищу лучший результат.
DECLARE @GatewayId INT = 4;
DECLARE @PriorityIn TABLE(
[PriorityId] [INT] NOT NULL
);
INSERT INTO @PriorityIn
SELECT PriorityId FROM PriorityProviders WHERE ProviderId = @GatewayId AND Type = 0
SELECT count(Id) From MessageIn m
LEFT JOIN @PriorityIn o on m.PriorityId = o.PriorityId
WHERE ScheduleDate < GetDate() AND CurrentStatus in (0,3,6) AND
((ProviderId is null AND o.PriorityId is not null)
OR ProviderId = @GatewayId)
Попробуйте сделать
PriorityIdпервичным ключом. Это заставляет индексировать табличную переменную и может изменить план выполнения.