Как оптимизировать запрос?
У меня есть запрос T-SQL, который выполняется в течение 6 минут.
В одной таблице есть несколько подзапросов. Я думаю, что это причина проблемы. Но у меня нет идей по оптимизации.
SELECT dateheure, bac, presence, reponse
,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as dateheure_precedente
,(select top 1 presence from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as presence_precedente
,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as reponse_precedente
,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as dateheure_suivante
,(select top 1 presence from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as presence_suivante
,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as reponse_suivante
FROM [alpla_log].[dbo].[LogEvents] t1
WHERE
t1.presence = 7845
AND dateheure BETWEEN '11/07/2024 00:00:00' AND '11/07/2024 23:59:59.997'
ORDER BY id DESC
Единственная используемая таблица — «LogEvents».
CREATE TABLE LogEvents (
id int IDENTITY NOT NULL PRIMARY KEY,
dateheure datetime NULL,
type varchar(50) NULL,
msg varchar(MAX) NULL,
presence int NULL,
destination int NULL,
status_prt int NULL,
reponse int NULL,
bac int NULL
);
План выполнения вы можете найти здесь.
Я попытался добавить индекс в таблицу, но это не имеет значения.
CREATE NONCLUSTERED INDEX ON dbo.Logevents (bac, dateheure) INCLUDE (reponse)
Других индексов нет.
Таблица представляет собой журнал событий на автоматическом складе. Это сохраняет положение ящиков в разных местах. Я хочу найти предыдущую и следующую позицию каждого ящика по сравнению с конкретным местом.
РЕДАКТИРОВАТЬ :
Благодаря использованию функций LEAD и LAG время выполнения запроса теперь составляет около 500 мс.
Почему бы вам не «выбрать топ-1, дату, присутствие, ответ и т. д. из LogEvents ГДЕ» один раз? Каждый из этих избранных делает одно и то же. А вычисление даты по частям в значительной степени гарантирует сканирование индекса. Вы хотите полностью избежать подобных вещей. Однако в корне вам необходимо получить план выполнения и выяснить, как SQL-сервер обрабатывает запрос за вас. Это приведет вас к пониманию того, что должно измениться.
Комбинация: LEADLAG оконных функций, исправления WHERE для использования диапазона и удаления ORDER BY и индекса (bac, dateheure) INCLUDE (presence, reponse), вероятно, исправит это за вас dbfiddle.uk/LJcTmZ7k но опять же трудно сказать без дополнительной информации.
Прочтите о OUTER APPLY, нет необходимости повторять этот коррелированный подзапрос TOP 1 несколько раз.
ТАКЖЕ AND DATEFROMPARTS(DATEPART(year,dateheure),DATEPART(month,dateheure),DATEPART(day,dateheure)) = '07.11.2024' , почему бы просто не сделать это: year,dateheure between '20240711' and '20240711 23:59:59.997'
>= ... < логика была бы лучше, @siggemannen : dateheure >= '20240711' AND dateheure < '20240712' Это полностью инклюзивно.
@ThomA да, хороший звонок, я так привык к тому, что часть даты представляет собой неизменяемую строку, но твой вариант определенно лучше
Вы можете увидеть разницу с моими предложениями dbfiddle.uk/Ex0uXTTI одно сканирование индекса, без поиска ключей и сортировки. Индекс, который вы пробовали, не содержал presence в INCLUDE.
Ответы 2
Ваш запрос может быть значительно улучшен.
- Хотя вы можете заменить шесть подзапросов двумя
APPLY, гораздо лучшим решением будет использование оконных функцийLEADиLAG.- Обратите внимание, что
LEADиLAGне дадут результата для последней/первой строки раздела. Возможно, вам придется поместить все это в производную таблицу с более широким диапазоном дат, а затем отфильтровать ее обратно.
- Обратите внимание, что
- Предложение
WHEREне является «поддерживаемым» (не может использовать индексы). Вместо этого используйте диапазон дат, желательно полуоткрытый интервал>= AND < - Удалите
ORDER BY, если в этом нет абсолютной необходимости, так как это другой порядок, чемWHEREиPARTITION BYвLEAD.
SELECT
dateheure,
bac,
presence,
reponse,
LAG(t1.dateheure) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as dateheure_precedente,
LAG(t1.presence ) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as presence_precedente,
LAG(t1.reponse ) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as reponse_precedente,
LEAD(t1.dateheure) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as dateheure_suivante,
LEAD(t1.presence ) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as presence_suivante,
LEAD(t1.reponse ) OVER (PARTITION BY t1.bac ORDER BY t1.dateheure, t1.id) as reponse_suivante
FROM LogEvents t1
WHERE
t1.presence = 7845
AND dateheure >= '20240711'
AND dateheure < '20240712';
Наконец, добавьте правильный индекс для поддержки этого запроса. Индекс, который у вас был, не учитывал presence.
CREATE INDEX IX ON LogEvents (presence, bac, dateheure, id) INCLUDE (reponse);
Из этой скрипты вы можете видеть, что теперь это приводит к одному сканированию базовой таблицы, без соединений, без поиска ключей и сортировок.
Хороший ответ. Я собирался опубликовать решение на основе OUTER APPLY, но оно должно было иметь гораздо лучшую производительность. Единственным недостатком является то, что первая/последняя строка для каждого bac может не отображать предыдущие/следующие значения, если они выходят за пределы отфильтрованного диапазона дат. Кроме того, если dateheure может иметь повторяющиеся значения, может потребоваться использование ORDER BY t1.dateheure, T1.id.
Справедливые замечания, спасибо
Одна из самых основных ошибок заключается в том, что вы запрашиваете одну и ту же большую таблицу несколько раз. Вместо этого вам следует поместить основной результат в таблицу #temp, а затем выполнить запрос к таблице Temp. Это даст вам прирост производительности. После этого вы можете применить функцию Window или Outer Apply во втором шаге запроса.
Есть 2 шага, но есть прирост производительности
SELECT id,dateheure, bac, presence, reponse
--,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as dateheure_precedente
--,(select top 1 presence from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as presence_precedente
--,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as reponse_precedente
-- ,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as dateheure_suivante
-- ,(select top 1 presence from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as presence_suivante
-- ,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as reponse_suivante
into #temp
FROM [alpla_log].[dbo].[LogEvents] t1
WHERE
t1.presence = 7845
AND dateheure BETWEEN '11/07/2024 00:00:00' AND '11/07/2024 23:59:59.997'
--ORDER BY id DESC
затем шаг 2нс,
Select *
--write other query here
from #temp
order by id desc
Для вопросов о производительности запросов нам нужны как минимум: задействованные таблицы и индексы. Пожалуйста, поделитесь планом запроса через brentozar.com/pastetheplan. Иначе это не несет никакой ответственности.