Redgate Hub (Português)
T-SQL window functions make writing many queries e often provide better performance as well over older techniques. Por exemplo, usar a função de LAG é muito melhor do que fazer uma auto-adesão. Para obter um melhor desempenho em geral, no entanto, você precisa entender o conceito de enquadramento e como as funções da janela dependem da triagem para fornecer os resultados.nota: veja meu novo artigo para saber como melhorias no otimizador em 2019 afetam o desempenho!,
a cláusula OVER e a ordenação
Existem duas opções na cláusula OVER que podem causar ordenação: partição por e ordem por. A partição Por é suportada por todas as funções da janela, mas é opcional. A ordem Por é necessária para a maioria das funções. Dependendo do que você está tentando realizar, os dados serão ordenados com base na cláusula OVER, e que pode ser o gargalo de desempenho de sua consulta.
A ordem por opção na cláusula sobre é necessária para que o motor de banco de dados possa alinhar as linhas, por assim dizer, a fim de aplicar a função na ordem correta., Por exemplo, diga que deseja que a função ROW_NUMBER seja aplicada por ordem do SalesOrderID. Os resultados serão diferentes do que se você quiser a função aplicada por ordem de TotalDue em ordem descendente., Aqui está o exemplo:
|
1
2
3
4
5
6
7
8
9
10
11
|
USAR AdventureWorks2017; –ou qualquer versão você tem
IR
SELECIONE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(FIM POR SalesOrderID) COMO RowNum
a PARTIR de Vendas.,SalesOrderHeader;
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ordem por TotalDue DESC) AS RowNum
das vendas.SalesOrderHeader;
|
Desde a primeira consulta é usando a chave de cluster, conforme a ORDEM POR opção, nenhuma classificação é necessário.
a segunda consulta tem uma operação de ordenação dispendiosa.,
a ordem na cláusula OVER Não está ligada à ordem por cláusula adicionada à consulta global que poderia ser completamente diferente., Here is an example showing what happens if the two are different:
|
1
2
3
4
5
|
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
FROM Sales.,SalesOrderHeader
ORDER BY SalesOrderID;
|
A chave de índice agrupado é SalesOrderID, mas as linhas deve primeiro ser classificados por TotalDue em ordem decrescente e, em seguida, de volta para SalesOrderID. Dê uma olhada no plano de execução:
a partição por cláusula, suportada mas opcional, para todas as funções da janela T-SQL também causa ordenação. É semelhante, mas não exatamente como, ao grupo por cláusula para consultas agregadas., This example starts the row numbers over for each customer.
|
1
2
3
4
5
6
|
SELECT CustomerID,
SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RowNum
FROM Sales.,SalesOrderHeader;
|
O plano de execução mostra apenas uma operação de classificação, uma combinação de CustomerID e SalesOrderID.
a única maneira de superar o impacto de desempenho da ordenação é criar um índice específico para a cláusula sobre. In his book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, Itzik Ben-Gan recommends the POC index. POC significa (P)ARTITION BY, (o)RDER BY, E (c)overing., Ele recomenda a adição de quaisquer colunas usadas para filtrar antes da partição por e ordem por colunas na chave. Em seguida, adicionar quaisquer colunas adicionais necessárias para criar um índice de cobertura como colunas incluídas. Assim como qualquer outra coisa, você vai precisar testar para ver como tal índice impacta sua consulta e carga de trabalho global. Claro, você não pode adicionar um índice para cada consulta que você escreve, mas se o desempenho de uma consulta que utiliza uma função de janela é importante, você pode experimentar este conselho.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUIR (TotalDue);
|
Quando você executar a consulta novamente, a operação de ordenação agora se foi o plano de execução:
Enquadramento
Na minha opinião, o enquadramento é o mais conceito difícil de entender quando se aprende sobre T-SQL janela de funções. Para saber mais sobre a sintaxe, veja a introdução às funções da janela T-SQL., O enquadramento é necessário para o seguinte:
- Janela de agregados com a ORDEM, utilizado para a execução de totais ou médias móveis, por exemplo
- FIRST_VALUE
- LAST_VALUE
Felizmente, o enquadramento não é obrigatório a maior parte do tempo, mas, infelizmente, é fácil ignorar o quadro e use o padrão. A moldura padrão é sempre intervalo entre a linha anterior sem limites e a linha atual. Enquanto você vai obter os resultados corretos, desde que a ordem por opção consiste em uma coluna única ou conjunto de colunas, você vai ver um sucesso de desempenho.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET STATISTICS e / s;
IR
SELECIONE Códigodocliente,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY Códigodocliente ORDEM SalesOrderID)
COMO RunningTotal
a PARTIR de Vendas.,SalesOrderHeader;
SELECIONE Códigodocliente,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY Códigodocliente ORDEM SalesOrderID
LINHAS ENTRE UNBOUNDED ANTERIOR E a ATUAL LINHA)
COMO RunningTotal
a PARTIR de Vendas.SalesOrderHeader;
|
os resultados são Os mesmos, mas o desempenho é muito diferente. Infelizmente, o plano de execução não lhe diz a verdade neste caso., Ele relata que cada consulta demorou 50% dos recursos:
Se você analisar as estatísticas de e / s os valores, você vai ver a diferença:
Usando a corrigir o quadro é ainda mais importante se o seu PEDIDO ATRAVÉS da opção não é exclusiva, ou se você estiver usando LAST_VALUE. Neste exemplo, a ordem por coluna é OrderDate, mas alguns Clientes colocaram mais de uma ordem em uma determinada data. Ao não especificar a moldura, ou usando o intervalo, a função trata datas correspondentes como parte da mesma janela.,”>
a razão para A discrepância é que o ALCANCE vê os dados logicamente, enquanto LINHAS de vê-lo posicionalmente. Há duas soluções para este problema. Uma é garantir que a ordem por opção é única. A outra opção mais importante é sempre especificar a moldura onde é suportada.
O outro lugar que o enquadramento causa problemas lógicos é com LAST_VALUE., O LAST_ value devolve uma expressão da última linha da moldura. Uma vez que o quadro padrão (intervalo entre a linha anterior sem limites e a linha atual) só vai até a linha atual, a última linha do quadro é a linha onde o cálculo está sendo realizado.,
Janela de Agregados
Uma das mais práticas recurso de T-SQL funções da janela é a capacidade de adicionar uma expressão de agregação para um não-consulta agregada. Infelizmente, isto pode muitas vezes ter um mau desempenho. Para ver o problema, você precisa olhar para os resultados das estatísticas IO onde você verá um grande número de leituras lógicas., Meu conselho quando você precisa retornar valores em granularidades diferentes dentro da mesma consulta para um grande número de linhas é usar uma das técnicas mais antigas, como uma expressão de tabela comum (CTE), tabela temp, ou mesmo uma variável. Se é possível pré-agregar antes de usar o agregado da janela, essa é outra opção.,ows a diferença entre a janela de agregados e outra técnica:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECIONE SalesOrderID,
TotalDue,
SUM(TotalDue) MAIS() COMO OverallTotal
a PARTIR de Vendas.,SalesOrderHeader
WHERE YEAR(OrderDate) =2013;
DECLARE @OverallTotal MONEY;
selecione @OverallTotal = SUM(TotalDue)
das vendas.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013;
SELECT SalesOrderID,
TotalDue, @OverallTotal AS OverallTotal
das vendas.,SalesOrderHeader COMO SOH
ONDE o ANO(Datadopedido) = 2013;
|
A primeira consulta apenas verifica a tabela de uma só vez, mas tem 28,823 leituras lógicas em uma tabela de trabalho. O segundo método verifica a mesa duas vezes, mas não precisa da mesa de trabalho.
O próximo exemplo usa um agregado do windows aplicado a uma expressão agregada:
ao usar as funções da janela numa consulta agregada, a expressão deve seguir as mesmas regras que a seleção e a ordem pelas cláusulas., Neste caso, a função janela é aplicada à soma (TotalDue). Parece um agregado aninhado, mas é realmente uma função de janela aplicada a uma expressão agregada.
Uma vez que os dados foram agregados antes da função da janela ser aplicada, o desempenho é bom:
há mais uma coisa interessante a saber sobre a utilização de agregados da janela. Se você usar várias expressões que usam correspondência sobre definições de cláusulas, você não verá uma degradação adicional no desempenho.
O meu conselho é usar esta funcionalidade com precaução., É bastante útil, mas não é assim tão grande.
comparações de desempenho
os exemplos apresentados até agora têm usado as pequenas vendas.Salesorderhead table da AdventureWorks e revisou os planos de execução e leituras lógicas. Na vida real, seus clientes não se importarão com o plano de execução ou as leituras lógicas; eles se importarão com a rapidez com que as consultas correm. Para ver melhor a diferença nos tempos de execução, usei o roteiro de Adam Machanic pensando em grande (aventura) com uma reviravolta.
a escrita cria uma tabela chamada bigTransactionHistory contendo mais de 30 milhões de linhas., Depois de executar o roteiro de Adam, eu criei mais duas cópias de sua mesa, com 15 e 7,5 milhões de linhas, respectivamente. Eu também ativei os resultados descartados após a propriedade de execução no editor de Consulta de modo que a população da grade não afetou os tempos de execução. Fiz cada teste três vezes e limpei o buffer antes de cada corrida.,
I can’t say enough about how important it is to use the frame when it’s supported., Para ver a diferença, eu corri um teste para calcular totais em execução usando quatro métodos:
- Cursor solução
- Correlacionada sub-consulta
- função de Janela com o padrão de quadros
- função de Janela com LINHAS
executei o teste em três novas tabelas. Aqui estão os resultados em um formato de gráfico:
ao executar com a moldura de linhas, a tabela de 7,5 milhões de linhas levou menos de um segundo para executar no sistema que eu estava usando ao executar o teste. A mesa de 30 milhões levou cerca de um minuto para correr., id=”4b64f77d3b”>
|
1
2
3
4
5
|
SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID
ORDER BY TransactionDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM bigTransactionHistory;
|
I also performed a test to see how window aggregates performed compared to traditional techniques., Neste caso, usei apenas a tabela de 30 milhões de linhas, mas realizei um, dois ou três cálculos usando a mesma granularidade e, portanto, a mesma cláusula OVER. Comparei o desempenho agregado da janela a um CTE e a um subconjunto correlacionado.
o agregado da janela realizou o pior, cerca de 1,75 minutos em cada caso. O CTE realizou o melhor ao aumentar o número de cálculos, uma vez que a tabela foi tocada apenas uma vez para todos os três., O subquery correlacionado funcionou pior quando aumentou o número de cálculos desde que cada cálculo teve que correr separadamente, e tocou a tabela um total de quatro vezes.,v>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH Calcs AS (
SELECT ProductID,
AVG(ActualCost) AS AvgCost,
MIN(ActualCost) AS MinCost,
MAX(ActualCost) AS MaxCost
FROM bigTransactionHistory
GROUP BY ProductID)
SELECT O.,ProductID,
ActualCost,
AvgCost,
MinCost,
MaxCost
a PARTIR de bigTransactionHistory COMO O
JUNTAR Calques EM O. ProductID = Calques.ProductID;
|
Conclusão
T-SQL janela de funções têm sido promovidos como sendo ótimo para o desempenho. Na minha opinião, eles tornam as consultas de escrita mais fácil, mas você precisa compreendê-los bem para obter um bom desempenho. Indexação pode fazer a diferença, mas você não pode criar um índice para cada consulta que você escreve., Enquadramento pode não ser fácil de entender, mas é tão importante se você precisa escalar até tabelas grandes.