Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

DrAndrey © (2005-09-06 08:46:00)
Входных параметров у ХП 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 раза больше

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

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

Nikolay M. (2005-09-06 14:45:00) [1]

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

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

DrAndrey (2005-09-06 13:55:00) [2]
Переделал процедуру в соответствии с рекомендациями
приведенный в [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

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

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

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

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

Ega23 (2005-09-06 11:30:00) [4]
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)

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

MOA (2005-09-06 11:22:00) [5]
Конечно, это же не более чем 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:11:00) [6]
2 MOA © (06.09.05 10:05) [3]

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

DrAndrey (2005-09-06 10:26:00) [7]
Ega23 b МОА Спасибо!

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

MOA (2005-09-06 10:05:00) [8]
Или даже так:
(PersID = CASE WHEN ((@PersID IS NULL) THEN PersID ELSE @PersID END)
или так
(PersID = CASE @PersID WHEN -1 THEN PersID ELSE @PersID END)
;)
Это чтобы от тяжёлых OR избавиться ;)

Ega23 (2005-09-06 09:48:00) [9]
Есть ли какие-то более рациональные методы выполнения ХП с использованием непостоянного числа параметров, или только динамическое формирование

Посмотри на такой текст:
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 09:24:00) [10]
Имейте в виду - при динамическом SQL оператор будет выполняться не от имени владельца процелуры, а от имени запустившего её пользователя, что иногда мешает.

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »