Otimização de funções de janela

A partir do SQL Server 2012 novas funções de janela foram implementadas, várias delas permitindo construir códigos SQL mais eficientes. Mas mesmo assim é necessário ficar atento a como otimizar o processamento das funções de janela e este artigo detalha algumas técnicas.

p041_sumario

1. Introdução

Em outros artigos publicados no Porto SQL foi mencionado como otimizar consultas SQL considerando-se predicados sargable, índices de cobertura, conversão automática de tipos de dados e outras técnicas. Agora, neste artigo, é tratado em como otimizar as funções de janela, que também foram assunto de artigos do Porto SQL.

Para melhor aproveitamento do conteúdo deste artigo é recomendado conhecer como analisar planos de execução. Se estiver em dúvidas, sugiro a leitura do artigo O Plano Perfeito.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

1.1. Cláusula OVER

É através da cláusula OVER que é possível definir o que conterá cada janela na qual será aplicada a função. A sintaxe básica é

p041_figura 1.1a

em que os três elementos possibilitam o particionamento (PARTITION BY), ordenação (ORDER BY) e framing (ROW ou RANGE) do conteúdo da janela.

PARTITION BY. O primeiro elemento

p041_figura 1.1b

divide o conjunto de dados em n partes e a função de janela é então aplicada separadamente a cada partição. Este elemento está disponível em todas as funções de janela.

Por value_expression normalmente se utiliza coluna de tabela que faça parte da cláusula FROM, mas pode também ser uma expressão. Podem ser utilizadas uma ou mais colunas, cujo conteúdo definirá o que ficará em cada janela.

ORDER BY. O segundo elemento

p041_figura 1.1c

possibilita definir a ordem lógica em que as linhas da janela serão manuseadas pela função. O detalhe é que é possível definir o agrupamento (COLLATE) que será utilizado para a ordenação.

ROW ou RANGE. Finalizando, o terceiro elemento

p041_figura 1.1d

é denominado de window framing e é de entendimento complexo, comparando-se com os dois elementos anteriores. Atua como uma subdivisão da janela, limitando as linhas dentro da partição com a especificação de pontos iniciais e finais na partição. Segundo a documentação, “Isso é feito pela especificação de um intervalo de linhas em relação à linha atual por associação lógica ou associação física. (…) A cláusula ROWS limita as linhas dentro de uma partição especificando um número fixo de linhas antes ou depois da linha atual. Alternativamente, a cláusula RANGE limita as linhas logicamente dentro de uma partição especificando um intervalo de valores em relação ao valor na linha atual”.

Detalhando temos

p041_figura 1.1e

A sintaxe completa de window framing encontra-se na documentação da cláusula OVER.

É preciso ficar atento que, se ROWS/RANGE não for especificado mas ORDER BY for especificado, RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como definição da janela. Isso só se aplica a funções que podem aceitar a especificação de ROWS/RANGE como opcional.

1.2. Otimização POC

Funções de janela podem ter processamento eficiente caso exista índice que atenda a determinadas condições. A primeira é que o índice contenha as colunas utilizadas em colunas_partição e colunas_ordenação, na sequência em que estão definidas. Além disso é necessário que no índice constem também as colunas que façam parte do resultado da consulta: no caso de índice agrupado (clustered) todas as colunas estão presentes mas no caso de índice separado (non clustered) é necessário que na cláusula INCLUDE constem as colunas que façam parte do resultado da consulta e que não tenham sido utilizadas nas cláusulas PARTITION BY ou ORDER BY. Em artigo de Itzik Ben-Gan encontrei o acrônimo POC (P, de partição; O, de OVER; C, de colunas de cobertura) para definir tal índice.

Há mais uma condição que está relacionada à clausula WHERE, onde colunas ali presentes também devem fazer parte do índice. Temos então um índice WPOC: W de filtros na cláusula WHERE; P de partição; O de OVER; e C de colunas de cobertura. É claro que isso considerando-se que todas as colunas WPOC estejam na mesma tabela, pois se estiverem em tabelas diferentes os índices serão construídos de forma diferente, podendo ou não serem úteis.

