CODEWITHSUNDEEP
sqlsql-serverperformancewhere-clausequerying
DavidLinares
DavidLinares

Reputation: 61

SQL Server querying : improve performance by reducing WHERE clauses

I have a SQL query from my teammate that I think has a lot of predicates and this is the cause of a bad performance. It looks like this:

WHERE 
      (@IdSenales IS NULL OR senalesIds.id = comp.IdSenal) 
      AND
      (@IdAnunciantes IS NULL OR anunciantesIds.id = comp.IdAnunciante) 
      AND                                    
      (@IdProgramas IS NULL OR programasIds.id = emision.IdProgramaVariante) 
      AND   
      (@IdTipoPublicidades IS NULL OR publicidadesIds.id = orden.IdTipoPublicidad) 
      AND
      (@Canje = 0 OR (@canje = 1 AND comp.IdTipoCondicionCobro !=  12)) 
      AND 
      (emision.Fecha BETWEEN @FechaDesdeContrato AND ISNULL(@FechaHastaContrato, emision.fecha)) 
      AND
      (comp.FechaEmision BETWEEN @FechaDesde AND @FechaHasta) 
      AND                                                   
      (@IdSectorImputacion = 0 OR @IdSectorImputacion = simp.IdSectorImputacion)

I'm from Argentina so it's commented in spanish (sorry).

My questions is, is it a way to improve the performance by changing the comparisons in the WHERE clause with a function created by me that returns the same?

Thank you very much,

David

Upvotes: 0

Views: 1716

Answers (3)

DavidLinares
DavidLinares

Reputation: 61

Thanks for your suggestion @JohnLBevan !! I have checked the predicates because I read an article from Gail Shaw that said:

"Another common cause of SQL Server choosing to scan is a query that contains multiple predicates, when no single index exists that has all the columns necessary to evaluate the WHERE clause. For example, an index on (FirstName, Surname), would fully support any query with a WHERE clause of FirstName = @FirstName AND Surname = @Surname. However, if there was one index on FirstName only, and a second separate index on Surname, then SQL Server can use neither one efficiently. It may choose to seek one index, do lookups for the other columns and then do a secondary filter; it may choose to seek both indexes and perform an index intersection, or it may give up and scan the table."

https://www.red-gate.com/simple-talk/sql/database-administration/gail-shaws-sql-server-howlers/

When I read this I remembered I have seen multiple predicates in my query. I want to mention that this query is one of the most expensive queries that returns my query to check the cost of all the queries against the database. Well, I should check if there is enough indexes and/or create new ones.

David Linares.

Upvotes: 0

JohnLBevan
JohnLBevan

Reputation: 24430

As @GordonLinoff mentions, your best option is to look into the indexes used. He's also a much better coder than me; so take his advice over mine if you're able to. However, if dynamic SQL is not allowed at your company for some reason, or the rewrite is not an option, read on...

You may not have as big a problem as you think here; have you seen a performance problem, or are you just looking at the code & thinking "there's a lot of stuff going on with a lot of brackets, so that's bad"?

i.e. take this line: (@IdSenales IS NULL OR senalesIds.id = comp.IdSenal). This compares a parameter with null, so will only need to be evaulated once, rather than once per line; which isn't too bad. Thereafter it's no different to either not having this statement, or having only senalesIds.id = comp.IdSenal. The same is true for most of these lines.

That said, SQL will generate a query plan the first time it runs this code, and would thereafter use this for all subsequent queries, regardless of which parameters were used; so the plan may be entirely inappropriate for the new set of options. A good fix here is to add OPTION (RECOMPILE). You'll find a good explanation of this here: https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

Beyond that, this line may be a problem, since it involves applying a function, the output of which will be different for each row; so it won't be easy to optimise:

  (emision.Fecha BETWEEN @FechaDesdeContrato AND ISNULL(@FechaHastaContrato, emision.fecha))

Change this to: 

  (emision.Fecha >= @FechaDesdeContrato AND (emision.Fecha <= @FechaHastaContrato ))

...and you should be OK.

Full Code:

WHERE 
    (@IdSenales IS NULL OR senalesIds.id = comp.IdSenal) 
    AND
    (@IdAnunciantes IS NULL OR anunciantesIds.id = comp.IdAnunciante) 
    AND                                    
    (@IdProgramas IS NULL OR programasIds.id = emision.IdProgramaVariante) 
    AND   
    (@IdTipoPublicidades IS NULL OR publicidadesIds.id = orden.IdTipoPublicidad) 
    AND
    (@Canje = 0 OR (@canje = 1 AND comp.IdTipoCondicionCobro !=  12)) 
    AND 
    (emision.Fecha >= @FechaDesdeContrato AND (@FechaHastaContrato is null or emision.Fecha <= @FechaHastaContrato )) 
    AND
    (comp.FechaEmision BETWEEN @FechaDesde AND @FechaHasta) 
    AND                                                   
    (@IdSectorImputacion = 0 OR @IdSectorImputacion = simp.IdSectorImputacion) 
OPTION (RECOMPILE)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is a bit long for a comment.

The only way you can really significantly improve performance is to use indexes. That would require a bunch of indexes for all the different combinations -- but perhaps a few are more common and would suffice for most use-cases.

SQL Server is pretty bad about optimizing complex where clauses. What you could do is use dynamic SQL. Construct the where clause by only putting in the conditions that are necessary.

Then, be sure you have indexes for the common situations. And when the query is compiled, it should run faster.

Upvotes: 4

Related Questions