主页 / dba / 问题 / 237217
Accepted
Michael B
Asked: 2019-05-03 06:13:46 +0800 CST

为什么这个派生表可以提高性能？

  • 772

我有一个以 json 字符串作为参数的查询。json 是一个纬度、经度对的数组。示例输入可能如下。

declare @json nvarchar(max)= N'[[40.7592024,-73.9771259],[40.7126492,-74.0120867]
,[41.8662374,-87.6908788],[37.784873,-122.4056546]]';

它调用一个 TVF，计算一个地理点周围 1、3、5、10 英里距离的 POI 数量。

create or alter function [dbo].[fn_poi_in_dist](@geo geography)
returns table
with schemabinding as
return 
select count_1  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 1,1,0e))
      ,count_3  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 3,1,0e))
      ,count_5  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 5,1,0e))
      ,count_10 = count(*)
from dbo.point_of_interest
where LatLong.STDistance(@geo) <= 1609.344e * 10

json 查询的目的是批量调用这个函数。如果我这样称呼它，那么性能非常差，只需 4 分就需要将近 10 秒：

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from openjson(@json)
cross apply dbo.fn_poi_in_dist(
            geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326))

计划 = https://www.brentozar.com/pastetheplan/?id=HJDCYd_o4

但是，将地理结构移动到派生表中会导致性能显着提高，大约 1 秒即可完成查询。

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from (
select [key]
      ,geo = geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)
from openjson(@json)
) a
cross apply dbo.fn_poi_in_dist(geo)

计划 = https://www.brentozar.com/pastetheplan/?id=HkSS5_OoE

这些计划看起来几乎相同。既不使用并行性，也都使用空间索引。慢速计划还有一个额外的惰性线轴，我可以通过提示消除它option(no_performance_spool)。但是查询性能没有改变。它仍然慢得多。

使用添加的提示在批处理中运行这两个查询将平等地衡量两个查询。

Sql server 版本 = Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)

所以我的问题是为什么这很重要？我怎么知道何时应该计算派生表中的值？

sql-server sql-server-2016

1 个回答

  1. Best Answer

    我可以给你一个部分答案，解释为什么你会看到性能差异——尽管这仍然留下一些悬而未决的问题（例如SQL Server能否在不引入将表达式投影为列的中间表表达式的情况下生成更优化的计划？）

    不同之处在于，在快速计划中，解析 JSON 数组元素和创建 Geography 所需的工作完成了 4 次（函数发出的每一行一次openjson） - 而在慢速计划中完成了超过 100,000次。

    在快速计划中...

    geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)

    分配给函数Expr1000左侧的计算标量openjson。这对应geo于您的派生表定义。

    在此处输入图像描述

    快速计划中的过滤器和流聚合参考Expr1000。在慢速计划中，它们引用了完整的底层表达式。

    流聚合属性

    在此处输入图像描述

    过滤器执行 116,995 次，每次执行都需要表达式求值。流聚合有 110,520 行流入其中以进行聚合，并使用此表达式创建三个单独的聚合。110,520 * 3 + 116,995 = 448,555. 即使每个单独的评估需要 18 微秒，这也会为整个查询增加 8 秒的额外时间。

    您可以在计划 XML 中的实际时间统计中看到这一点的效果（下面用红色注释，从慢计划和蓝色为快速计划 - 时间以毫秒为单位）

    在此处输入图像描述

    流聚合的经过时间比其直接子节点大 6.209 秒。并且大部分子时间都被过滤器占用了。这对应于额外的表达式评估。

    顺便说一句....一般来说，带有标签的底层表达式只计算一次并且没有重新评估，但在这种情况下，从这里发生的执行时间差异很明显，这是不确定的。Expr1000

    • 15

相关问题