Есть, например, следующий запрос:
declare
@IINBIN nvarchar(12)='531008300343'
,@address nvarchar(200)='г.Алматы'
select
cl.*
From
dbo.Deals d
left join clients c on c.clients_id = d.clientsId
where
(c.IINBIN = @IINBIN or @IINBIN='')
and (c.[address] =@address or @address='')
Этот запрос выполняется дольше, чем запрос со следующими условиями:
c.IINBIN = @IINBIN and c.[address] = @address
Мне надо чтобы без указания параметров выборка делалась полная. т.е. оставляю поля пустыми и делается выборка без заполнения полей параметров
Как написать запрос без or и как его оптимизировать?
Ответ
Вероятнее всего для запросов с условиями
where
(c.IINBIN = @IINBIN or @IINBIN = '')
and (c.[address] = @address or @address = '')
и
where
c.IINBIN = @IINBIN and c.[address] = @address
строятся разные планы.
Во-первых стоит конечно же посмотреть на семантику запроса и, быть может, постараться переписать его как-то более оптимально. Если в этом направлении дальше двигаться некуда, то можно попробовать следующее.
Попробовать перестроить индексы и обновить статистики (сделать это нужно для каждой из таблиц, участвующих в запросе):
alter index all on [TableName] rebuild
GO
update statistics [TableName] with fullscan
GO
Более актуальные статистики могут помочь построению более оптимального плана, а свежепостроенные нефрагментированные индексы - более быстрому его исполнению.
Также можно попробовать добавить опцию recompile
select c.*
from
dbo.Deals d
left join clients c on c.clients_id = d.clientsId
where
(c.IINBIN = @IINBIN or @IINBIN='')
and (c.[address] = @address or @address = '')
option (recompile)
Это заставит query processor каждый раз компилировать запрос заново, учитывая актуальные значения переменных @address и @IINBIN. Перекомпиляция занимает время, поэтому не всегда таким способом можно получить выигрыш.
Наконец можно разбить запрос на два независимых с помощью if ... else
if @IINBIN != '' and @address != ''
select c.*
from
dbo.Deals d
left join clients c on c.clients_id = d.clientsId
where
c.IINBIN = @IINBIN and c.[address] = @address
else
select c.*
from
dbo.Deals d
left join clients c on c.clients_id = d.clientsId
where
(c.IINBIN = @IINBIN or @IINBIN = '')
and (c.[address] = @address or @address = '')
это своеобразный компромисс между запросом с опцией recompile и без неё.
Ну и, конечно же, у вас должны быть полезные для запроса индексы на таблицах. Если IINBIN уникален, то я бы добавил индекс
create unique index IX_Clients_IINBIN on Clients (IINBIN) include (address)
если не уникален, то
create index IX_Clients_1 on Clients (IINBIN, address)
(более селективный столбец ставим вперёд).
Для случая, когда поиск идёт только по address, тоже можно добавить свой индекс:
create index IX_Clients_2 on Clients (address)
Вообще запросы такого вида (когда столбцы могут фильтроваться в самых разных сочетаниях.) не самые лёгкие для оптимизации, в особенности, если столбцов не 2, а намного больше.
Комментариев нет:
Отправить комментарий