Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Стиль написания условий в запросах. Как быстрее?  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Я заметил, что у каждого разработчика есть свой стиль написания запросов в процедурах. Рассмотрим типовую задачу фильтрации данных по параметру, если он задан. И отсутствие фильтрации, если не задан:

SELECT * FROM material m
WHERE m.document_num = ISNULL(@DocumentNum, m.document_num)

SELECT * FROM material m
WHERE @DocumentNum IS NULL OR m.document_num = @DocumentNum


Чем отличаются эти ситуации? Я заметил, что в первом случае сервер умеет строить параллельный план. Первый запрос выполняется существенно быстрее. Однако, если параллелизм не прокатил, то будет чуть-чуть быстрее второй запрос.
17 июн 16, 13:33    [19304880]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Типовая задача над шарообразными таблицами с индексами в вакууме?
17 июн 16, 13:36    [19304905]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev,

1. ну вообще-то запросы разные и результаты могут быть разные.
2. и да это обсуждение ежа в вакууме, всё зависит от индексов/статистик
17 июн 16, 14:13    [19305110]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Параллельный план может возникнуть из-за превышения ожидаемого времени выполнения установленного порога. Как раз параллельный - признак неэффективности запроса. Разумеется, если оба варианта могут быть распараллелены.
17 июн 16, 14:19    [19305156]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
В Вашем случае лучше выполнение на два запроса, дабы не создавать "универсальный" план.
17 июн 16, 14:22    [19305182]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Как в первом запросе получить записи с m.document_num IS NULL?
17 июн 16, 14:36    [19305284]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
monstrU
Member

Откуда: Москва
Сообщений: 1216
Dmitry V. Liseev
Я заметил, что у каждого разработчика есть свой стиль написания запросов в процедурах. Рассмотрим типовую задачу фильтрации данных по параметру, если он задан. И отсутствие фильтрации, если не задан:

SELECT * FROM material m
WHERE m.document_num = ISNULL(@DocumentNum, m.document_num)

SELECT * FROM material m
WHERE @DocumentNum IS NULL OR m.document_num = @DocumentNum


Чем отличаются эти ситуации? Я заметил, что в первом случае сервер умеет строить параллельный план. Первый запрос выполняется существенно быстрее. Однако, если параллелизм не прокатил, то будет чуть-чуть быстрее второй запрос.


как то искали отчего долго выборка происходит - второй вариант оказался быстрее из-за того, что нет затрат на использование функции isnull
17 июн 16, 14:52    [19305376]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
или крестик снять или трусы надеть:
запросы можно сравнивать как "одинаковые", только для случая когда колонка document_num IS NOT NULL

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

автор
второй вариант оказался быстрее из-за того, что нет затрат на использование функции isnull

ставлю, что в вашем варианте были затраты на конвертацию, а не на null...
17 июн 16, 15:01    [19305441]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
iap
Как в первом запросе получить записи с m.document_num IS NULL?

именно. Логика запросов разная.
Либо, судя по всему так и есть поле document_num NOT NULL
17 июн 16, 15:35    [19305688]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Dmitry V. Liseev
Я заметил, что у каждого разработчика есть свой стиль написания запросов в процедурах. Рассмотрим типовую задачу фильтрации данных по параметру, если он задан. И отсутствие фильтрации, если не задан:

SELECT * FROM material m
WHERE m.document_num = ISNULL(@DocumentNum, m.document_num)

SELECT * FROM material m
WHERE @DocumentNum IS NULL OR m.document_num = @DocumentNum


Чем отличаются эти ситуации? Я заметил, что в первом случае сервер умеет строить параллельный план. Первый запрос выполняется существенно быстрее. Однако, если параллелизм не прокатил, то будет чуть-чуть быстрее второй запрос.



if @DocumentNum is null
SELECT * FROM material m
else
SELECT * FROM material m
WHERE @DocumentNum IS NULL OR m.document_num = @DocumentNum


Так побыстрее будет ))))
17 июн 16, 17:46    [19306506]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
if @DocumentNum is null
SELECT * FROM material m
else
SELECT * FROM material m
WHERE m.document_num = @DocumentNum

всмысле так, ну ты понял )))) описался
17 июн 16, 17:47    [19306516]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
zasandator
if @DocumentNum is null
SELECT * FROM material m
else
SELECT * FROM material m
WHERE m.document_num = @DocumentNum

всмысле так, ну ты понял )))) описался

уберите за собой :) и да логика отстой :) если запросы начнут сильно усложняться и переменных станет больше вы под каждый вариант будете писать if-else?
17 июн 16, 20:37    [19307191]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
TaPaK
zasandator
if @DocumentNum is null
SELECT * FROM material m
else
SELECT * FROM material m
WHERE m.document_num = @DocumentNum

всмысле так, ну ты понял )))) описался

уберите за собой :) и да логика отстой :) если запросы начнут сильно усложняться и переменных станет больше вы под каждый вариант будете писать if-else?


