Eu tenho uma consulta que leva uma string json como parâmetro. O json é uma matriz de pares de latitude e longitude. Um exemplo de entrada pode ser o seguinte.
declare @json nvarchar(max)= N'[[40.7592024,-73.9771259],[40.7126492,-74.0120867]
,[41.8662374,-87.6908788],[37.784873,-122.4056546]]';
Ele chama um TVF que calcula o número de POIs em torno de um ponto geográfico, a distâncias de 1,3,5,10 milhas.
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
A intenção da consulta json é chamar essa função em massa. Se eu chamar assim o desempenho é muito ruim levando quase 10 segundos para apenas 4 pontos:
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))
plano = https://www.brentozar.com/pastetheplan/?id=HJDCYd_o4
No entanto, mover a construção da geografia dentro de uma tabela derivada faz com que o desempenho melhore drasticamente, concluindo a consulta em cerca de 1 segundo.
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)
plano = https://www.brentozar.com/pastetheplan/?id=HkSS5_OoE
Os planos parecem praticamente idênticos. Nenhum usa paralelismo e ambos usam o índice espacial. Há um carretel preguiçoso adicional no plano lento que posso eliminar com a dica option(no_performance_spool). Mas o desempenho da consulta não muda. Ainda continua muito mais lento.
A execução de ambos com a dica adicionada em um lote pesará as duas consultas igualmente.
Versão do servidor SQL = Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)
Então minha pergunta é por que isso importa? Como posso saber quando devo calcular valores dentro de uma tabela derivada ou não?
Posso dar uma resposta parcial que explica por que você está vendo a diferença de desempenho - embora isso ainda deixe algumas questões em aberto (como o SQL Server pode produzir o plano mais ideal sem introduzir uma expressão de tabela intermediária que projeta a expressão como uma coluna?)
A diferença é que no plano rápido o trabalho necessário para analisar os elementos da matriz JSON e criar a Geografia é feito 4 vezes (uma vez para cada linha emitida pela
openjsonfunção) - enquanto é feito mais de 100.000 vezes no plano lento.No plano rápido...
É atribuído
Expr1000no escalar de computação à esquerda daopenjsonfunção. Isso corresponde ageosua definição de tabela derivada.No plano rápido, o filtro e a referência agregada de fluxo
Expr1000. No plano lento, eles fazem referência à expressão subjacente completa.Propriedades agregadas de stream
O filtro é executado 116.995 vezes com cada execução exigindo uma avaliação de expressão. A agregação de fluxo tem 110.520 linhas fluindo para ela para agregação e cria três agregações separadas usando essa expressão.
110,520 * 3 + 116,995 = 448,555. Mesmo que cada avaliação individual leve 18 microssegundos, isso adiciona até 8 segundos de tempo adicional para a consulta como um todo.Você pode ver o efeito disso nas estatísticas de tempo real no XML do plano (anotado em vermelho abaixo do plano lento e azul para o plano rápido - os tempos estão em ms)
A agregação de fluxo tem um tempo decorrido 6,209 segundos maior que seu filho imediato. E a maior parte do tempo da criança foi ocupada pelo filtro. Isso corresponde às avaliações de expressão extra.
A propósito.... Em geral, não é certo que expressões subjacentes com rótulos como
Expr1000sejam calculadas apenas uma vez e não reavaliadas, mas claramente, neste caso, pela discrepância de tempo de execução, isso acontece aqui.