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
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.
这有可能成为问题,因为这是一种catch all查询。检查这篇文章,如果你有多个标准,你需要像这样检查。
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
#2
1
Use a UNION
使用一个联盟
select Column1 ... Column290 from dob.table1 where Column50 is null
union
select Column1 ... Column290 from dob.table1 where Column50 = 1
#3
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.
正如您在这里所说的,在Where子句中使用ISNULL的效率似乎不如使用ISNULL的效率。
#1
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.
这有可能成为问题,因为这是一种catch all查询。检查这篇文章,如果你有多个标准,你需要像这样检查。
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
#2
1
Use a UNION
使用一个联盟
select Column1 ... Column290 from dob.table1 where Column50 is null
union
select Column1 ... Column290 from dob.table1 where Column50 = 1
#3
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.
正如您在这里所说的,在Where子句中使用ISNULL的效率似乎不如使用ISNULL的效率。