Embora a regra básica para otimizar funções de janela seja simples, nem sempre temos um contexto fácil para aplicá-la. Às vezes em uma mesma consulta são utilizadas duas ou mais funções de janela para uma mesma tabela e com cláusulas OVER diferentes.

1.3. Tabelas para testes

Os testes foram realizados no SQL Server 2016, edição Developer e com o Management Studio 17.9.1. O parâmetro MAXDOP do banco de dados foi ajustado para 1, somente para fins didáticos, ao simplificar os planos de execução gerados. Entretanto, isto não é uma recomendação para ambiente de produção ou para otimização de funções de janela.

Neste artigo uma das tabelas utilizadas é a tbPedido, gerada a partir do conteúdo das tabelas SalesOrderHeader e SalesOrderDetail do banco de dados AdventureWorks. O código SQL de criação desta tabela está no anexo, ao final deste artigo.

A tabela tbPedido possui as seguintes colunas:

p041_figura 1.1f

Essa tabela possui cerca de 120 mil linhas. Para conhecer o conteúdo da tabela tbPedido você pode rodar o seguinte código SQL:

-- código #1.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT ID_Pedido as [N. pedido],
       convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Item as [Item], ID_Produto as Produto, 
       Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido]
  from dbo.tbPedido;

que retorna o seguinte resultado (trecho inicial):

p041_codigo #1.1

O plano de execução do código #1.1 é bem simples:

p041_codigo #1.1 - plano
clique sobre a figura acima para obter zoom

São somente 3 operadores (da direita para a esquerda):

  • Clustered Index Scan: efetua a leitura sequencial da tabela, pelo índice I1_tbPedido;
  • Compute scalar: converte o conteúdo da coluna Data_pedido para ser exibido no formato dd/mm/aaaa;
  • Result showplan (SELECT): exibe as linhas.

É um plano trivial, considerando-se a propriedade “Nível de otimização” do plano de execução.

2. Apresentação dos casos

Para demonstração de algumas técnicas de otimização de funções de janela serão criados 4 casos, com os 3 primeiros com uma única totalização através de função de janela e o último reunindo as totalizações em uma única consulta SQL.

2.1. Calcular total de cada pedido

No código SQL inicial vamos acescentar o cálculo total de cada pedido. Para isto será utilizada a função de janela SUM(), na seguinte forma

sum (Valor_Item) over (partition by ID_Pedido)

A coluna Valor_Item é uma coluna calculada que contém o valor total de cada item. Ao particionar pela coluna ID_Pedido a função de janela somará todas as linhas de um mesmo pedido. O código SQL fica então assim:

-- código #2.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT ID_Pedido as [N. pedido],
       convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Item as [Item], ID_Produto as Produto, Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by ID_Pedido) as [Valor pedido]
  from dbo.tbPedido
  order by ID_Pedido, ID_Item;

que retorna o seguinte resultado (trecho inicial):

p041_codigo #2.1

A diferença em relação ao resultado do código #1.1 é a presença da coluna [Valor pedido], que contém a somatória da coluna [Valor item pedido] de todos os itens de um mesmo pedido. Observe que o valor dela se repete para cada linha de um mesmo pedido.

Temos o seguinte plano execução para o código #2.1:

p041_codigo #2.1 - plano
clique sobre a figura acima para obter zoom

2.2. Calcular soma acumulada de cada item do pedido

No código SQL #2.1 vamos agora acrescentar o somatório acumulado de cada item, por pedido. Para isto novamente será utilizada a função de janela SUM(), mas agora na seguinte forma

sum (Valor_Item) over (partition by ID_Pedido order by ID_Item)

A coluna Valor_Item é uma coluna calculada que contém o valor total de cada item. Ao particionar pela coluna ID_Pedido a função de janela somará todas as linhas de um mesmo pedido e ao definir ordenação pelo conteúdo da coluna ID_Item a função SUM() entende que deve realizar a soma acumulada desde o primeiro item do pedido até o item da linha. Este tipo de cálculo também é conhecido por running totals.

O código SQL fica então assim:

