Как оптимизировать использование предложения "ИЛИ" при использовании с параметрами (SQL Server 2008)

Question

Интересно, есть ли какой-нибудь разумный способ переписать следующий запрос, чтобы оптимизаторы использовали индексы столбцов?

Create Procedure select_Proc1
    @Key1 int=0,
    @Key2 int=0
As
BEGIN
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
END
GO

Хотя столбцы в предложениях WHERE охватываются индексами, SQL Server не может использовать эти индексы. Это поднимает вопрос о том, блокирует ли что-либо использование индексов. Ответ на этот вопрос - да - виновниками являются параметры и условие "ИЛИ". Параметры не охватываются индексами, что означает, что SQL Server не может использовать ни один из индексов для оценки "@key1=0" (условие, которое также применяется к @key2=0). Фактически это означает, что SQL Server не может использовать индексы для оценки предложения "@key1=0 ИЛИ Key1= @ ключ1" (поскольку предложение "ИЛИ" представляет собой объединение строк, охватываемых обоими условиями). Тот же принцип применим и к другому пункту (re. Key2). Это приводит к тому, что SQL Server приходит к выводу, что никакие индексы не могут использоваться для извлечения строк, а SQL Server использует следующий лучший подход - сканирование кластерных индексов.

Как видите, оптимизатор SQL не будет использовать индексы для столбцов, если в предложении WHERE предикаты помечены "ИЛИ". Одним из решений этой проблемы является разделение запросов с помощью предложения IF для всех возможных комбинаций параметров.

Пожалуйста, прочтите эту короткую статью, чтобы получить более полное представление о проблеме: http://www.sql-server-performance.com/articles/per/optimize_or_clause_p1.aspx

Теперь мой вопрос: что нам делать, если возможные комбинации больше трех или четырех? Написание отдельного запроса для каждой комбинации не представляется рациональным решением. Есть ли другой способ обойти эту проблему?

Answer 1

SQL Server не очень хорошо в оптимизации OR предикаты.

Использовать этот:

SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 = 0
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 <> 0
        AND key2 = @key2
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key2 = 0
        AND @key1 <> 0
        AND key1 = @key1
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 <> 0
        AND @key2 <> 0
        AND key1 = @key1
        AND key2 = @key2

SQL Server будет искать значения переменных до выполнения запросов и оптимизировать избыточные запросы.

Это означает, что только один запрос из четырех будет фактически выполнен.

Answer 2

MSSQL 2008 имеет синтаксис оптимизации упрощения условий, вот он

 Where (@key1 =0 OR Key1 =@Key1) AND
      (@key2 =0 OR Key2 =@Key2) option(recompile)

Это оптимизирует использование констант

Answer 3

Да - осторожное использование динамического sql решит эту проблему. Есть два способа сделать это:

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

б. Если вы проявляете гибкость в отношении расположения этого SQL, вы можете (снова ВНИМАТЕЛЬНО) составить строку запроса в вашем приложении и передать ее на сервер.

Опасность, конечно же, связана с SQL-инъекцией. Поэтому вы должны быть очень осторожны, когда данные передаются от клиента в динамический SQL-оператор.

Полная информация от Эрланда Соммарскога: http://www.sommarskog.se/dynamic_sql.html и http://www.sommarskog.se/dyn-search.html

Answer 4

Вы пробовали табличную функцию?

CREATE FUNCTION select_func1 (  
    @Key1 int=0,
    @Key2 int=0
)
RETURNS TABLE 
AS RETURN (
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
)


select * from select_func1(1,2)