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

Вы можете попытаться улучшить свое решение, лучше используя индекс в столбце 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_rounded, который содержит исходную метку времени, округленную до 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

Я нашел эту статью в другом месте, и она оказалась весьма полезной. В результате я изменил 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 секундами для предыдущей версии.) Это все еще может быть намного медленнее, чем возможно; моей следующей задачей в идеале было бы понять, почему этот подход работает быстрее. Лаконичный ответ (и ссылка) от Шона Ланге, несомненно, дает некоторые подсказки. Конечно, мне придется отложить это и перейти к следующей задаче в нашей первоначальной реализации.

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