-- código #2.2 v2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT ID_Pedido as [N. pedido],
       convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Item as [Item], ID_Produto as Produto, 
       Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by ID_Pedido 
                              order by ID_Item
                              rows unbounded preceding) as [Soma acumulada item do pedido]
  from dbo.tbPedido
  order by ID_Pedido, ID_Item;

que retorna o seguinte resultado (trecho inicial):

p041_codigo #2.2

Observe que o valor da coluna [Soma acumulada item do pedido] possui valor crescente, para cada pedido.

Para facilitar o impacto do cálculo da soma acumulada, temos o seguinte plano execução do código #2.2:

p041_codigo #2.2 - plano
clique sobre a figura acima para obter zoom

2.3. Calcular total dos pedidos por dia

A terceira coluna a acrescentar é a somatória de pedidos por dia. Para isto novamente será utilizada a função de janela SUM(), mas agora na seguinte forma

sum (Valor_Item) over (partition by Data_Pedido)

A coluna Valor_Item é uma coluna calculada que contém o valor total de cada item. Ao particionar pela coluna Data_Pedido a função de janela somará todas as linhas de uma mesma data; esta é a diferença principal com relação às duas colunas acrescentadas nos itens anteriores.

O código SQL fica então assim:

-- código #2.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Pedido as [N. pedido],
       ID_Item as [Item], ID_Produto as Produto, 
       Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by Data_Pedido) as [Valor total dia]
  from dbo.tbPedido
  order by Data_Pedido, ID_Pedido, ID_Item;

que retorna o seguinte resultado (trecho inicial):

p041_codigo #2.3

Observe que o valor da coluna [Valor total do dia] é o mesmo para todas as linhas que tenham um mesmo valor na coluna [Data pedido].

Temos o seguinte plano execução do código #2.3:

p041_codigo #2.3 - plano
clique sobre a figura acima para obter zoom

2.4. Relatório completo

O último caso é a reunião em um único relatório de todas as acumulações efetuadas nos casos anteriores, com o seguinte código SQL:

-- código #2.4 v2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT convert (char(10), Data_Pedido, 103) as [Data pedido],
       ID_Pedido as [N. pedido], ID_Item as [Item],
       ID_Produto as Produto, Qtd as Quantidade,
       [Preço_Unitário] as [Preço unitário produto],
       Valor_Item as [Valor item pedido],
       sum (Valor_Item) over (partition by ID_Pedido 
                              order by ID_Item
                              rows unbounded preceding) as [Soma acumulada item do pedido],
       sum (Valor_Item) over (partition by ID_Pedido) as [Valor pedido],
       sum (Valor_Item) over (partition by Data_Pedido) as [Valor total dia]
  from dbo.tbPedido
  order by Data_Pedido, ID_Pedido, ID_Item;

que retorna o seguinte resultado (trecho inicial):

p041_codigo #2.4
clique sobre a figura acima para obter zoom

3. Otimização dos casos

Apresentados os quatro casos, o passo seguinte é demonstrar formas de otimização para cada um deles, conforme a regra POC definida no item 1.2 deste artigo.

3.1. Caso “Calcular total de cada pedido”

Temos o seguinte plano execução para o código #2.1:

p041_codigo #2.1 - plano
clique sobre a figura acima para obter zoom

O plano de execução do código #2.1 efetua a leitura sequencial na tabela tbPedido utilizando o índice I1_tbPedido. A seguir o operador Sort ordena as linhas pela coluna ID_Pedido, encaminhando a seguir os dados para o operador Segment, que identifica as linhas pelo conteúdo da coluna ID_Pedido, criando subconjunto de linhas para cada valor dessa coluna. Neste ponto o resultado do operador Segment é armazenado em uma tabela temporária (work table) pelo operador lógico Lazy Spool. O conteúdo dessa tabela temporária é então lido duas vezes: uma para calcular a soma da coluna Valor_Item para cada pedido; outra para obter os dados das linhas. Observe que o cálculo da coluna [Valor pedido] é dividido em 3 sequências paralelas de processamento e que o operador Table Spool (Lazy Spool) está presente, de modo a armazenar resultados intermediários em tabela temporária no banco de dados tempDB. A mesclagem possibilita retornar o resultado da consulta SQL.

