Объединение SQL 2000 ПОЛНОСТЬЮ разрушает оптимизацию запросов

#sql-server #query-optimization #union-all

#sql-server #оптимизация запросов #объединение всех

Вопрос:

У меня есть хранимая процедура, для нормального запуска которой требуется менее секунды. Пользователи хотели использовать данные из другой таблицы в этом запросе, поэтому я объединил эти данные с ОБЪЕДИНЕНИЕМ ВСЕХ и кучей фиктивных столбцов, которые отсутствовали в новой таблице.

При тестировании он отлично работал, но когда мы развернули его на сервере SQL 2000, у него начались тайм-ауты. Старый запрос выполняется менее чем за секунду, а два новых запроса выполняются менее чем за секунду, но когда они объединяются с использованием объединения ВСЕХ, время ожидания запроса истекает.

Вот общая идея того, как выглядит запрос. Реальный запрос содержит около 20 входных параметров и возвращает около 30 или 40 столбцов, но это должно дать основную идею:

 CREATE PROCEDURE dbo.SearchHistory
(
    @Criteria1 bigint,
    @Criteria2 int,
    @Criteria3 varchar(10)
)
AS
BEGIN
    -- Part 1
    SELECT
        A,
        NULL AS B,
        0 AS C,
        D
    FROM TableA
    WHERE @Criteria1 IS NULL
    AND @Criteria3 IS NULL
    AND (A = @Criteria2 OR @Criteria2 IS NULL)

UNION ALL

    -- Part 2
    SELECT
        A,
        NULL AS B,
        0 AS C,
        E
    FROM TableA
    WHERE @Criteria1 IS NULL
    AND @Criteria3 IS NULL
    AND (A = @Criteria2 OR @Criteria2 IS NULL)

UNION ALL

    -- Part 3
    SELECT
        A,
        B,
        C,
        D
    FROM TableB
    WHERE (F = @Criteria1 OR @Criteria1 IS NULL)
    AND (A = @Criteria2 OR @Criteria2 IS NULL)
    AND (G = @Criteria3 OR @Criteria3 IS NULL)
END

В приведенном выше примере @Criteria1 не равен null, поэтому части 1 и 2 вернут 0 строк, а часть 3 возвращает только 3 строки. Но если я закомментирую части 1 и 2, это немедленно завершится; если я оставлю их, я получу тайм-аут.

Как убедить SQL Server не вмешиваться в свой план выполнения в подобной ситуации?

1. Какой план выполнения это дает вам с UNION ALL ? Кроме того, что, если вы просто вставляете в @table_variable 3 отдельных оператора и выбираете из этого в конце?

2. (Я думаю, что в списке выбора отсутствует синтаксическая ошибка из-за отсутствия обязательной запятой; это фактический код?)

3. Помогает ли изменение процедуры CREATE PROCEDURE dbo.SearchHistory WITH RECOMPILE (... ?

4. Кроме того, попробуйте погуглить что-нибудь под названием «прослушивание параметров» — у меня такое чувство, что план выполнения облажался…

Ответ №1:

Я думаю, что ваша проблема связана с перехватом параметров SQL Server.

http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

Я сталкивался с этим несколько раз. Есть несколько способов обойти это. Например. использовать С ПЕРЕКОМПИЛЯЦИЕЙ, как предложил @Biff MaGriff. Самый простой способ, который я нашел для исправления, — преобразовать все ваши входные параметры в локальные параметры и использовать локальные.

1. При ПЕРЕКОМПИЛЯЦИИ все запросы были отключены, а не только там, где @Criteria1 не равен нулю. Я еще не успел попробовать локальные переменные, слишком много других вещей происходит, но я все еще намерен попробовать это.

Ответ №2:

На этот вопрос невозможно ответить, не увидев планы выполнения (пожалуйста, разместите их в виде изображений, по одному для каждой части и по одному для запроса объединения). Обходным путем является использование временной таблицы, в которую вы вставляете части по отдельности. Однако лучшим решением было бы исправить запрос в первую очередь.