Как оптимизировать запрос? Написать запрос без or

#sql #sql_server

Есть, например, следующий запрос:  

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 и как его оптимизировать?
    

Ответы

Ответ 1

Вероятнее всего для запросов с условиями

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, а намного больше.


Ответ 2

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



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

Но, поскольку решение с пользователя i-one option(recompile) работает достаточно
быстро - необходимости расписывать этот вариант подробнее я не вижу.