A análise da chamada da função de janela SUM()

sum (Valor_Item) over (partition by ID_Pedido)

indica que ela pode ser otimizada se houver índice pela coluna ID_Pedido e que contenha a coluna Valor_Item e também as demais colunas que façam parte da lista de colunas exibidas:

-- código #3.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE unique nonclustered INDEX I2_tbPedido
     on dbo.tbPedido (ID_Pedido, ID_Item)
     include (Valor_Item, Data_Pedido,
              ID_Produto, Qtd, [Preço_Unitário]);

Após gerar o índice I2_tbPedido rodamos novamente o código #2.1 e obtemos o seguinte plano de execução:

p041_codigo #2.1 pos #3.1 - plano
clique sobre a figura acima para obter zoom

Comparando com o primeiro plano de execução do código #2.1 observa-se a ausência do operador Sort, pois as linhas foram lidas sequencialmente e de forma ordenada diretamente no índice I2_tbPedido.

No índice criado no código #3.1 somente seria necessária a coluna ID_Pedido como chave, mas como o código #2.1 ordena o resultado pelo par {ID_Pedido, ID_Item}, foi utilizado então esse par como chave. Neste caso possibilitou o uso da opção UNIQUE na criação do índice.

Trade off. Voltando ao código #3.1, parece que criamos um índice que é tão grande quanto a tabela, pois possui todas as colunas da tabela! Sempre que se cria índice nonclustered é preciso ficar atento que isso representa mais espaço físico utilizado e mais trabalho para o database engine, pois a cada alteração na tabela será necessário manter atualizado também o índice nonclustered criado. É um caso de ganha-perde, pois ganha-se no desempenho ao processar determinadas consultas SQL mas perde-se no trabalho adicional que o database engine terá que realizar.

3.2. Caso “Calcular soma acumulada de cada item do pedido”

Temos o seguinte plano execução do código #2.2:

p041_codigo #2.2 - plano
clique sobre a figura acima para obter zoom

É um plano de execução linear, que que inicia pela leitura sequencial na tabela tbPedido utilizando o índice I1_tbPedido. A seguir o operador Sort ordena as linhas pelas colunas ID_Pedido e ID_Item, encaminhando os dados para a primeira ocorrência do operador Segment, que identifica as linhas pelo conteúdo da coluna ID_Pedido, criando subconjunto de linhas para cada valor dessa coluna. A seguir encaminha para a segunda ocorrência do operador Segment, que identifica as linhas pelo conteúdo das colunas ID_Pedido e ID_Item, criando subconjunto de linhas para cada valor desse par de colunas. E então temos o operador Windows Spool, pois a janela é definida com ORDER BY. O operador seguinte, Stream Aggregate, recebe os dados e efetua o cálculo de soma para cada frame.

A análise da chamada da função de janela SUM()

sum (Valor_Item) over (partition by ID_Pedido order by ID_Item)

indica que ela pode ser otimizada se houver índice pelas colunas ID_Pedido e ID_Item e que contenha a coluna Valor_Item e também as demais colunas que façam parte da lista de colunas exibidas:

-- código #3.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- apaga o índice criado no item anterior
DROP INDEX I2_tbPedido on dbo.tbPedido;
--
CREATE unique nonclustered INDEX I3_tbPedido
     on dbo.tbPedido (ID_Pedido, ID_Item)
     include (Valor_Item, Data_Pedido,
              ID_Produto, Qtd, [Preço_Unitário]);

Após gerar o índice I3_tbPedido rodamos novamente o código #2.2 e obtemos o seguinte plano de execução:

p041_codigo #2.2 pos #3.2 - plano
clique sobre a figura acima para obter zoom

Comparando com o primeiro plano de execução do código #2.2 observa-se a ausência do operador Sort, pois as linhas foram lidas sequencialmente e de forma ordenada diretamente pelo índice I3_tbPedido.

