Как я могу улучшить скорость этого SQL-запроса?

Question

У нас есть два устройства для сбора данных примерно с 30-секундными интервалами. Устройства расположены в двух широко расположенных местах. Абсолютное время каждой коллекции для каждого сайта может варьироваться +/- 30 секунд. Иногда сайт отключается по разным причинам. Данные от каждого устройства представляют различные виды измерений, например, температура от устройства 1 и влажность от устройства 2. Процесс записывает данные от device1 и device2 в отдельные таблицы в базе данных SQL Server 2012 Express, работающей на сервере, отдельном от каждого устройства.

Желательно представить данные с обоих устройств, сопоставленные в записи, которые будут содержать столбцы со значением для site1 для конкретной даты / времени в сочетании с данными для site2, если таковые имеются. Затем пользовательские программы будут запрашивать наборы записей для указанного диапазона даты / времени. Для этого я построил следующий SP:

ALTER PROCEDURE [db_datareader].[DataJoinDateRange] 
@DateFrom DateTime = '2014-05-15 15:10:24.000', 
@DateTo DateTime = '2014-06-15 15:10:24.000' 
AS
BEGIN
SET NOCOUNT ON;
WITH site1(id, date_time, dataval)
AS
(
    SELECT * 
    FROM site1_data 
    WHERE site1_data.date_time BETWEEN @DateFrom AND @DateTo
),
site2(id, date_time, datavaql)
AS
(
    SELECT *
    FROM site2_data
    WHERE site2_data.date_time BETWEEN @DateFrom AND @DateTo
)
SELECT * from site1 site1_res
INNER JOIN (select id, date_time, data_val) site2_res
on ABS(DATEDIFF("SECOND", site1_res.date_time, site_2_res.date_time)) < 30
END

Намерение состоит в том, чтобы сначала выбрать записи в желаемом диапазоне дат / времени, а затем соединить записи с сайта 1 с записями на сайте 2, которые находятся в пределах +/- 30 секунд. спектр. Результирующий набор записей будет содержать данные с обоих устройств или нули, если не существует соответствующей записи.

Кажется, это работает: записи с нужной формой выводятся и соответствуют правильным записям в каждой таблице. Но исполнение очень медленное. Запрос в диапазоне дат в несколько недель занимает около 1 минуты и 30 секунд. Сайт1 содержит около 5000 записей в этом диапазоне дат, а Сайт2 содержит только 1 запись. Запрос SELECT в диапазоне дат только для каждой таблицы выполняется менее чем за секунду.

Раньше я никогда не углублялся в SQL, но в нашей небольшой группе сейчас нет никого, кто мог бы выполнить эту задачу. Может кто-нибудь дать мне представление о правильном способе сделать это, или, по крайней мере, как ускорить этот SP?

Answer 1

Вы можете попытаться улучшить свое решение, лучше используя индекс на date_time колонка.

ABS(S1 - S2) < 30

эквивалентно

ABS(S2 - S1) < 30

<=>

-30 < S2 - S1 < 30

<=>

S2 - S1 < 30
AND
S2 - S1 > -30

<=>

S2 < S1 + 30
AND
S2 > S1 - 30

Вам действительно не нужен первый CTE, хотя он не должен причинять боль. Но WHERE пункт внутри CROSS APPLY лучше так написать. Кроме того, вы должны использовать OUTER APPLY вместо CROSS APPLY если вы хотите увидеть данные с сайта1, который не имеет соответствующих данных с сайта2. Теперь site2.date_time не находится внутри вызова функции, и оптимизатор может использовать индекс для этого столбца.

