Início / dba / Perguntas / 158374
Accepted
Jason
Asked: 2016-12-17 06:54:09 +0800 CST

Comparação de desempenho entre o uso da função Join e Window para obter valores de lead e lag

  • 772

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:

Estatísticas para método de junção

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]

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.

sql-server performance

1 respostas

  1. Best Answer

    O desempenho relativamente baixo do modo de linha LEADe das LAGfunções de janela em comparação com autojunções não é novidade. Por exemplo, Michael Zilberstein escreveu sobre isso no SQLblog.com em 2012. Há um pouco de sobrecarga nos operadores de plano Segment (repetido), Sequence Project, Window Spool e Stream Aggregate:

    Seção do plano

    No SQL Server 2016, você tem uma nova opção, que é habilitar o processamento em lote para os agregados de janela. Isso requer algum tipo de índice columnstore na tabela, mesmo que esteja vazio. Atualmente, a presença de um índice columnstore é necessária para que o otimizador considere planos de modo em lote. Em particular, ele habilita o operador de modo de lote Window Aggregate muito mais eficiente.

    Para testar isso no seu caso, crie um índice columnstore não clusterizado vazio:

     -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON dbo.tab1 (id, [time], [value]) 
WHERE id < 0 AND id > 0;

    A pergunta:

    SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag = 
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

    Agora deve dar um plano de execução como:

    Plano de armazenamento de linha em modo de lote

    ...que pode muito bem ser executado muito mais rápido.

    Você pode precisar usar uma OPTION (MAXDOP 1)ou outra dica para obter a mesma forma de plano ao armazenar os resultados em uma nova tabela. A versão paralela do plano requer uma classificação em lote (ou possivelmente duas), que pode ser um pouco mais lenta. Depende bastante do seu hardware.

    Para saber mais sobre o operador Batch Mode Window Aggregate, consulte os seguintes artigos de Itzik Ben-Gan:

    • 15

relate perguntas