Как оптимизировать использование “ИЛИ” пункт при Использовании с параметрами (SQL Server 2008)
Интересно, существует ли какой-либо мудрый способ переписать следующий запрос так, чтобы индексы на столбцах привыкли оптимизатором?
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
В соответствии с этой статьей How to Optimize the Use "ИЛИ" Пункт При Использовании с Параметрами Preethiviraj Kulasingham:
Даже при том, что столбцы в
WHEREпункты покрыты индексами, SQL Server не может использовать эти индексы. Это поднимает вопрос относительно того, "блокирует" ли что-нибудь использование индексов? Ответ на этот вопрос - да - преступники являются параметрами иORсостояние.Параметры не покрыты индексами, что означает, что SQL Server не может использовать ни один из индексов для оценки
@key1=0(условие, которое также относится@key2=0).Эффективно, это означает, что SQL Server не может использовать индексы для оценки пункта
@key1=0 OR Key1= @key1(какORпункт является объединением строк, покрытых обоими условиями). Тот же принцип относится к другому пункту (ре. key2) также. Это приводит SQL Server приходить к заключению, что никакие индексы не могут использоваться для извлечения строк, оставляя SQL Server для использования следующего лучшего подхода - сканирование кластерного индекса
Как Вы видите, оптимизатор SQL не будет использовать индексы на столбцах, если предикаты будут ORредактор в WHERE пункт. Одно решение для этой проблемы, должен разделить запросы с IF пункт для всей возможной комбинации параметров.
Теперь мой вопрос - Что мы должны сделать, если возможные комбинации больше что всего три или четыре? Запись отдельного запроса для каждой комбинации не кажется рациональным решением.
Есть ли какое-либо другое обходное решение для этой проблемы?
4 ответа
SQL Server не очень хорош в оптимизации предикатов или .
Используйте это:
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 будет смотреть на значения переменных перед выполнением запросов и оптимизируйте избыточные запросы.
Это означает, что фактически выполняется только один запрос из четырех четырех.
Пробовали ли вы управление Daypilot ? Есть бесплатная версия, можно попробовать.
-121--4551130-RPC (удаленный вызов процедуры) является формой IPC (межпроцессная связь) - последний является более общим термином, охватывающим множество механизмов, помимо RPC.
-121--1225128-MSSQL 2008 имеет синтаксис оптимизации упрощения условий, здесь это
Where (@key1 =0 OR Key1 =@Key1) AND
(@key2 =0 OR Key2 =@Key2) option(recompile)
Это оптимизирует использование констант
Вы пытаетесь ли вы тратить таблицу функцию?
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)
Да - Осторожно Использование динамического SQL решит эту проблему. Есть два способа сделать это:
Если вы являетесь «пуристом» о сохраненных процессах, затем составьте настраиваемую строку запроса внутри сохраненного ProC и выполнить строку. Затем конкретный запрос может быть динамически записан на исполнение, чтобы включить только соответствующие критерии.
Если вы гибкие о местонахождении этого SQL, вы можете (снова осторожно) составить строку запроса в своем приложении и передайте ее на сервер.
Опасность, конечно, связана с инъекцией SQL. Таким образом, вы должны быть очень осторожны, как данные передаются от клиента в динамическое утверждение SQL.
Действительно тщательные и всеобъемлющие статьи из Erland Sommarskog :