ALTER PROCEDURE [dbo].[SPJoinDateRange]
    @DateFrom DateTime = '2014-05-01 15:10:24.000', 
    @DateTo   DateTime = '2014-07-31 15:10:00.000'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        site1_data.id AS id1
        ,site1_data.date_time AS date_time1
        ,site1_data.data_val1
        ,CA_site2.id2
        ,CA_site2.date_time2
        ,CA_site2.data_val2
    FROM
        site1_data
        OUTER APPLY
        (
            SELECT
                site2_data.id as id2
                ,site2_data.date_time as date_time2
                ,site2_data.data_val2
            FROM
                site2_data
            WHERE
                site2.date_time BETWEEN @DateFrom AND @DateTo
                AND site2.date_time < DATEADD(second, +30, site1_data.date_time)
                AND site2.date_time > DATEADD(second, -30, site1_data.date_time)
        ) AS CA_site2
    WHERE
        site1_data.date_time BETWEEN @DateFrom AND @DateTo
    ;

END

Это будет работать еще быстрее, если вы сможете добавить дополнительный столбец, который будет содержать ваши метки времени, округленные до ближайших 30 секунд. Или округлите существующие значения на месте, если вам не нужны точные метки времени.

Если мы добавим столбец с именем date_time_ounded, который содержит исходную метку времени, округленную до 30 секунд, создадим индекс по нему, тогда запрос будет выглядеть так:

ALTER PROCEDURE [dbo].[SPJoinDateRange]
    @DateFrom DateTime = '2014-05-01 15:10:24.000', 
    @DateTo   DateTime = '2014-07-31 15:10:00.000'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        site1_data.id AS id1
        ,site1_data.date_time AS date_time1
        ,site1_data.data_val1
        ,site2_data.id AS id2
        ,site2_data.date_time AS date_time2
        ,site2_data.data_val2
    FROM
        site1_data
        LEFT JOIN site2_data ON site2_data.date_time_rounded = site1_data.date_time_rounded
    WHERE
        site1_data.date_time BETWEEN @DateFrom AND @DateTo
    ;

END

Округлить date_time до ближайших 30 секунд вы можете использовать что-то вроде этого:

DATEADD(second, 30 * ROUND(DATEDIFF(second, '20010101', date_time)/30.0, 0), '20010101')

Он рассчитывает количество секунд от 2001-01-01 к данному date_time, делит их на 30, округляет результат до целого числа, умножает результат на 30, добавляет это число секунды к 2001-01-01,

Запустите это несколько раз, чтобы увидеть, как это работает:

SELECT 
GETDATE() as original, 
DATEADD(second, 30 * ROUND(DATEDIFF(second, '20010101', GETDATE())/30.0, 0), '20010101') AS rounded

Answer 2

Я нашел эту статью в другом месте, и это было довольно полезно. В результате я изменил SP следующим образом:

ALTER PROCEDURE [dbo].[SPJoinDateRange]
@DateFrom DateTime = '2014-05-01 15:10:24.000', 
@DateTo   DateTime = '2014-07-31 15:10:00.000'
AS
BEGIN
SET NOCOUNT ON;
WITH site1(id, date_time, data_val1)
AS
(
    SELECT * 
    FROM site1_data 
    WHERE site1_data.date_time BETWEEN @DateFrom AND @DateTo
)
SELECT * FROM site1
CROSS APPLY 
(
    SELECT id as id1, date_time as date_time1, data_val2
    FROM site2_data AS site2
    WHERE site2.date_time BETWEEN @DateFrom AND @DateTo 
    AND 
    ABS(DATEDIFF("SECOND", site1.date_time, site2.date_time)) < 30
) 
AS result
END

Время результата для этого запроса составляет 6 секунд. (по сравнению с 90 сек. для предыдущей версии.) Это может быть намного медленнее, чем это возможно; моей следующей задачей было бы в идеале понять, почему этот подход быстрее. Лаконичный ответ (и ссылка) Шона Ланге, несомненно, дает некоторые подсказки. Конечно, мне придется отложить это и перейти к следующей задаче в нашей первоначальной реализации.

Спасибо всем, кто так быстро ответил на мой вопрос.