如何为SQL Server调优子查询中带有OR条件的SQL语句?

下面示例显示带有EXISTS子查询的SQL语句。如果在DEPARTMENT表的子查询中满足OR条件,SQL会对EMPLOYEE表中的记录进行计数。

select countn(*) from employee a where
exists (select ‘x’ from department b
   where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
    )

下面是Tosska专有的树结构执行计划,它需要4分29秒才能完成。

该执行计划显示了从EMPLOYEE到全表扫描DEPARTMENT表的嵌套循环,这是整个执行计划的主要问题。因为 SQL Server 无法通过其他连接操作来解决这个OR条件“a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary”。

下面我将子查询中的OR条件改写为UNION ALL子查询,子查询中UNION ALL的第一部分表示“a.emp_id=b.dpt_manager”条件,第二部分表示“a.emp_salary=b.dpt_avg_salary”条件,但排除已经满足第一个条件的数据。

select count(*)
from  employee a
where exists ( select ‘x’
        from   department b
        where a.emp_id = b.dpt_manager
        union all
        select  ‘x’
        from   department b
        where ( not ( a.emp_id = b.dpt_manager )
           or b.dpt_manager is null )
           and a.emp_salary = b.dpt_avg_salary )

下面是改写后SQL的执行计划,看起来有点复杂,但现在性能很好,只需要0.447秒。有两个散列匹配连接用于替换原来从EMPLOYEE表到全表扫描DEPARTMENT表的嵌套循环。

虽然最终改写的步骤有点复杂,但这种改写可以由Tosska SQL Tuning Expert for SQL Server自动完成,这表明该改写SQL比原始SQL快了600多倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

如何调优SQL Server性能不好并且设置返回行数的SQL语句?

在设置返回行数或者使用Top关键字后,某些SQL语句会运行的非常慢。设置返回行数和使用Top关键字会告诉SQL Server从SQL语句中选择特定行数而不是提取所有记录。没有多少人知道在设置返回行数或者使用Top关键字后SQL Server会尝试重新优化您的SQL语句,重新优化后的SQL语句产生的执行计划通常可以更快的检索前几条记录,但是也可能适得其反。

设置返回行数重新优化查询的好示例
下面是一个示例,显示了该SQL从数据库中检索217500行记录需要6.78秒。这是一个好的执行计划,因为它对[DEPARTMENT]和[EMPLOYEE]的两个表扫描进行哈希匹配。

下面屏幕显示了设置返回行数为1后新的执行计划把哈希匹配改为嵌套循环。嵌套循环操作通常提供更快的前几条记录检索时间,但在某些情况下可能不利于整体记录的提取。很高兴看到SQL Server提取此SQL的第一行记录仅用了0.013秒。

设置返回行数重新优化查询的坏示例
让我们来看一个坏示例,它展示了在使用设置返回行数为1后,SQL Server如何将一个好的执行计划变成一个糟糕的执行计划。下面一个示例显示,该SQL从数据库中检索1613行记录花费了0.118秒,虽然该执行计划有点复杂,但对于检索完所有行来说是一个很好的执行计划。

下面屏幕显示了设置返回行数为1后生成的新执行计划,该执行计划现在更改为带有两次表扫描的嵌套循环。新的执行计划提取第一条记录需要1.312秒,比从数据库提取所有行所用的0.118秒还要慢。

如何解决这个问题呢?
我们可以使用提示注入或者SQL语法改写来影响SQL Server为设置返回行数或者Top关键字操作改回原始的执行计划或者生成更好的执行计划。下面是提示注入生成的一个很好的执行计划,它比设置返回行数为1的原始SQL快了90倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

如何调优SQL Server的冷缓存SQL语句?

对于不经常执行的SQL语句,相关的数据可能不在缓存区,冷缓存会显著的影响这类SQL语句的性能。 用于热缓存的高性能SQL语句在冷缓存环境中可能表现的不好。经验丰富的开发人员会调优他们的SQL, 使其在两种情况下都能得到良好运行。

