Dlaczego ta tabela pochodna poprawia wydajność?

Mam zapytanie, które bierze ciąg json jako parametr. Json to tablica par szerokości i długości geograficznej. Przykładowe dane wejściowe mogą być następujące.

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

Wzywa TVF, który oblicza liczbę punktów POI w pobliżu punktu geograficznego, w odległości 1,3,5,10 mil.

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

Celem zapytania json jest zbiorcze wywołanie tej funkcji. Jeśli nazywam to w ten sposób, wydajność jest bardzo niska i zajmuje prawie 10 sekund za zaledwie 4 punkty:

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

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

Jednak przesunięcie konstrukcji geograficznej wewnątrz tabeli pochodnej powoduje znaczną poprawę wydajności, wypełniając zapytanie w około 1 sekundę.

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

Plany wyglądają praktycznie identycznie. Żadne z nich nie używa równoległości i oba wykorzystują indeks przestrzenny. Na wolnym planie jest dodatkowa leniwa szpula, którą mogę wyeliminować za pomocą podpowiedzi option(no_performance_spool). Ale wydajność zapytania nie zmienia się. Nadal pozostaje znacznie wolniejszy.

Uruchomienie obu z dodaną wskazówką w partii spowoduje równe ważenie obu zapytań.

Wersja serwera SQL = Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)

Więc moje pytanie brzmi: dlaczego to ma znaczenie? Skąd mam wiedzieć, kiedy powinienem obliczyć wartości w tabeli pochodnej, czy nie?

Mogę dać ci częściową odpowiedź, która wyjaśnia, dlaczego widzisz różnicę w wydajności - choć wciąż pozostawia to kilka otwartych pytań (na przykład czy SQL Server może stworzyć bardziej optymalny plan bez wprowadzania wyrażenia tabeli pośredniej, która wyświetla to wyrażenie jako kolumnę?)

Różnica polega na tym, że w szybkim planie praca potrzebna do parsowania elementów tablicy JSON i utworzenia geografii jest wykonywana 4 razy (raz dla każdego wiersza emitowanego z openjsonfunkcji) - podczas gdy jest wykonywana ponad 100 000 razy w porównaniu z wolnym planem.

W szybkim planie ...

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

Jest przypisany do Expr1000skalaru obliczeniowego po lewej stronie openjsonfunkcji. To odpowiada geodefinicji tabeli pochodnej.

W szybkim planie odwołanie do filtru i strumienia agreguje odniesienie Expr1000. W wolnym planie odnoszą się do pełnego wyrażenia leżącego u podstaw.

Strumień właściwości agregujących

Filtr jest wykonywany 116 995 razy, a każde wykonanie wymaga oceny wyrażenia. Agregacja strumienia ma 110.520 wierszy wpływających do niego w celu agregacji i tworzy trzy oddzielne agregaty za pomocą tego wyrażenia. 110,520 * 3 + 116,995 = 448,555. Nawet jeśli każda indywidualna ocena zajmuje 18 mikrosekund, daje to do 8 sekund dodatkowego czasu na zapytanie jako całość.

Możesz zobaczyć efekt tego w statystykach czasu rzeczywistego w XML planu (adnotacje na czerwono poniżej z wolnego planu i niebieskie dla szybkiego planu - czasy w ms)

Agregacja strumienia ma czas, który upłynął o 6,209 sekundy dłuższy niż jego bezpośrednie dziecko. I większość czasu dziecięcego została zajęta przez filtr. Odpowiada to dodatkowym ocenom wyrażeń.

Nawiasem mówiąc ... Ogólnie rzecz biorąc, nie jest pewne, czy wyrażenia leżące u podstaw takich etykiet Expr1000są obliczane tylko raz i nie są ponownie oceniane, ale w tym przypadku wyraźnie z rozbieżności czasu wykonania, co się tutaj dzieje.