4

I have the following large select query which return 1.5 million rows in 08:15. I need to optimize the query which selects about 290 columns and I can't reduce the number of columns to improve the speed.

select Column1 ... Column290
from dob.table1
where (ISNULL(Column50, 1) = 1)

I have read that ISNULL has a performance cost if used in the WHERE clause because the optimizer does not use the index but resort to scan, correct?

I am trying to figure out how to rewrite the 

WHERE (ISNULL(Column50, 1) = 1)

I tried using with cte and setting the 

IP_Column50 = case when IP_Column50 is null then else IP_Column50 end

and rewriting my query to 

select * 
from cte 
where IP_Column50 = 1

but the CTE took longer.

I read about this approach

If so, consider creating a computed column with the result if isnull(col1, 0) and index the computed column and use it in your where-clause

But I am not sure how to implement this. Any help is appreciated with optimizing this query.

Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
John
  • 211
  • 2
  • 12

3 Answers3

2

You can do this in a single query too. Like this.

select Column1 ... Column290
from dob.table1
where Column50 = 1
OR Column50 IS NULL

This has the potential though of becoming problematic as this is a type of catch all query. Check out this article if you have multiple criteria you need to check like this.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
1

Use a UNION

select Column1 ... Column290 from dob.table1 where Column50 is null
union
select Column1 ... Column290 from dob.table1 where Column50 = 1
Xavier J
  • 4,326
  • 1
  • 14
  • 25
1

You could just do

select Column1 ... Column290
from dob.table1
where Column50 IS NULL OR (Column50 IS NOT NULL AND Column50 = 1)

As you said and as shown here, it seems that using ISNULL in the Where clause is less efficient than using IS NULL.

Community
  • 1
  • 1
Gabriel Rainha
  • 1,713
  • 1
  • 21
  • 34
  • 2
    Why do you check for both = 1 AND NOT NULL inside the parenthesis? By definition if the value is 1 it can't possibly be NULL. :) – Sean Lange Nov 10 '16 at 20:31
  • @SeanLange I guess you're right in this case, but I have no way of testing it now =/ It's just an old habbit of mine when comparing any nulls as to avoid [any problems with relational comparison involving said nulls](http://stackoverflow.com/a/1833975/4905310). – Gabriel Rainha Nov 10 '16 at 20:57
  • @SeanLange You're absolutely right on that case. Just tested and indeed there was no need to check for IS NULL and IS NOT NULL at the same time. Thanks for the call. – Gabriel Rainha Nov 11 '16 at 18:26