Eu tenho uma tabela com 20 milhões de linhas e cada linha tem 3 colunas: time, ide value. Para cada ide time, há um valuepara o status. Eu quero saber os valores de lead e lag de um certo timepara um específico id.
Eu usei dois métodos para conseguir isso. Um método está usando join e outro método está usando as funções de janela lead/lag com índice clusterizado ativado timee id.
Comparei o desempenho desses dois métodos pelo tempo de execução. O método join leva 16,3 segundos e o método window function leva 20 segundos, sem incluir o tempo para criar o índice. Isso me surpreendeu porque a função da janela parece ser avançada enquanto os métodos de junção são força bruta.
Aqui está o código para os dois métodos:
Criar índice
create clustered index id_time
on tab1 (id,time)
Método de junção
select a1.id,a1.time
a1.value as value,
b1.value as value_lag,
c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time
Estatísticas de IO geradas usando SET STATISTICS TIME, IO ON:
Aqui está o plano de execução para o método join
Método de função de janela
select id, time, value,
lag(value,1) over(partition by id order by id,time) as value_lag,
lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1
(Ordenar apenas por timeeconomiza 0,5 segundos.)
Aqui está o plano de execução para o método de função Window
estatísticas de IO
[![Estatísticas para o método de função de janela 4]](https://isstatic.askoverflow.dev/IjuQW.png)
Eu verifiquei os dados sample_orig_month_1999e parece que os dados brutos estão bem ordenados por ide time. Esta é a razão da diferença de desempenho?
Parece que o método join tem mais leituras lógicas do que o método window function, enquanto o tempo de execução para o primeiro é realmente menor. É porque o primeiro tem um paralelismo melhor?
Eu gosto do método de função de janela por causa do código conciso, existe alguma maneira de acelerá-lo para esse problema específico?
Estou usando o SQL Server 2016 no Windows 10 64 bits.
