欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

程序员文章站 2022-05-18 21:05:11
之前写过一篇博客“SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析”,里面介绍了OR可能会引起全表扫描或索引扫描的各种案例,以及如何优化查询条件中含有OR的SQL语句的几种方法,其实还有一些方法可以用来优化这种问题,这里简单介绍一下。 如下所示,下面的SQL语句之所有出现这种写法,是因为... ......

之前写过一篇博客sql server中关于or会导致索引扫描或全表扫描的浅析,里面介绍了or可能会引起全表扫描或索引扫描的各种案例,以及如何优化查询条件中含有or的sql语句的几种方法,其实还有一些方法可以用来优化这种问题,这里简单介绍一下。

 

如下所示,下面的sql语句之所有出现这种写法,是因为程序的查询界面,可能有多个输入性的查询条件,往往用户只填了一个或部分查询条件(业务情况,应该不用详细介绍,大家都能明白),但是程序里面没有通过判断查询条件生成不同的sql语句,而是用一个sql搞定,不管用户没有填写jobno这个查询条件,下面这种写法:where isnull(@jobno, '') = ''  or jobno = @jobno都能满足条件,实现逻辑功能。

 

declare @generatedatestart datetime ,
    @generatedateend datetime ,
    @jobno nvarchar(200) ,
    @gkno nvarchar(200);
set @jobno = 'pt19b030';
set @gkno = 'pv19-1-8050'; 
 
  select    *
  from      [dbo].[gew_unitconsumption] as a
            left join dbo.unitconsumption_relation as b on b.usableflag = 'y'
                                                           and a.gewunitconsumptionid = b.rootunitconsumptionid
  where     ( isnull(@jobno, '') = ''
              or a.jobno = @jobno
            )
            and ( isnull(@gkno, '') = ''
                  or a.gkno = @gkno
                );

 

其实,如果根据查询条件动态生成sql语句,的确能避免查询条件中出现or的情形,但是动态sql语句没有上面语句简单和通熟易懂,尤其是查询条件较多的情况下。只能说各有利弊。这里暂且不讨论那种策略的优劣。

 

 

 

下面介绍一种技巧,如何避免or引起的索引扫描或全表扫描问题。我们可以使用case when改写一下这个sql语句,就能避免or引起的执行计划不走索引查找(index seek)的情况,如下所示:

 

declare @generatedatestart datetime ,
    @generatedateend datetime ,
    @jobno nvarchar(200) ,
    @gkno nvarchar(200);
set @jobno = 'pt19b030';
set @gkno = 'pv19-1-8050'; 
 
 
select  *
from    [dbo].[gew_unitconsumption] as a
        left join dbo.unitconsumption_relation as b on b.usableflag = 'y'
                                                       and a.gewunitconsumptionid = b.rootunitconsumptionid
where   case when isnull(@jobno, '') = '' then a.jobno
             else @jobno
        end = jobno
        and case when isnull(@gkno, '') = '' then a.gkno
                 else gkno
            end = @gkno;

 

 

测试对比发现性能改善非常明显,当然这种优化技巧也是有局限性的,并不能解决所有or引起的性能问题(没有银弹!)。如下所示,对于下面这种情况,这种技巧也是无能为力!

 

 

select * from test1 where a=12 or b=500