Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Всем привет!

Как оптимально реализовать Select из таблиц/таблицы если данные для запроса передаются в виде параметров процедуры.
Часть из параметров может быть NULL (пользователь не ищет по ним данные), либо заданные значения.

Использовать динамический sql запрос или есть более красивое решение?

Я использую следующий подход, но он мне самому не нравится

WHERE (@col1 IS NULL  OR @col1=col1) 
AND (@col2 IS NULL OR  @col2 LIKE '%'+col2+'%')


и так далее по всем столбцам по которым возможен поиск.
13 ноя 12, 15:33    [13465853]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
https://www.sql.ru/articles/mssql/03060701arraysandlistsinsqlserver.shtml
13 ноя 12, 15:43    [13465951]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
Testor1
Всем привет!

Как оптимально реализовать Select из таблиц/таблицы если данные для запроса передаются в виде параметров процедуры.
Часть из параметров может быть NULL (пользователь не ищет по ним данные), либо заданные значения.

Использовать динамический sql запрос или есть более красивое решение?

Я использую следующий подход, но он мне самому не нравится

WHERE (@col1 IS NULL  OR @col1=col1) 
AND (@col2 IS NULL OR  @col2 LIKE '%'+col2+'%')


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

можно
where col1 = isnull(@col1, col1)
13 ноя 12, 16:48    [13466677]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Ivan Durak
Testor1
Всем привет!

Как оптимально реализовать Select из таблиц/таблицы если данные для запроса передаются в виде параметров процедуры.
Часть из параметров может быть NULL (пользователь не ищет по ним данные), либо заданные значения.

Использовать динамический sql запрос или есть более красивое решение?

Я использую следующий подход, но он мне самому не нравится

WHERE (@col1 IS NULL  OR @col1=col1) 
AND (@col2 IS NULL OR  @col2 LIKE '%'+col2+'%')


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

можно
where col1 = isnull(@col1, col1)
И куда же делись записи с col1 IS NULL при @col1 IS NULL ?

Вот такую штуку: @col2 LIKE '%'+col2+'%' "в лоб" ну никак не соптимизируешь!
13 ноя 12, 17:00    [13466766]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
iap
Вот такую штуку: @col2 LIKE '%'+col2+'%' "в лоб" ну никак не соптимизируешь!


Это не главное. Важно проверка на isnull. Можно ли это как-то избежать? Динамический скл, может сформировать нужный where, но не будет ли он работать медленнее чем текущая схема ?
13 ноя 12, 17:13    [13466871]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Crimean
Member

Откуда:
Сообщений: 13148
в зависимости от того, что важнее - конкурентность или компактность кода - пользуем или if else или option recompile
к сожалению, третьего варианта не существует для "этого" оптимизатора
p.s.
еси чо, recompile снижает конкурентность, а if else раздувает код или грузит tempdb, в зависимости от предпочтений
13 ноя 12, 17:16    [13466890]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Crimean
в зависимости от того, что важнее - конкурентность или компактность кода - пользуем или if else или option recompile
к сожалению, третьего варианта не существует для "этого" оптимизатора
p.s.
еси чо, recompile снижает конкурентность, а if else раздувает код или грузит tempdb, в зависимости от предпочтений


Если кол-во параметров небольшое (2 или 3), то можно комбинаторно составить реальные IF (@col1 IS NOT NULL) SELECT * FROM WHERE col1 = @col1 и т.д.
13 ноя 12, 17:30    [13466995]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Testor1,

конечно можно. или это был не вопрос, а развитие идеи? или намек был недостаточно прозрачным? ;)
13 ноя 12, 17:37    [13467053]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
nezhadnye_my
Guest
Testor1
Crimean
в зависимости от того, что важнее - конкурентность или компактность кода - пользуем или if else или option recompile
к сожалению, третьего варианта не существует для "этого" оптимизатора
p.s.
еси чо, recompile снижает конкурентность, а if else раздувает код или грузит tempdb, в зависимости от предпочтений


Если кол-во параметров небольшое (2 или 3), то можно комбинаторно составить реальные IF (@col1 IS NOT NULL) SELECT * FROM WHERE col1 = @col1 и т.д.


можно не весь запрос переписывать,
а по частям собрать динамику, анализируя полученные параметры.
типа того:

set @sql =
N'select * from Mytable
where 1 = 1'
+ case when @col1 is not null then 
       ' and col1 = @col1' else '' end 
+ case when @col2 is not null then 
       ' and col2 = @col2' else '' end
       ... 
       
exec sp_executesql @sql,
     <declaration> ,
     @col1, @col2, ... 
13 ноя 12, 18:26    [13467319]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Crimean
Testor1,

конечно можно. или это был не вопрос, а развитие идеи? или намек был недостаточно прозрачным? ;)


развитие идеи
13 ноя 12, 19:05    [13467547]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
nezhadnye_my
Testor1
пропущено...


Если кол-во параметров небольшое (2 или 3), то можно комбинаторно составить реальные IF (@col1 IS NOT NULL) SELECT * FROM WHERE col1 = @col1 и т.д.


можно не весь запрос переписывать,
а по частям собрать динамику, анализируя полученные параметры.
типа того:

set @sql =
N'select * from Mytable
where 1 = 1'
+ case when @col1 is not null then 
       ' and col1 = @col1' else '' end 
+ case when @col2 is not null then 
       ' and col2 = @col2' else '' end
       ... 
       
exec sp_executesql @sql,
     <declaration> ,
     @col1, @col2, ... 



Crimean

в зависимости от того, что важнее - конкурентность или компактность кода - пользуем или if else или option recompile
к сожалению, третьего варианта не существует для "этого" оптимизатора
p.s.
еси чо, recompile снижает конкурентность, а if else раздувает код или грузит tempdb, в зависимости от предпочтений

13 ноя 12, 19:08    [13467563]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Testor1,

динамика дает некоторые проблемы с правами. которые в 2008, правда, уже достаточно неплохо решаются
ну и вы попробуйте "выше среднего" запрос динамически собрать. будет "не очень удобно"
я уже молчу про получение датасата - 50/50 упремся в "insert exec cant be nested", а в функцию такое не запхнуть
и - обращаю внимание - пример вполне "расово верный" ибо при тупой склейке с параметрами "малыш дроппи" в гости может заскочить

чтоп "не раздувать" дальше - все примеры имеют место быть использованы в разных ситуациях. универсального решения в виде явной зависимости плана выполнения от набора параметров MS решил не делать
13 ноя 12, 19:14    [13467593]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Crimean
Testor1,

динамика дает некоторые проблемы с правами. которые в 2008, правда, уже достаточно неплохо решаются
ну и вы попробуйте "выше среднего" запрос динамически собрать. будет "не очень удобно"
я уже молчу про получение датасата - 50/50 упремся в "insert exec cant be nested", а в функцию такое не запхнуть
и - обращаю внимание - пример вполне "расово верный" ибо при тупой склейке с параметрами "малыш дроппи" в гости может заскочить

чтоп "не раздувать" дальше - все примеры имеют место быть использованы в разных ситуациях. универсального решения в виде явной зависимости плана выполнения от набора параметров MS решил не делать


получается, текущее решение - более менее оптимальное ..
13 ноя 12, 20:53    [13467970]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Testor1,

Кстати что легче для сервера (@a IS NULL OR @a = a) или (a = ISNULL(@a, b)) ?
13 ноя 12, 21:03    [13467989]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Testor1
Testor1,

Кстати что легче для сервера (@a IS NULL OR @a = a) или (a = ISNULL(@a, b)) ?
Не b, - a наверно?
Это разные выражения, дающие разный результат.
Сравнивать их некорректно. По крайней мере, если допустимо a IS NULL.
13 ноя 12, 21:18    [13468016]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
iap
Testor1
Testor1,

Кстати что легче для сервера (@a IS NULL OR @a = a) или (a = ISNULL(@a, b)) ?
Не b, - a наверно?
Это разные выражения, дающие разный результат.
Сравнивать их некорректно. По крайней мере, если допустимо a IS NULL.


b - не равное a значение.

С точки зрения производительности и построения плана запроса

когда таких условий несколько, то думаю, что одно из них будет работать быстрее. вопрос какое
13 ноя 12, 21:20    [13468028]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а я еще вброшу. если у вас параметры меняются так, что проца обрабатывает то 1-2 записи (условно) то почти всю таблицу - вам все равно придется ИЛИ уходить на варианты динамики / if else / recompile ИЛИ мириться с тем, что проца ВНЕЗАПНО начала тормозить. а после ВНЕЗАПНО опять заработала быстро. ибо план процы само по себе перестраивается не так часто
13 ноя 12, 21:28    [13468047]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
Testor1
Member

Откуда:
Сообщений: 679
Crimean
а я еще вброшу. если у вас параметры меняются так, что проца обрабатывает то 1-2 записи (условно) то почти всю таблицу - вам все равно придется ИЛИ уходить на варианты динамики / if else / recompile ИЛИ мириться с тем, что проца ВНЕЗАПНО начала тормозить. а после ВНЕЗАПНО опять заработала быстро. ибо план процы само по себе перестраивается не так часто


Чаще будет срабатывать условие @a IS NULL, поскольку пользователи редко заполняют множество полей для поиска. 1-3 поля максимум. Всего возможных полей для поиска будет до 10.
13 ноя 12, 21:39    [13468085]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация поиска по нескольким значениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Testor1
Crimean
а я еще вброшу. если у вас параметры меняются так, что проца обрабатывает то 1-2 записи (условно) то почти всю таблицу - вам все равно придется ИЛИ уходить на варианты динамики / if else / recompile ИЛИ мириться с тем, что проца ВНЕЗАПНО начала тормозить. а после ВНЕЗАПНО опять заработала быстро. ибо план процы само по себе перестраивается не так часто


Чаще будет срабатывать условие @a IS NULL, поскольку пользователи редко заполняют множество полей для поиска. 1-3 поля максимум. Всего возможных полей для поиска будет до 10.
Выгоднее всего собрать статистику вызовов, для 90% вызовов (2-3-5 вариантов) написать с if, для оставшихся сделать запрос с recompile
13 ноя 12, 23:29    [13468512]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить