Господа, поделитесь опытом как лучше быть.

Такая процедура:
create procedure dbo.test
@p1 int= null
as
if @p1 is null
begin
select * from table
return
end

select * from table where field1 = @p1
go

Здесь всё понятно и индексы нормально работают (если параметр не нулевой), но что делать если параметров много. Использовать IFы для каждой комбинации крайне затруднительно, а "фокусы" типа
select * from table where field1 = isnull(@p1, field1) или
select * from table where (@p1 is null or field1 = @p1)
приводят к сканированию таблицы (по крайней мере так планы процедур показывают).

Заранее большое спасибо

Неужели никому не приходится иметь дело с процедурами в которых запрос использует параметры в качестве SARG?

White OwlДа приходится, то приходится....
Просто универсального ответа на этот вопрос лично мне не известно. Если мне нужно сделать что-то подобное, то я обычно просто иду по пути ' where (@p1 is null or field1 = @p1) ', но в основном я пытаюсь изменить логику работы чтобы вообще избавится от or в условиях.

---
http://www.rusug.ru] Портал русскоязычной группы пользователей Sybase

Спасибо за ответ.
Мне тоже нравится where (@p1 is null or field1 = @p1)
Почему-то кажется в этом случае когда параметр не нулевой индекс должен использоваться. Но план запроса упорно показывает скан таблицы.

Dmitry.можно пользовать execute(@query)
ну и @query заранее сформировать без OR

В этом случае упадёт производительность т.к. при каждом выполнении план будет строиться заново.

White Owl KruВ этом случае упадёт производительность т.к. при каждом выполнении план будет строиться заново.Время потраченное на построение плана это ничто по сравнению с выигрышем если оптимизатор воспользуется индексом.
Полностью согласен, но всё-таки не хочется строить запросы динамически. Трудно подобные процедуры потом поддерживать. Да и не везеде они в корпоративный стандарт вписываются.

Наверное придётся исходить из ситуации - смотреть где индекс действительно нужен, там параметры Ifом проверять, ну а по остальным можно и условие типа ((@param is null) or (field = @param)).

Всё-таки старнно, что при таком условии происходит скан. Ведь всего-то требуется ключи индекса сравнить с параметром, если он есть.

Кажется, что Сайбезовский оптимизатор всё ещй очень легко сбить с толку.
Такой простой пример:
select * from tst where ((100 is null) or id = 100)
приводит к сканированию таблицы.

Ради интереса попробовал тоже самое на DB2. Там оптимизатор выбирает индекс.