Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

Откуда:
Сообщений: 34
Всем доброго дня.
Не могу разобраться, почему резко увеличивается время выполнения на запросах типа, когда параметры все не заданы. Типовой запрос выглядит так:

select *
from table
where
((@param1 is null) or (column1 = @param1))
and ((@param2 is null) or (column1 > @param2))
and ((@param3 is null) or (column3 < @param3))
..... и так до 15 разных условий

В 90% случаев из 10-15 параметров отбора работают только 1-2 (всё время разных, поэтому все возможные варианты описать невозможно в разных статических запросах), и если запрос делать динамическим и с участием sp_executesql @Query выполнять, то скорость выполнения часто различается - в 10 раз. К примеру, если задан только один параметр, и я выполняю
select *
from table
where column1 = @param1 , то он выполняется в 10 раз быстрее, чем верхний вопрос, хотя все param2, param3... is null

В моей голове не укладывается почем так. Мне хочется кричать "MS SQL тупит!", но скорее всего недопонимаю чего то я.

Конкретный запрос и план выполнения (который медленнее) - во вложении.

К сообщению приложен файл. Размер - 76Kb
24 июн 13, 10:01    [14472103]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

Откуда:
Сообщений: 34
А вот который работает быстрее

К сообщению приложен файл. Размер - 84Kb
24 июн 13, 10:01    [14472107]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
комментирование условия замедляет запрос? так не комментируйте
24 июн 13, 10:19    [14472199]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
FOKS,

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

п.с.
планы лучше прикладывать отдельным файлом, в формате sqlplan/xml.
24 июн 13, 10:33    [14472315]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

Откуда:
Сообщений: 34
Нашёл тут https://www.sql.ru/blogs/t-sql/1145 ответы свой вопрос - SQL строит не оптимальный план выполнения запроса.
Инструкция OPTION(RECOMPILE) после запроса решает проблему. Есть ещё варианты, описаны в статье.
Раньше такой опции не знал, надеюсь хуже не будет.
24 июн 13, 10:39    [14472361]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

Откуда:
Сообщений: 34
Паганель, комментирование тут для примера. После комментирования одного из условий, которое не должно влиять на время выполнения запроса - оно напротив - ускоряет исполнение запроса.
24 июн 13, 10:40    [14472373]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

Откуда:
Сообщений: 34
SomewhereSomehow,
Сообщением выше я написал решение проблемы. Конкретно на приведённом запроса - 100 мс. против 140 мс., но на других запросов разница есть и 100 мс. против 500 мс. и больше. Как я указал - помогает опция OPTION(RECOMPILE) к запросу, но повсеместно ей тоже лучше не пользоваться, но наверное в моём случае, это оптимальный вариант.
24 июн 13, 10:42    [14472390]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
FOKS
Всем доброго дня.

select *
from table
where
((@param1 is null) or (column1 = @param1))
and ((@param2 is null) or (column1 > @param2))
and ((@param3 is null) or (column3 < @param3))
..... и так до 15 разных условий

тут имхо как раз лучше динамику использовать.
С целью избавиться от or.
24 июн 13, 10:47    [14472420]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
FOKS,

Я по этому и спросил про время, глядя на те картинки планов, что вы привели, трудно поверить, что между двумя планами есть драматическая разница во времени выполнения.
40 мс это слишком похоже на погрешность.
Если судить по картинке, планы почти одинаковые, за исключением оценок и дополнительного сравнения по предикату.
Для первого запроса выделяется чуть-чуть больше памяти, возможно это дает какую-то незначительную разницу. Но, имхо, на таких объемах эта разница носит скорее случайный характер.

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

Если говорить в общем, не про конкретные запросы и планы что вы привели, то с необязательными параметрами действительно может быть связан выбор неудачного плана выполнения, сами разработчики сиквел сервера именуют эту проблему Optional Parameter Problem.
Если владеете английским можно еше посмотреть вот эти статьи на эту тему 1, 2.

option(recompile) - это годный вариант, если запрос вызывается не слишком часто, настолько часто, что компиляция запроса каждый раз во время выполнения (именно это указывает данная подсказка) сама по себе может стать проблемой.
Эта опция, однако, может спасти не во всех версиях сервера, до определенного Cummulative Update 2008 и 2008R2 серверов - запрос перекомпилировался, но поведение с параметрами было другим.
Оно изменилось когда ввели фишку parameter embedding optimization, которая, по-сути, заменяет во время выполнения параметры на константы времени выполнения а ненужные ветки отбрасывает, это позволяет строить такой план, как если бы параметров не было.

Но у вас 2012 сервер (судя по картинкам планов) - так что у вас должно работать. Однако, если в запросе идет присвоение переменной - этот прием тоже не сработает (не тестировал на последних сервиспаках, правда).
24 июн 13, 11:06    [14472592]     Ответить | Цитировать Сообщить модератору
 Re: Тормоза с типом выборки where (@param is null) or (param=@param)  [new]
FOKS
Member

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

Спасибо! Теперь хоть знаю куда копать, а то терялся в недоумении.
24 июн 13, 14:29    [14474216]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить