- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
create procedure [dbo].[pbsp_GetClientUsers]
(
@ClientId int,
@fname varchar(100),
@lname varchar(100),
@email varchar(150)
)
AS
DECLARE @SQL varchar(1000)
Set @SQL = 'Select TOP 500 *, tblRoles.title AS Role from tblUser INNER JOIN
tbl_mtm_UserRoles ON tblUser.UserId = tbl_mtm_UserRoles.UserId INNER JOIN
tblRoles ON tbl_mtm_UserRoles.RoleId = tblRoles.Id where tblUser.ClientId = ' + STR(@ClientId) + ' '
if LEN(@fname) > 0
Set @SQL = @SQL + ' AND tblUser.fName like ''' + @fname + '%'' '
if LEN(@lname) > 0
Set @SQL = @SQL + ' AND tblUser.lName like ''' + @lname + '%'' '
if LEN(@email) > 0
Set @SQL = @SQL + ' AND tblUser.Email like ''' + @email + '%'' '
Set @SQL = @SQL + ' Order by tblUser.lName, tblUser.Fname'
exec (@SQL)
По долгу работы приходится местами переписывать унаследованный код. Я держалась неделю, но после этого шедевра все таки зарегилась на сайте и решила поделиться перлом. Интересно, что бы делали, если бы параметров еще штук пускай даже двадцать добавить?
Follow us!
FROM dbo.tblUser
WHERE
(@FirstName IS NULL OR fName LIKE @FirstName)
AND (@LastName IS NULL OR lName LIKE @LastName)
AND (...
Я полагаю что как то так. Хотя конечно если вы считаете что это нормальная практика, то пора Вам покупать билет в Индию или оторвать себе руки, чтобы работы меньше было.
С какими объемами данных работали? Как давно в мире БД?
Приведенный Вами пример чужого кода не очень хорош, но в плане производительности будет гораздо лучше того, что Вы написали самостоятельно.
IMHO было бы лучше дать начальные значения параметров процедуры = null, считать не Len(@param) а выполнять проверку if @param is not null при формировании строки динамического запроса и выполнять получившийся запрос не через Exec(@param) а через sp_executesql.
Кроме того при перечислении таблиц в динамическом запросе практически необходимо добавлять имя схемы, а также можно посоветовать использовать псевдонимы таблиц.
А про билеты в Индию и отрывание рук Вы зря. Если бы мне пришлось выбирать, кого отправлять в ссылку в Индию - Вас или неизвестного ваятеля, то я скорее всего отправил бы Вас.
Не судите и не судимы будете (с)
Самому недавно пришлось править подобный г-код (не тот, который динамический, а тот, что Крендель считает правильным). Только параметров было не 3, а 30-40 и запрос получался экрана на 3. Я бы на месте оптимизатора застрелился.
Только вместо like ''' + @lname + '%'' ', лучше использовать
set @lname = '%' + @lname
в запросе 'like @lname'
и exec sp_executesql ... с передачей @lname и прочих параметров.
Ну, чтоб SQL-injection не случилось...
ибо
1. план нужно оптимизатору каждый раз строить заново
2. Понять окончательный текст запроса сможете иногда
только в профайлере.
А 30-40 параметров - зло уже само по себе.
б) Oracle к примеру умеет преобразовывать статик sql в параметризированные запросы (читаем дядю Кайта)
2) никто не мешает иметь две процедуры: одна формирующая текст запроса, вторая выполняющая запрос.
P.S. Зарубливаем себе на носу: чем точнее запрос, тем он "понятнее" оптимизатору.
Но учтите всевозможные комбинации условий (сколько возможных комбинаций при 30 параметрах?)
2. Можно и больше. Но чем больше процедур, тем менее удобно.
Увы, оптимизатору бывает не очень понятны и простые запросы.
Иногда приходится объяснять хинтами )
Вообще проблема говнокода зачастую не столько в неоптимальности
(потеря 50 тактов на нечасто вызываемой функции),
сколько в невозможности такой код нормально поддерживать, править, находить в нем ошибки ...
1б SQL-сервер планы динамических запросов умеет кэшировать. Особенно, если эти запросы параметризованы.
1в Что лучше - построить новый план для простенького текста запроса или производить ту же выборку по заведомо неоптимальному плану для запроса длиной на несколько экранов?
По поводу 30-40 параметров - каким еще образом позволить юзеру гибко указать критерии отбора при возможном (по ТЗ) сочетании из этих 30-40 условий? Причем в зависимости от выбранных критериев в запросе могут участвовать от 1 до 10 таблиц.
1б. Что-то я не вижу параметризации в исходном коде.
1в. Производить выборку по заведомо оптимальному плану для запроса длиной в пять строчек.
Каков вопрос ....
Про 30-40 условий и критерии отбора.
Есть такое понятие, как архитектура данных.
Можно вообще свести все в одну таблицу с 200ми полями и по ней выбирать.
Очень инетерсно, что тогда план показывал
Потому как МС-овский оптимизатор обычно выкусывает заведомо ложные ветки без особых проблем
Единственно где встречал проблемы с производительностью для таких конструкций - это ДБ2, а с МС-ом нормально всё было.
PS Про объемы данных было сказано в самом начале. И сама процедура будет вызываться крайне редко.