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

Откуда:
Сообщений: 8768
День добрый ! Вот сделали такой запрос, я хоть убей не пойму в чем там оптимизация.
Пример:
 select top (@MAX_RESULTS)
        TL_ID, TL_DATE, LT_ID, LS_ID, ST_SHORT_NAME2, GD_NAME, SM_NAME, TL_MORE_INFO_SM, TL_PRICE, TL_AMOUNT
      from 
      (
        select
          TL_ID, TL_DATE, LT_ID, LS_ID, ST_SHORT_NAME2, GD_NAME, SM_NAME, TL_MORE_INFO_SM, TL_PRICE, TL_AMOUNT, USR_ID, GD_ID, ST_ID2, SM_ID
        from
          JV_TRANSACTION_LOG_ERROR A (nolock) 
        where
        (
          ((@TL_ID        IS NOT NULL AND A.TL_ID    = @TL_ID)        OR (@TL_ID IS NULL)) AND
          ((@TL_DATE_FROM IS NOT NULL AND A.TL_DATE >= @TL_DATE_FROM) OR (@TL_DATE_FROM IS NULL)) AND
          ((@TL_DATE_TO   IS NOT NULL AND A.TL_DATE <= @TL_DATE_TO)   OR (@TL_DATE_TO IS NULL))
        )
      )A
      where
      (
        ((@LT_ID            IS NOT NULL AND A.LT_ID =           @LT_ID)            OR (@LT_ID IS NULL)) AND
        ((@LS_ID            IS NOT NULL AND A.LS_ID =           @LS_ID)            OR (@LS_ID IS NULL)) AND
        ((@TL_MORE_INFO_SM  IS NOT NULL AND A.TL_MORE_INFO_SM = @TL_MORE_INFO_SM)  OR (@TL_MORE_INFO_SM IS NULL)) AND
        ((@USR_IDS          IS NOT NULL AND A.USR_ID in (select Data from dbo.Split(@USR_IDS,','))) OR (@USR_IDS IS NULL)) AND
        ((@GD_IDS           IS NOT NULL AND A.GD_ID in (select Data from dbo.Split(@GD_IDS,','))) OR (@GD_IDS IS NULL)) AND
        ((@ST_IDS2          IS NOT NULL AND A.ST_ID2 in (select Data from dbo.Split(@ST_IDS2,','))) OR (@ST_IDS2 IS NULL)) AND
        ((@USR_ID           IS NOT NULL AND A.USR_ID =          @USR_ID)           OR (@USR_ID IS NULL)) AND 
        ((@SM_ID            IS NOT NULL AND A.SM_ID =           @SM_ID)            OR (@SM_ID IS NULL)) AND 
        ((@TL_PRICE         IS NOT NULL AND A.TL_PRICE =        @TL_PRICE)         OR (@TL_PRICE IS NULL))
      )
      order by TL_DATE desc, TL_ID desc

1. Почему нельзя все в одно WHERE?
2. Наверное не совсем в тему - чем явно задание параметров лучше, чем через exec sp_executesql (....)
26 мар 12, 13:31    [12313946]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
ChA
Member

Откуда: Москва
Сообщений: 11316
netivan
Вот сделали такой запрос, я хоть убей не пойму в чем там оптимизация.
Ну как же ? А nolock ?

P.S. Шутка.
26 мар 12, 13:38    [12314002]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
iap
Member

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

весёлый пример!
Автор полагает, что если @TL_ID IS NULL, то всё равно может выполниться уловие A.TL_ID = @TL_ID
Уже забавно.

Это всё параметры процедуры? И @USR_IDS тоже?
Почему бы в таком случае в начале процедуры не убрать из него пробелы и не окружить запятыми,
чтобы в запросе вместо вызова функции просто писать @USR_IDS LIKE '%,'+CAST(A.USR_ID AS VARCHAR)+',%'
Хотя это по-любому выглядит уродливо - лучше передавать через табличный параметр (SQL2008).

exec sp_executesql (....) - это динамический запрос, который рекомендуется всячески избегать и применять в крайнем случае.

