How to optimize a query? Write request without or

Question

There is, for example, the following query:

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='')

This query takes longer than a query with the following conditions:

 c.IINBIN = @IINBIN and c.[address] = @address

It is necessary for me that sampling was done without specifying the parameters. those. I leave the fields empty and the selection is made without filling in the parameter fields
How to write a query without or and how to optimize it?

You have select cl.* from ... ( cl = c presumably), i.e.
You choose customers, and predicates you have on the table of customers.
@ i-one, well, this is for example, the problem is that the request works longer when adding or @ IINBIN = '' or or @ address = ''
and the conditions @ IINBIN = '' it is necessary that with an empty value everything

i-one i-one 6,896 ten 28 · Accepted Answer · 2016-04-27T06:40:59

Most likely for requests with conditions

 where (c.IINBIN = @IINBIN or @IINBIN = '') and (c.[address] = @address or @address = '')

and

 where c.IINBIN = @IINBIN and c.[address] = @address

different plans are being made.

First of all, you should of course look at the semantics of the query and, perhaps, try to rewrite it somehow more optimally. If there is no place to move further in this direction, then you can try the following.

Try to rebuild the indexes and update the statistics (this should be done for each of the tables participating in the query):

 alter index all on [TableName] rebuild GO update statistics [TableName] with fullscan GO

More relevant statistics can help build a more optimal plan, and freshly built unfragmented indices help it to be executed more quickly.

You can also try adding the recompile option.

 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)

This will force the query processor to re-compile the query each time, taking into account the actual values of the @address and @IINBIN . Recompilation takes time, so it is not always possible to get a win in this way.

Finally, you can split the request into two independent ones with the help of an 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 = '')

This is a kind of compromise between a request with the recompile option and without it.

And, of course, you should have useful indexes on the tables for the query. If IINBIN is unique, then I would add an index.

 create unique index IX_Clients_IINBIN on Clients (IINBIN) include (address)

if not unique

 create index IX_Clients_1 on Clients (IINBIN, address)

(we put forward a more selective column).

For the case when the search goes only by address , you can also add your own index:

 create index IX_Clients_2 on Clients (address)

In general, queries of this type (when columns can be filtered in various combinations.) Are not the easiest to optimize, especially if there are not 2 columns, but much more.

cool thanks, I put down the indices, c recompile or via if else quickly runs
But if you need to use if / else, then for a large number of parameters you will have to resort to dynamic queries, because the number of variants of filter combinations grows exponentially ...
@Pavel Mayorov, compared, recompile is enough and runs much faster
@Zhandos is worth considering that the compilation of a query can cause much more time than its execution (for some queries it is several orders of magnitude more), and it should be used only as a last resort.

Answer 2 · 2016-04-27T06:28:22

The easiest way is to generate a query dynamically depending on the installed filters. I did not find a way to fit an optional filter into a query and not lose it in optimization.

If we are talking about pure SQL, then there is no way to dynamically generate a query and not spawn injections. But you can add a managed assembly to the database, in which you declare a function that already generates a dynamic query to the database and returns the result.

But, since the solution from the user i-one option(recompile) works fairly quickly - I don’t see the need to paint this option in more detail.

I ask an example of the dynamism of the request in my cases - Zhandos
@Zhandos depends on what language you are going to compile - Pavel Mayorov
@Zhandos php, java, c #, object pascal? Vba? Or do you make a request from a batch file? Powershell? - Pavel Mayorov

How to optimize a query? Write request without or

2 answers 2

More articles: