sql server 2019智能查询_表变量延迟编译特性

问题简介

       SQL Server使用表变量进行表关联操作时，当表变量的值较大，并且关联表的数据量非常庞大时，性能表现并不好，其根源在于生成的执行计划低效。

虽然SQL Server官网上说可以通过重新编译等查询提示来改善，但实际上并没有效果。

       但在SQL Server2019（安装SP1）后，提到了一个智能查询处理的优化，尝试进行相关测试验证，发现效果改善明显。

验证过程

     环境说明

       T_ORG_AUTOALLOCLOGDETAIL_L数据量55423811

       T_ORG_AUTOALLOCLOGDETAIL   数据量840300

测试SQL语句如下（以主键字段作为过滤条件）：

        declare @t as udt_inttable

        insert into @t select top 500000 fpkid from T_ORG_AUTOALLOCLOGDETAIL_L order by FPKID desc

        select * from T_ORG_AUTOALLOCLOGDETAIL_L u

        join T_ORG_AUTOALLOCLOGDETAIL v on u.FPKID=v.FID

        join @t t on t.FID=v.FID

测试过程一

SQL Server 2016执行计划  

SQL Server 2019执行计划

执行计划对比

l  SQL Server 2019下采用自适应联接替代了SQL Server2016下的嵌套循环

l  T_ORG_AUTOALLOCLOGDETAIL在SQL Server 2016下扫描的次数为5000000，而在SQL Server2019下只有1次

l  SQL Server 2016下SQL取数执行的时间为7351毫秒，而在SQL Server 2019下只需要522毫秒

小结

       可以看到IO的减少，让性能提升了14倍。

测试过程二

       上面测试的SQL语句是以主键进行关联，很多时候，实际上并非主键字段关联，所以以非主键进行测试验证，看实际情况如何？

进一步测试，改用其他字段进行比较

       测试语句，使用FDETAILTITLE字段选择50条数据作为过滤条件

declare @t as udt_varchartable

insert into @t

select top 50 fid from (select distinct  FDETAILTITLE as fid from T_ORG_AUTOALLOCLOGDETAIL_L) t order by FID desc

select * from T_ORG_AUTOALLOCLOGDETAIL_L u

join T_ORG_AUTOALLOCLOGDETAIL v on u.FPKID=v.FID

join @t t on t.FID=u.FDETAILTITLE

where u.FLOCALEID=2052

SQL Server 2016执行计划

SQL Server 2019执行计划

执行计划对比

       三个主要变化

l  表变量进行计算标量后，sql server 2016下由50行变成了1行，sql server 2019下，依然是50行。这里说明数据已经出现了差异

l  T_ORG_AUTOALLOCLOGDE