0

I have the below SQL Query getting data from View, the View has almost 175,000 record.

The query needs to filter on some parameters before get the data, but it takes very long time to execute.

Where I make the mistake, could you please help me.

This is the query code:

select 

 [Col1]      
,[Col2]  
,[Col3]  
,[Col4]      
,[Col5]      
,[Col6]      
,[Col7]      
,[Col8]          
,[Col9]      
,[Col10]     
,[Col11]         
,[Col12]     
,[Col13]         
,[Col14]         
,[Col15]    
,[Col16]     
,[Col17]        
,[Col18] 
,[Col19]
          
from Tabel_1

where (

        (([Col1]            = IsNull(@Col1,  [Col1]))       or ([Col1]    is null))
and         (([Col2]        = IsNull(@Col2,  [Col2]))   or ([Col2]    is null))
and         (([Col3]            = IsNull(@Col3,  [Col3]))   or ([Col3]    is null))
and         (([Col4]            = IsNull(@Col4,  [Col4]))   or ([Col4]    is null))
and         (([Col5]        = IsNull(@Col5,  [Col5]))   or ([Col5]    is null))
and         (([Col6]        = IsNull(@Col6,  [Col6]))   or ([Col6]    is null))
and         (([Col7]        = IsNull(@Col7,  [Col7]))   or ([Col7]    is null))
and         (([Col8]        = IsNull(@Col8,  [Col8]))   or ([Col8]    is null))
and         (([Col9]        = IsNull(@Col9,  [Col9]))   or ([Col9]    is null))
and         (([Col10]       = IsNull(@Col10, [Col10]))  or ([Col10]   is null))
and         (([Col11]       = IsNull(@Col11, [Col11]))  or ([Col11]   is null))
and         (([Col12]       = IsNull(@Col12, [Col12]))  or ([Col12]   is null))
and         (([Col13]       = IsNull(@Col13, [Col13]))  or ([Col13]   is null))
and         (([Col14]       = IsNull(@Col14, [Col14]))  or ([Col14]   is null))
and         (([Col15]           = IsNull(@Col15, [Col15]))  or ([Col15]   is null))


)
Mike
  • 38
  • 7
  • 1
    `@Col1 is null or Col1 = @Col1` might give faster results then `IsNull()` – GuidoG Nov 24 '21 at 09:16
  • 2
    Looks like a "catch all" query. I suggest a read of these articles: [Catch-all queries](https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) & [An Updated Kitchen Sink Example](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example) – Thom A Nov 24 '21 at 09:16
  • 2
    Side note, using unecessary parenthesis makes things harder, not easier, to read. – Stu Nov 24 '21 at 09:17
  • [This](https://stackoverflow.com/questions/5024090/sargable-queries-using-isnull-in-tsql) might be of use to you – GuidoG Nov 24 '21 at 09:20
  • 2
    Honestly, if this is a stored procedure, I would likely recommend using dynamic SQL here; with 15 `NULL`able parameters it will likely be the (overall) more performant option. Using the syntax @GuidoG recommends, and then adding `RECOMPILE` to the `OPTION` clause will still give better results, but all those `OR`s could still lead to a less performant result (and the data engine will need to generate the plan every time the query is run). – Thom A Nov 24 '21 at 09:24
  • What are your constraints and indexes? Can you share the query plans via https://brentozar.com/pastetheplan – Charlieface Nov 24 '21 at 09:29
  • @GuidoG Thank you vary much my friend, I used the syntax that you suggest `@Col1 is null or Col1 = @Col1` and it's much better now. The data is retreived in zero seconds now :) – Mike Nov 24 '21 at 10:04
  • I suggest your logic is incorrect. For example if `@Col151` contains a value you want to filter on, your logic will include a row if the Col15 column contains null. That does not seem correct to me. – SMor Nov 24 '21 at 12:27
  • @Stu, Let assume for a minute that we are not discussing performance of above query nor result.There is not even a single unecessary parenthesis.all parenthesis are justified and is neccessary for to give correct result. – KumarHarsh Nov 24 '21 at 15:36
  • I thought your requirement is also to to get all rows where [Col1] is null) irrespective of @Col1 value. – KumarHarsh Nov 24 '21 at 15:39
  • @KumarHarsh no that's simply not true, half can be removed with no effect, including the very first and last. – Stu Nov 24 '21 at 17:08
  • @Larnu your suggeted solution regarding the dynamic SQL with @GuidoG syntax is working prefectly and high performance with the big size of data. Also I added `RECOMPILE` to the `OPTION` clause like you said. I catched your idea regarding all these unnecessary `OR` and i must gave the data engine a clear query to excute. Thanks man – Mike Nov 27 '21 at 20:58

0 Answers0