Да под каждый вариант... Вы лучше предложите "более" лучшее решение )))).
Если посмотреть планы запросов к примеру:
use db1
go
if object_id('big_tbl','U') is not null drop table big_tbl;
with cte as
(
select 1 as id
union all
select id+1
from cte
)
select top(1000000) id, replicate(cast(newid() as varchar(1000)),5) as gd
into big_tbl
from cte
option (maxrecursion 0)
go
create unique clustered index pk_big_tbl on big_tbl (id)
go
-- здесь начинаем смотреть планы запросов
declare @id int=12345

--1
select count(*) from big_tbl where id=isnull(@id,id)
--2
select count(*) from big_tbl where id=@id or @id is null
--3
select count(*) from big_tbl where id=isnull(@id,id) option(recompile)
--4
select count(*) from big_tbl where id=@id or @id is null option(recompile)
--5
if @id is not null
select count(*) from big_tbl where id=@id
else
select count(*) from big_tbl
--6
select count(*) from big_tbl where id=@id and @id is not null
union all
select count(*) from big_tbl where @id is null
option(recompile)

скрипта, комментируя оставляя опцию recompile. Видно сразу что предложенный ТС вариант без рекомпайл всегда выводит на сканирование таблицы. Рекомпайл спасайт хоть как то, в мелких селектах еще фиг бы с ним, и то зависит от того как часто этот селект будет выполняться... На мой взгляд if else самый приемлемый вариант с разовой компиляцией скрипта, и разными планами, от условий... А то что запросы будут усложняться это да... очевидно. Если веток запросов реально много может даже и в итоге проще будет что то типа такого:
--7
declare @cmd nvarchar(max) = 'select count(*) from big_tbl where 1=1'
declare @gd varchar(1000)
if @id is not null set @cmd += ' and id=@id'
if @gd is not null set @cmd += ' and gd=@gd' --на случай если параметров будет много
print @cmd
exec sp_executesql @cmd, N'@id int, @gd varchar(1000)', @id, @gd

здесь и перекомпиляция планов не будет постоянной, и "sargable predicates", план в поиск уйдет гарантированно, если это нужно.
18 июн 16, 14:48    [19308741]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
zasandator,

автор
скрипта, комментируя оставляя опцию recompile. Видно сразу что предложенный ТС вариант без рекомпайл всегда выводит на сканирование таблицы. Рекомпайл спасайт хоть как то, в мелких селектах еще фиг бы с ним, и то зависит от того как часто этот селект будет выполняться... На мой взгляд if else самый приемлемый вариант с разовой компиляцией скрипта, и разными планами, от условий...

даже не знаю с какого слова начать...
автор
будет что то типа такого:
будем надеяться sql у вас просто хобби
19 июн 16, 10:17    [19310415]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
TaPaK
zasandator,

автор
скрипта, комментируя оставляя опцию recompile. Видно сразу что предложенный ТС вариант без рекомпайл всегда выводит на сканирование таблицы. Рекомпайл спасайт хоть как то, в мелких селектах еще фиг бы с ним, и то зависит от того как часто этот селект будет выполняться... На мой взгляд if else самый приемлемый вариант с разовой компиляцией скрипта, и разными планами, от условий...

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


Ваше решение подобных задач какое?
19 июн 16, 10:21    [19310418]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mike_za,

автор
WHERE @DocumentNum IS NULL OR m.document_num = @DocumentNum
19 июн 16, 10:26    [19310423]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
TaPaK,
Есть опыт выполнения такого кода на реальных таблицах?
19 июн 16, 10:30    [19310430]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mike_za,
да, любого размера
19 июн 16, 10:33    [19310435]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
TaPaK,
И что говорит ваш опыт про прослушивание параметров и повторное использование планов?
19 июн 16, 10:34    [19310439]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mike_za,

https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
19 июн 16, 10:44    [19310453]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

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

Возвращаясь к сути темы: если у вас запрос с произвольной комбинацией параметров. Распределение вызовов по под каждую комбинацию равное. Под каждый вариант есть нужные индексы?
19 июн 16, 10:48    [19310463]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mike_za,

вы хотите что бы я проверил есть ли на моих таблицах нужные индексы?
19 июн 16, 10:56    [19310480]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

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

Нет, я хотел услышать, как вы решили проблему плохих планов (и рекомпиляций) поднятых в данном топике.
zasandator ее решил, но его подходы вы сочли "непрофессиональными" :))
19 июн 16, 11:03    [19310491]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mike_za
TaPaK,

Нет, я хотел услышать, как вы решили проблему плохих планов (и рекомпиляций) поднятых в данном топике.
zasandator ее решил, но его подходы вы сочли "непрофессиональными" :))

решил динамическим запросом??? ну что тут скажешь - пользуйтесь
19 июн 16, 11:05    [19310496]     Ответить | Цитировать Сообщить модератору
 Re: Стиль написания условий в запросах. Как быстрее?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
TaPaK,
, так я так и не понял. У вас реально таких запросах на продакшене на больших таблицах нету или есть?
19 июн 16, 11:09    [19310500]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить