使用ISNULL where子句优化大型查询。

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查询，它在08:15返回150万行。我需要优化查询，选择大约290列，我不能减少列的数量来提高速度。

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?

我已经读过，如果在WHERE子句中使用了ISNULL，因为优化器不使用索引，而是使用扫描，对吗?

I am trying to figure out how to rewrite the

我在想怎么重写

WHERE (ISNULL(Column50, 1) = 1)

I tried using with cte and setting the

我试着用cte设置

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.

但是CTE花了更长的时间。

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

如果是，考虑使用isnull(col1, 0)创建一个计算列，并对计算列进行索引，并在where子句中使用它

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

但是我不知道如何实现这个。对于优化这个查询，我们非常感激。

Thanks

谢谢

3 个解决方案

#1

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.

这有可能成为问题，因为这是一种catch all查询。检查这篇文章，如果你有多个标准，你需要像这样检查。

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

#2

Use a UNION

使用一个联盟

select Column1 ... Column290 from dob.table1 where Column50 is null
union
select Column1 ... Column290 from dob.table1 where Column50 = 1

#3

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.

正如您在这里所说的，在Where子句中使用ISNULL的效率似乎不如使用ISNULL的效率。

#1