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.
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 é
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
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
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
é 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
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:
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):
O plano de execução do código #1.1 é bem simples:
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):
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:
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):
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:
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):
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:
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):
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:
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:
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:
É 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:
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:
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:
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:
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:
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:
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
- O Plano Perfeito
- T-SQL Window Functions (palestra de Itzik Ben-Gan)
5.2.2. Livros
- T-SQL Window Functions (capítulo 5, de Itzik Ben-Gan)
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;
| 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. |