下面是一个SQL示例:
select * from
EMPLOYEE A
where A.EMP_ID IN (SELECT B.EMP_ID from EMP_SUBSIDIARY B
     where B.EMP_DEPT < ‘D’)

下面是Tosska的树结构执行计划,第一次执行因为缓存延迟需要8.024秒,第二次执行无需缓存延迟只需要3.7秒。

根据执行计划,您可能发现IO消耗最多的是[EMPLOYEE]表的表扫描。为了模拟冷缓存环境,我们可以在每次执行SQL语句前使用DBCC DROPCLEANBUFFERS命令来清除数据缓存。

让我为这条SQL添加OPTION(LOOP JOIN)提示,并尝试将执行计划的哈希匹配更改为嵌套循环连接。因此,将使用[EMPLOYEE]的EMP_ID(EMPLOYEE_PK)和RID查找,而不是使用表扫描。我希望RID查找可以从硬盘中选择较少的数据,同时在[EMPLOYEE]和[EMP_SUBSIDIARY]中匹配EMP_ID。

select *
from   EMPLOYEE A
where  A.EMP_ID in (select  B.EMP_ID
          from   EMP_SUBSIDIARY B
          where  B.EMP_DEPT < ‘D’) OPTION(LOOP JOIN)

根据下面的执行计划,携带数据缓存开销的执行时间从8.024秒减少到1.565秒,物理读也从190,621减少到39,044。如果您用SQL Server的EstimateIO乘以EstimiateExecutions来得到IO估计值,这是错误的。

下面的人工智能调优工具还提供了其它更好的调优解决方案:

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

下面带有提示的SQL语句生成一个更复杂的执行计划,最好的执行时间为0.7秒。该SQL是在冷缓存下进行调优的,每次执行SQL语句之前都会清除缓存数据。

如何调优SQL Server数据库包含EXISTS子查询的SQL语句(一)?

下面示例展示了一个带有Exists子查询的SQL语句。该SQL语句查询DEPARTMENT表中DPT_ID列的值和employee表中emp_dept列的值相等并且employee表中emp_id列的值大于2700000的记录。

SELECT *
  FROM DEPARTMENT
   where exists (select ‘x’
        from employee
        where emp_id > 2700000
        and emp_dept=DPT_ID)

下面是Tosska树结构执行计划,该sql语句执行需要2.23秒才能完成。

该执行计划显示了从[EMPLOYEE].[EMPLOYEE_PK]到[EMPLOYEE].[EMPS_DPT_INX]的两个哈希匹配,然后合并连接到排序的[DEPARTMENT]表。这个执行计划看起来很合理,但是在第一阶段从[EMPLOYEE]表扫描的记录数太多了,我们可以用比较小的[DEPARTMENT]表去扫描[EMPLOYEE]表来改善这条SQL语句。

下面让我将EXISTS子查询改写为IN子查询,但是执行计划并没有按照预期的改变。

select *
from   DEPARTMENT
where DPT_ID in (select  emp_dept
         from    employee
         where  emp_id > 2700000)

我将进一步的改写SQL,并且添加伪函数“isnull(emp_dept,emp_dept)”到查询列表中,但它不能停止哈希匹配到[EMPLOYEE].[EMPS_DPT_INX]的操作。

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)

为了进一步停止“哈希匹配到[EMPLOYEE].[EMPS_DPT_INX]”这一操作,我尝试在子查询中添加“group by emp_dept”伪操作。

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)
         group by emp_dept)

下面是最终改写后的执行计划,SQL Server首先对[DEPARTMENT]进行表扫描和EMPS_DPT_INX索引从[EMPLOYEE]中寻找RID的嵌套循环操作,因此[DEPARTMENT]表中的每条记录最多匹配一次[EMPLOYEE]。现在的执行速度是0.024秒,比原来的SQL快很多。

尽管改写的步骤有点复杂,但它可以由Tosska SQL Tuning Expert for SQL Server自动实现,并且它比原始SQL快了90多倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited