json 문자열을 매개 변수로 사용하는 쿼리가 있습니다. json은 위도, 경도 쌍의 배열입니다. 입력 예는 다음과 같습니다.

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

1,3,5,10 마일 거리의 지리적 지점 주변의 POI 수를 계산하는 TVF를 호출합니다.

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)

plan = 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)

내 질문은 왜 이것이 중요합니까? 파생 테이블 내에서 값을 계산해야하는 시점을 어떻게 알 수 있습니까?

답변

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