SQL Server: case when in where condition takes more time

Question

I have the following SQL query which takes more time to run, i.e. more that 4 minutes to execute while executing the same query with static '1' or '0' value takes much less time, i.e. less than 3-4 seconds:

select 
    column1, column2 
from 
    tablename
where  
    bitColumn_1 = (case when @bitColumn_1_param is null 
                           then bitColumn_1 
                           else @bitColumn_1_param 
                   end)
    and bitColumn_2 = (case when @bitColumn_2_param is null 
                               then bitColumn_2 
                               else @bitColumn_2_param 
                       end)
    and bitColumn_3 = (case when @bitColumn_3_param is null 
                               then bitColumn_3 
                               else @bitColumn_3_param 
                       end)
    and bitColumn_4 = (case when @bitColumn_4_param is null 
                               then bitColumn_4 
                               else @bitColumn_4_param 
                       end)
    and bitColumn_5 = (case when @bitColumn_5_param is null 
                          then bitColumn_5 
                          else @bitColumn_5_param 
                       end)
    and bitColumn_6 = (case when @bitColumn_6_param is null 
                               then bitColumn_6 
                               else @bitColumn_6_param 
                       end)
    and bitColumn_7 = (case when @bitColumn_7_param is null 
                               then bitColumn_7 
                               else @bitColumn_7_param 
                       end)

Any help in improving the query would be helpful!

Thanks in advance

Maybe you are using a different set o parameters, like 1 returns lots of results and 0 just a few. Anoher possibility is [parameter sniffing](https://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server) — jean, Dec 16 '17 at 12:51
If a query is different it is not the "same query" but an different query returning (perhaps) the same results - to properly do a comparison and actually answer *we would need to see both* - the execution plans may just differ on those two different queries. — Mark Schultheiss, Dec 16 '17 at 16:26

Hasmukh Viradiya · Answer 1 · 2017-12-16T12:57:18.933

2

you can try below query,this may be help you

 select column1,column2 from tablename
 where bitColumn_1 = ISNULL(@bitColumn_1_param,bitColumn_1)
 AND bitColumn_2 =ISNULL(@bitColumn_2_param,bitColumn_2) 
 AND bitColumn_3 =ISNULL(@bitColumn_3_param,bitColumn_3) 
 AND bitColumn_4 =ISNULL(@bitColumn_4_param,bitColumn_4) 
 AND bitColumn_5 =ISNULL(@bitColumn_5_param,bitColumn_5)
 AND bitColumn_6 =ISNULL(@bitColumn_6_param,bitColumn_6) 
 AND bitColumn_7 =ISNULL(@bitColumn_1_param,bitColumn_7)

edited Dec 16 '17 at 12:57

answered Dec 16 '17 at 12:46

Hasmukh Viradiya

174
8

4

Why do you think OP should try your query? – shA.t Dec 16 '17 at 12:48
where is `coalesce` in my query ?? – Hasmukh Viradiya Dec 16 '17 at 12:51
`ISNULL` uses a type converting also in its differentiation. – shA.t Dec 16 '17 at 12:52

score 0 · Answer 2 · answered Dec 16 '17 at 15:38

0

create table #test
(bitcolumn1 nchar(10),
 bitcolumn2 nchar(10),
 bitcolumn3 nchar(10),
 bitcolumn4 nchar(10),
 bitcolumn5 nchar(10),
 bitcolumn6 nchar(10),
 bitcolumn7 nchar(10)
 )


 insert #test
 values
 ('x1',null,'x3','x4',null,'x6','x7')

 declare 
 @bitcolumn1 nchar(10)=null,
 @bitcolumn2 nchar(10)='z2',
 @bitcolumn3 nchar(10)='z3',
 @bitcolumn4 nchar(10)=null,
 @bitcolumn5 nchar(10)='z5',
 @bitcolumn6 nchar(10)='z6',
 @bitcolumn7 nchar(10)='z7'

 select  * from #test
 where bitcolumn1 =  coalesce(bitcolumn1,@bitcolumn1) and
       bitcolumn2=  coalesce(bitcolumn2,@bitcolumn2) and
       bitcolumn3 = coalesce(bitcolumn3,@bitcolumn3) and
       bitcolumn4 = coalesce(bitcolumn4,@bitcolumn4) and
       bitcolumn5 =  coalesce(bitcolumn5,@bitcolumn5) and
       bitcolumn6 =  coalesce(bitcolumn6,@bitcolumn6) and
       bitcolumn7 =  coalesce(bitcolumn7,@bitcolumn7)

answered Dec 16 '17 at 15:38

Natig Gurbanov

36
4

Would you explain your answer? – Gholamali Irani Dec 16 '17 at 15:45
you need simple case isnull rules – Natig Gurbanov Dec 16 '17 at 15:52
first null or second null use colaesce thats positions – Natig Gurbanov Dec 16 '17 at 15:52
see https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql – Natig Gurbanov Dec 16 '17 at 15:53
I think your parameters might perhaps be in inverse order needed? `coalesce(bitcolumn1,@bitcolumn1)` might need to be `coalesce(@bitcolumn1,bitcolumn1)` as it returns the first non-null result – Mark Schultheiss Dec 16 '17 at 16:21
you can update what your need ;yes coalesce give you first not null parameter ,if will null then second not null parameter – Natig Gurbanov Dec 16 '17 at 16:42

SQL Server: case when in where condition takes more time

2 Answers2