Trade off. Voltando ao código #3.2, parece que novamente criamos um índice que é tão grande quanto a tabela, pois possui todas as colunas da tabela! Sempre que se cria índice nonclustered é preciso ficar atento que isso representa mais espaço físico utilizado e mais trabalho para o database engine, pois a cada alteração na tabela será necessário manter atualizado também o índice nonclustered criado. É um caso de ganha-perde, pois ganha-se no desempenho ao processar determinadas consultas SQL mas perde-se no trabalho adicional que o database engine terá que realizar.

3.3. Caso “Calcular total dos pedidos por dia”

Temos o seguinte plano execução do código #2.3:

p041_codigo #2.3 - plano
clique sobre a figura acima para obter zoom

Estruturalmente é o mesmo plano de execução do código #2.1. Faz sentido, pois o cálculo realizado nos dois códigos é o mesmo, variando somente a coluna que é utilizada para a definição de cada janela: ID_Pedido no código #2.1 e ID_Pedido no código #2.3.

A análise da chamada da função de janela SUM()

sum (Valor_Item) over (partition by Data_Pedido)

indica que ela pode ser otimizada se houver índice pela coluna Data_Pedido e que contenha a coluna Valor_Item e também as demais colunas que façam parte da lista de colunas exibidas:

-- código #3.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- apaga o índice criado no item anterior
DROP INDEX I3_tbPedido on dbo.tbPedido;

--
CREATE unique nonclustered INDEX I4_tbPedido
     on dbo.tbPedido (Data_Pedido, ID_Pedido, ID_Item)
     include (Valor_Item, ID_Produto, Qtd, [Preço_Unitário]);

Após gerar o índice I3_tbPedido rodamos novamente o código #2.3 e obtemos o seguinte plano de execução:

p041_codigo #2.3 pos #3.3 - plano
clique sobre a figura acima para obter zoom

Comparando com o primeiro plano de execução do código #2.3, observa-se a ausência do operador Sort, pois as linhas foram lidas sequencialmente e de forma ordenada diretamente pelo índice I4_tbPedido.

No índice criado no código #3.3 somente seria necessária a coluna Data_Pedido como chave, mas como o código #2.3 ordena o resultado pelo trio {Data_Pedido, ID_Pedido, ID_Item}, foi utilizado então esse trio como chave. Neste caso possibilitou o uso da opção UNIQUE na criação do índice.

Trade off. Voltando ao código #3.3, parece que novamente criamos um índice que é tão grande quanto a tabela, pois possui todas as colunas da tabela! Sempre que se cria índice nonclustered é preciso ficar atento que isso representa mais espaço físico utilizado e mais trabalho para o database engine, pois a cada alteração na tabela será necessário manter atualizado também o índice nonclustered criado. É um caso de ganha-perde, pois ganha-se no desempenho ao processar determinadas consultas SQL mas perde-se no trabalho adicional que o database engine terá que realizar.

Para retornar à situação original, vamos apagar os índices criados neste artigo:

-- código #3.4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

DROP INDEX if exists I2_tbPedido on dbo.tbPedido;
DROP INDEX if exists I3_tbPedido on dbo.tbPedido;
DROP INDEX if exists I4_tbPedido on dbo.tbPedido;

3.4. Caso “Relatório completo”

O plano de execução do código #2.4, que emite o relatório completo, é o seguinte:

p041_codigo #2.4 - plano
clique sobre a figura acima para obter zoom

Na imagem acima estão demarcados os operadores Sort, utilizados em dois pontos diferentes do plano de execução. O primeiro, à esquerda do operador Custered Index Scan, ordena as linhas pelas colunas ID_Pedido e Id_item; o segundo operador, quinto da direita para a esquerda, ordena as linhas pelas colunas Data_Pedido, ID_Pedido e Id_item.

Observe que em um dos operadores Sort há um triângulo amarelo com o caractere ! e com a seguinte mensagem de alerta: Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s); Sort wrote 972 pages to and read 972 pages from tempdb with granted memory 16240KB and used memory 16240KB. A causa desse caso e como solucioná-lo não serão tratados neste artigo, por não estar diretamente relacionado ao objetivo deste artigo. Entretanto, é um alerta que deve ser considerado.

Para o relatório completo temos 3 totalizações através de função de janela:

sum (Valor_Item) over (partition by ID_Pedido)

e

sum (Valor_Item) over (partition by ID_Pedido order by ID_Item)

e

sum (Valor_Item) over (partition by Data_Pedido)

Ao otimizar a segunda condição automaticamente a primeira também fica otimizada, pois a definição da janela de ambas é a mesma, pela coluna ID_Pedido. O índice POC que atende à segunda totalização também atende à primeira totalização. Com relação à terceira totalização, a definição de janela utiliza outra coluna, Data_Pedido, o que demanda índice diferente. O que ocorrerá se criarmos 2 índices, de modo que todas as totalizações sejam atendidas?

-- código #3.5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- totalizações 1 e 2
CREATE unique nonclustered INDEX I3_tbPedido
     on dbo.tb Pedido (ID_Pedido, ID_Item)
     include (Valor_Item, Data_Pedido,
              ID_Produto, Qtd, [Preço_Unitário]);

-- totalização 3
CREATE unique nonclustered INDEX I4_tbPedido
     on dbo.tbPedido (Data_Pedido, ID_Pedido, ID_Item)
     include (Valor_Item, ID_Produto, Qtd, [Preço_Unitário]);

Trade off. Sempre que se cria índice nonclustered é preciso ficar atento que isso representa mais espaço físico utilizado e mais trabalho para o database engine, pois a cada alteração na tabela será necessário manter atualizado também o índice nonclustered criado. É um caso de ganha-perde, pois ganha-se no desempenho ao processar determinadas consultas SQL mas perde-se no trabalho adicional que o database engine terá que realizar.

Após criarmos os índices definidos no código #3.5, ao rodar novamente o código #2.4 temos então o seguinte plano de execução:

p041_codigo #2.4 pos #3.5 - plano
clique sobre a figura acima para obter zoom

Um dos operadores Sort foi eliminado, pois a leitura passou a ser pelo índice I3_tbPedido, que fornece as linhas já ordenadas pelas colunas ID_Pedido e ID_Item. Quanto ao índice I4_tbPedido, foi ignorado. Ou seja, ele não é necessário para este contexto.

Há uma forma de eliminar por completo os operadores Sort do plano de execução do código #2.4 e é através do processo de reescrever o código SQL da consulta. Se quebrarmos a consulta em duas subconsultas, com a primeira subconsulta SQL atendendo às duas primeiras totalizações (que são pela coluna ID_Pedido); e com a segunda subconsulta SQL atendendo à terceira totalização (que é pela coluna Data_Pedido), podemos fazer com que os dois índices criados no código #3.5 sejam utilizados.

-- código #3.6 v2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

with
Consulta_1 as (
SELECT ID_Pedido, ID_Item, ID_Produto, Qtd, 
       [Preço_Unitário], Valor_Item,
       sum (Valor_Item) over (partition by ID_Pedido 
                              order by ID_Item
                              rows unbounded preceding) as somaPedidoItem,
       sum (Valor_Item) over (partition by ID_Pedido) as somaPedido
  from dbo.tbPedido
),
Consulta_2 as (
SELECT Data_Pedido, ID_Pedido, ID_Item,
       sum (Valor_Item) over (partition by Data_Pedido) as somaDia
  from dbo.tbPedido
)
SELECT C2.Data_Pedido as [Data pedido],
       C2.ID_Pedido as [N. pedido], C2.ID_Item as [Item],
       C1.ID_Produto as Produto, C1.Qtd as Quantidade,
       C1.[Preço_Unitário] as [Preço unitário produto],
       C1.Valor_Item as [Valor item pedido],
       C1.somaPedidoItem as [Soma acumulada item do pedido],
       C1.somaPedido as [Valor pedido],
       C2.somaDia as [Valor total dia]
  from Consulta_1 as C1
       inner join Consulta_2 as C2 
          on C2.ID_Pedido = C1.ID_Pedido and C2.ID_Item = C1.ID_Item;

Aliás, o índice para atender à segunda subconsulta pode ser simplificado, pois ela retorna menos colunas. Podemos ter algo como

-- código #3.7
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

DROP INDEX if exists I4_tbPedido on dbo.tbPedido;

CREATE unique nonclustered INDEX I4_tbPedido
     on dbo.tbPedido (Data_Pedido, ID_Pedido, ID_Item)
     include (Valor_Item);

Isto faz com que o índice I4_tbPedido  ocupe menos espaço físico, além de otimizar a navegação nele, pois haverão mais linhas em uma mesma página.

Após redefinir o índice e rodar o código #3.6, temos o seguinte plano de execução:

p041_codigo #3.6 - plano
clique sobre a figura acima para obter zoom

Os dois índices foram utilizados para a leitura e operador Sort não foi necessário.

3.5. Observações

Nos três exemplos iniciais deste capítulo foi demonstrado como utilizar a abordagem “índice POC” para melhorar o desempenho de consultas SQL que utilizem funções de janela. No último exemplo, caso “relatório completo”, o código SQL foi reescrito para tirar proveito da abordagem POC. Como destacado ao longo do capítulo, há ganhos e perdas na criação de índices adicionais.

4. Finalizando…

4.1. Observações

Neste artigo estão descritas duas técnicas para otimização de funções de janelas; se há vantagem ou não na utilização delas depende do contexto, mas sempre é bom ter trunfo na manga para utilizar quando possível.

Cabe repetir que os testes foram efetuados com o banco de dados configurado com maxdop 1 para gerar planos de execução sem paralelismo, o que facilita a apresentação e explicação dos mesmos. Entretanto, em uma situação de otimização deve-se avaliar vários contextos.

Ao longo do artigo foram apresentados vários planos de execução para demonstrar o impacto das opções apresentadas. Cada plano de execução possui uma riqueza de detalhes quando se consulta as propriedades de cada um dos operadores. Infelizmente não foi possível descrever tudo o que foi observado durante os testes, pois tornaria este artigo muito longo.

4.2. Material de estudos

5.2.1. Artigos e vídeos

5.2.2. Livros

5. Anexo

5.1. Criação e carga da tabela tbPedido

A tabela tbPedido foi gerada a partir do conteúdo das tabelas Sales.SalesOrderHeader e Sales.SalesOrderDetail, do banco de dados Adventure Works.

-- código #5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

USE testdb;
drop table tbPedido;

SELECT identity(int) as Chave,
       O.SalesOrderID as ID_Pedido, cast (0 as tinyint) as ID_Item,
       cast (O.OrderDate as date) as Data_Pedido,
       OD.ProductID as ID_Produto, OD.OrderQty as Qtd,
       cast (round (OD.UnitPrice, 2) as decimal(9,2)) as [Preço_Unitário],
       cast ((OD.OrderQty * round (OD.UnitPrice, 2)) as decimal(9,2)) as Valor_Item
  into tbPedido
  from AdventureWorks.Sales.SalesOrderHeader as O
       inner join AdventureWorks.Sales.SalesOrderDetail as OD on OD.SalesOrderID = O.SalesOrderID
  order by O.OrderDate, O.SalesOrderID, OD.SalesOrderDetailID;

--
with Sequenciado as (
SELECT Chave, [ID_Pedido], ID_Item,
        row_number() over (partition by [ID_Pedido] order by Chave) as Seq
  from tbPedido
)
UPDATE Sequenciado
  set ID_Item= Seq;

--
ALTER TABLE tbPedido ADD constraint I1_TbPedido primary key (Chave);

-- cast() gerou colunas que permitem null
ALTER TABLE tbPedido ALTER COLUMN ID_Item tinyint not null;
ALTER TABLE tbPedido ALTER COLUMN Data_Pedido date not null;
ALTER TABLE tbPedido ALTER COLUMN [Preço_Unitário] decimal(9,2) not null;
ALTER TABLE tbPedido ALTER COLUMN Valor_Item decimal(9,2) not null;

 


 

PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Otimização de funções de janela

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s