Все условия (с учётом возможного равенства параметров NULLу) можно переписать намного короче и оптимальнее.
Даже может быть, и индексы удастся использовать.
26 мар 12, 13:48    [12314073]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
netivan
Member

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

нет, ну по поводу IS NULL мне как раз ясно, грубо говоря если параметр NULL то мы его не учитываем ( получается типа 1=1).
А по поводу динамического - вроде бы для sp_executsql() даже оптимизатор используется и план строится, разве я не прав? Но больше всего меня удивляет почему нельзя условия в одно WHERE? Кстати пробовал в одно условие - план не меняется.
26 мар 12, 14:38    [12314461]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
AndyD
Member

Откуда:
Сообщений: 30
Можно так сделать
(@TL_ID IS NULL OR A.TL_ID = @TL_ID) AND
...

Смысл тот же, а запись короче

По полям TL_ID и TL_DATE есть составной индекс?
Может быть, автор запроса думал, что внешний WHERE так же будет использовать индексы по полям, не входящим в него?
26 мар 12, 15:00    [12314625]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Можно в один WHERE и подзапрос тут ни к чему.
Условия лучше написать так:
WHERE A.TL_ID BETWEEN ISNULL(@TL_ID,-2147483648) AND ISNULL(@TL_ID,2147483647)
  AND A.TL_DATE <= ISNULL(@TL_DATE_FROM,'99991231')
  AND A.TL_DATE >= ISNULL(@TL_DATE_TO,'1753')
  AND A.LT_ID BETWEEN ISNULL(@LT_ID,-2147483648) AND ISNULL(@LT_ID,2147483647)
--и т.д.
26 мар 12, 15:06    [12314677]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
netivan
Member

Откуда:
Сообщений: 8768
AndyD
Можно так сделать
(@TL_ID IS NULL OR A.TL_ID = @TL_ID) AND
...

Смысл тот же, а запись короче

По полям TL_ID и TL_DATE есть составной индекс?
Может быть, автор запроса думал, что внешний WHERE так же будет использовать индексы по полям, не входящим в него?

запись в самом деле короче, спс. Индекса такого нет, есть по TL_ID и TL_DATE отдельно. ПО Tl_ID кластеризованный. Мне кажется или оптимизатор все равно воспринимает эти 2 условия как одно where?
26 мар 12, 15:10    [12314719]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31965
netivan
Мне кажется или оптимизатор все равно воспринимает эти 2 условия как одно where?
Для сервера нет "внутреннего" и "внешнего" запроса, он пытается понять семантику запроса в целом и построить его план. Только для очень больших запросов имеет значение внешний вид, просто потому, что серверу становится невыгодно перебирать все варианты.
26 мар 12, 16:35    [12315594]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
netivan,

Если запрос вызывается относительно редко, при этом таблицы большие и скорость критична, то:
1. результаты dbo.Split во временные таблицы
2. 
OPTION(RECOMPILE)
3. ну и конечно же можно все условия в один WHERE, разницы не будет
27 мар 12, 00:56    [12317878]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
netivan,

Смотри соседний топик, может натолкнёт на идею. У меня похожая задача сегодня решалась.
https://www.sql.ru/forum/actualthread.aspx?tid=929489
27 мар 12, 01:48    [12317915]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SandalTree
netivan,

Смотри соседний топик, может натолкнёт на идею. У меня похожая задача сегодня решалась.
https://www.sql.ru/forum/actualthread.aspx?tid=929489

Вы предлагаете топикстартеру разложить 12 параметров на IF ELSE? А че, всего 4096 вариантов получается. Ну вы и садист однако :)
27 мар 12, 02:58    [12317937]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Mind
SandalTree
netivan,

Смотри соседний топик, может натолкнёт на идею. У меня похожая задача сегодня решалась.
https://www.sql.ru/forum/actualthread.aspx?tid=929489

Вы предлагаете топикстартеру разложить 12 параметров на IF ELSE? А че, всего 4096 вариантов получается. Ну вы и садист однако :)
Там вообще-то ещё одно решение есть, более простое, с использованием переменных.
Я-ж не знаю как ТС их использует.

И оно должно сработать без ифов, это у меня немеряная куча таблиц была, а у него-то всего одна.


Можно ещё попытаться выложить первый запрос во временную таблицу. Тоже может ускорить.
27 мар 12, 05:50    [12318003]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SandalTree
Mind
пропущено...

Вы предлагаете топикстартеру разложить 12 параметров на IF ELSE? А че, всего 4096 вариантов получается. Ну вы и садист однако :)
Там вообще-то ещё одно решение есть, более простое, с использованием переменных.

Это решение переключает оптимизатор в режим - "оптимизируй под значения которых я тебе не скажу". В определенных случаях помогает, но далеко не всегда.
27 мар 12, 08:17    [12318115]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31965
Mind
SandalTree
netivan,

Смотри соседний топик, может натолкнёт на идею. У меня похожая задача сегодня решалась.
https://www.sql.ru/forum/actualthread.aspx?tid=929489

Вы предлагаете топикстартеру разложить 12 параметров на IF ELSE? А че, всего 4096 вариантов получается. Ну вы и садист однако :)
А что, хороший вариант :-)

Конечно, не 4096 вариантов, а по статистике найти несколько самых частых вызовов, а остальное с OPTION(RECOMPILE). Я обычно так делаю.
27 мар 12, 11:20    [12318993]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
netivan
Member

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

про Split не понял, он разве не временную создаст?:) Да и потом там строка из очень небольшого значения.
Задача по большому счета банальна. Есть одна большая таблица Т с транзакциями(бизнес понятие, а не sql). Кто-то хочет искать транзакции по разным параметрам( пусть их 10 штук). причем комбинация может быть абсолютно разная, поэтому построить индексы под все комбинации просто нереально ( точнее реально, то боюсь не окупится по скорости)) ). Вот и ищется "оптимальное" решение, чтобы "не тормозило":).
И еще хочу понять чем sp_execsql() хуже данного запроса?
27 мар 12, 11:41    [12319136]     Ответить | Цитировать Сообщить модератору
 Re: объясните оптимизацию запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
netivan
Mind,

про Split не понял, он разве не временную создаст?:) Да и потом там строка из очень небольшого значения.

Он это кто? Сервер? :)
Он создаст подобие табличной переменной, но основная проблема не в этом, а в том что ожидаемое количество строк всегда будет равно 1. Если у вас там реально ожидается не больше 2-5 значений, то тогда нормально, а если там будут передавать сотню, то лучше переделать.

netivan
И еще хочу понять чем sp_execsql() хуже данного запроса?

Не надо давать права на таблицу. 
Не надо переживать по поводу injection.
Легче читать, понимать и сопровождать.
Нет вероятности нарваться на ошибку в процессе выполнения, если по каким то причинам динамический SQL сформирован неверно для какой-то комбинации параметров.
Проще отслеживать зависимости объектов.
sp_execsql будет создавать разные планы в для различных комбинаций параметров только в случаях если параметр поменял свое значение с NULL или NOT NULL или наоборот. Но при этом если комбинация переданных параметров осталась такая же, а значение одного их них изменилось скажем с ID = 10 на ID = 25, то будет использован старый план, при этом не важно подходит он под это новое значение или нет. Если у вас данные распределены неравномерно, то это может быть критичным.

netivan
Задача по большому счета банальна. Есть одна большая таблица Т с транзакциями(бизнес понятие, а не sql). Кто-то хочет искать транзакции по разным параметрам( пусть их 10 штук). причем комбинация может быть абсолютно разная, поэтому построить индексы под все комбинации просто нереально ( точнее реально, то боюсь не окупится по скорости)) ). Вот и ищется "оптимальное" решение, чтобы "не тормозило":).

Как вариант, можно определить несколько критичных и достаточно селективных параметров, создать по ним индексы и требовать от пользователя чтобы он ввёл хотя бы один из них, ну а в процедуре сделать IF ELSE ветвления только для этих нескольких параметров. такое решение требует тщательного анализа и необходимо в случае если у вас процедуру дергают каждую секунду, если же это не так, то положитесь лучше на оптимизатор сервера, пусть перекомпилирует при каждом вызове. Ну и само собой, это для SQL2008 SP2 и выше.
27 мар 12, 20:40    [12323644]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить