ХП с параметрами, возвр. набор данных , при поиске в базе

← →
DrAndrey (2005-09-06 08:46) [0]

Входных параметров у ХП 16 штук, в соответствии с числом условий для поиска (целые числа, строки, даты)

Поиск в 2 связанных таблицах на выходе - 7 полей из 5 связанных таблиц (основная и текстовые поля из справочников) + 2 поля UDF (вход tinyint выход varchar(10) )

Использовал динамическое формирование строки запроса @SQL varchar(1000)
затем его выполнение EXECUTE (@SQL)

Но есть ли это правильно, ведь при каждом вызове процедуры она будет перекомпилироваться, хотя SELECT, FROM, JOIN - неизменно, меняется только WHERE да и конструкции типа

SET @SQL= @SQL + CHAR(13) + "AND (DataRogden BETWEEN """ + CONVERT(varchar(10), @DataRogdenS,101)+ """ AND """+ CONVERT(varchar(10), @DataRogdenPo,101) +""")

не очень мне нравятся.

Вроде бы работает хорошо и быстро, но пока:
- сервер локальный
- ПК мощный (2,8; 1Гб)
- строк в таблицах мало (180 000 и 400 000) будет в 3 раза больше

В реальных условиях эксплуатации, наверное, будут тормоза.

Есть ли какие-то более рациональные методы выполнения ХП с использованием непостоянного числа параметров, или только динамическое формирование

← →
MOA © (2005-09-06 09:24) [1]

Имейте в виду - при динамическом SQL оператор будет выполняться не от имени владельца процелуры, а от имени запустившего её пользователя, что иногда мешает.

← →
Ega23 © (2005-09-06 09:48) [2]

Есть ли какие-то более рациональные методы выполнения ХП с использованием непостоянного числа параметров, или только динамическое формирование

Посмотри на такой текст:
Create Procedure S_MyProc @ObjID int =-1, @CodID tinyint =255, @PersID int =-1, @PersName varchar(50)="", @currDate datetime=0, ...... As Select ..... from ..... where (@PersID=-1 or PersID=@PersID) and (@ObjID=-1 or ObjID=@ObjID) and (@CodID=255 or CodID=@CodID) and (@PersName="" or PersName=@PersName) and (@CurrDate=0 or CurrDate=@CurrDate)

← →
MOA © (2005-09-06 10:05) [3]

Или даже так:
(PersID = CASE WHEN ((@PersID IS NULL) THEN PersID ELSE @PersID END)
или так
(PersID = CASE @PersID WHEN -1 THEN PersID ELSE @PersID END)
;)
Это чтобы от тяжёлых OR избавиться ;)

← →
DrAndrey (2005-09-06 10:26) [4]

Ega23 b МОА Спасибо!

т.е. если значение параметра задано - условие отбора равно значению параметра, а если не задано - условие отбора равно значению поля в таблице? Иэто работает
Это чтобы от тяжёлых OR избавиться ;)

← →
Ega23 © (2005-09-06 11:11) [5]

2 MOA © (06.09.05 10:05) [3]

Разве CASE в WHERE работает?

← →
MOA © (2005-09-06 11:22) [6]

Конечно, это же не более чем R-выражение. Обязан, согласно доке :). Вот пример из моей реально использующейся ф-ции (выдрал копипастом):
.... WHERE ((l.StartDate <= @EndDate) AND ((l.EndDate >= @StartDate) OR (l.EndDate IS NULL))) AND ((tls.StartDate <= @EndDate) OR (tls.StartDate IS NULL)) AND ((tls.EndDate >= @StartDate) OR (tls.EndDate IS NULL)) AND (tlc.Categor = CASE @Cat WHEN 0 THEN tlc.Categor ELSE @Cat END)
Ну правда тут OR этот - но ничего толкового не придумал, чтобы от него избавиться :(.

← →
Ega23 © (2005-09-06 11:30) [7]

2 MOA © (06.09.05 11:22) [6]
AND ((tls.StartDate <= @EndDate) OR (tls.StartDate IS NULL))
Проверку на дефолтное значение лучше ставь первой.

AND (tlc.Categor = CASE @Cat WHEN 0 THEN tlc.Categor ELSE @Cat END)

Гм... Даже и не подозревал. Хотя, с другой стороны, особо и не требовалось... :о)

Кстати, слегка поспешил. Для NULL, возможно, (а возможно, и нет, т.е. одинаково) лучше использовать не
CASE @PersID WHEN -1 THEN PersID ELSE @PersID END
а
PersID = ISNULL(@PersID,PersID)
Может и не быстрее - но лаконичнее ;)
Касательно CASE - его даже и в ORDER BY использовать можно ;).

Переделал процедуру в соответствии с рекомендациями
приведенный в [1] кусок кода заменил на

AND (DataRogden BETWEEN CASE WHEN @DataRogdenS IS NULL THEN DataRogden ELSE @DataRogdenS END AND CASE WHEN @DataRogdenPo IS NULL THEN DataRogden ELSE @DataRogdenPo END)

остальные по аналогии

... работает, но значительно медленнее, наверное, потому, что на практике вряд ли будет нужен поиск по всем 16 параметрам сразу, чаще по комбинациям 4-5 и при динамическом формировании в запросе получается намного меньше AND и OR

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

Может есть еще какие-нибудь варианты.

Подскажите мастера.

> ... работает, но значительно медленнее,

Медленнее чем что?
Или чем "где"?

ХП с параметрами, возвр. набор данных , при поиске в базе Найти похожие ветки