Melhorando (ainda mais) a performance de consultas SQL com abordagens “set-based”

Em um post anterior, demonstramos como melhorar a performance (1400%+) de um procedimento, modificando a abordagem de linha-a-linha (row by row), para outra baseada em blocos (set-based).

O código proposto na solução final era o seguinte:

use tempDB
go

set nocount on

update A set
  A.line = B.line
from dbo.ProcessoDemorado as A
inner join
(
  select 
    A.id,
    A.rowNumber - A.eventChange as line
  from
  (
    select
      A.ID,
      sum(iif(A.typeID not in (1, 2), 0, 1)) over (order by A.orderDate, A.value, A.typeID, A.id rows between unbounded preceding and current row) as eventChange,
      row_number() over(order by A.orderDate, A.value, A.typeID, A.id) as rowNumber
    from dbo.ProcessoDemorado as A
  ) as A
) as B
  on A.ID = B.ID

Entretanto, identificamos outra oportunidade de otimização observando o plano de execução.

Devido ao uso das windows functions (elas só são permitidas nas cláusulas select e order by), se faz obrigatório o uso do join para executar o update.

Esse código habilita o SQL Server para utilizar paralelismo para distribuir o processamento dos dados entre as CPUs, e assim, ganhando ainda mais performance na execução da consulta (sem o paralelismo o código acima demora cerca de 4 segundos no meu ambiente). Porém, nesse plano de execução, chama atenção o custo de dois scans na tabela dbo.ProcessoDemorado, que tem um milhão de linhas, e que poderia ser evitado. Abaixo, demonstramos como contornar essa “limitação” do SQL Server removendo o join obrigatório:

use tempdb
go

set nocount on

;with cteUpdate
as
(
  select
    A.id,
    A.line,
    A.rowNumber - A.eventChange as newLine
  from
  (
    select
      A.ID,
      A.line,
      row_number() over (order by A.orderDate, A.value, A.typeID, A.id) as rowNumber,
      sum(iif(A.typeID not in ( 1, 2 ), 0, 1)) over (order by A.orderDate, A.value, A.typeID, A.id rows between unbounded preceding and current row) as eventChange
    from dbo.ProcessoDemorado as A
  ) as A
)
update A set
  A.line = A.newLine
from cteUpdate as A

O código acima gera um plano de execução com apenas um scan na tabela e sem o hash join do original. Ele ficou mais rápido e utiliza menos CPU que o código original.

A consulta original alocava cerca de 461 MBs, utilizando destes apenas 150 MBs. Com a refatoração do código, apenas 272 MBs são alocados e destes, 131 MBs foram realmente utilizados.

Dica Bônus

Essa mesma técnica pode ser utilizada para resolver outro tipo de problema no SQL Server: Executar um update com order by!

use AdventureWorks2019
go

;with cte
as
(
	select top (50)
		P.ModifiedDate
	from Person.Person as P
	order by P.ModifiedDate desc
)
update cte set cte.ModifiedDate = getdate()

Em resumo

O fato
Otimizar performance no banco de dados obriga analisar planos de execução. Quase sempre, o “segredo” é escrever consultas da forma que leve o banco produzir um plano melhor. Esse tipo de conhecimento demanda prática!
O insight
Quando o assunto é tuning em banco de dados é preciso se ater ao fato de que revisões no código eventualmente devem ser feitas (mesmo em um código que já executa com performance), e que é através dessas pequenas melhorias incrementais que são atingidos os resultados excepcionais. Para isso, aprender a “ler” o plano de execução é essencial. Saber escrever consultas que levem o banco a montar planos eficazes “ganha o jogo”.

Compartilhe este insight:

Comentários

Participe deixando seu comentário sobre este artigo a seguir:

Subscribe
Notify of
guest
0 Comentários
Inline Feedbacks
View all comments

AUTOR

Marcelo Michelon
Mais de 15 anos como Especialista na modelagem, manutenção e otimização de bases de dados e na gestão de servidores com SQL Server

SOLUÇÕES EXIMIACO

Arquitetura de Dados

ESTRATÉGIA & EXECUÇÃO EM TI

Simplificamos, potencializamos 
aceleramos resultados usando a tecnologia do jeito certo.

INSIGHTS EXIMIACO

Confira outros insights de nossos consultores relacionados a esta solução de negócio:

29/04/2021
Marcelo Michelon
Especialista em performance de Bancos de Dados de larga escala
29/04
2021
19/02/2021
19/02
2021

COMO PODEMOS LHE AJUDAR?

Vamos marcar uma conversa para que possamos entender melhor sua situação e juntos avaliar de que forma a tecnologia pode trazer mais resultados para o seu negócio.

COMO PODEMOS LHE AJUDAR?

Vamos marcar uma conversa para que possamos entender melhor sua situação e juntos avaliar de que forma a tecnologia pode trazer mais resultados para o seu negócio.

+55 51 3049-7890 |  [email protected]

0
Queremos saber a sua opinião, deixe seu comentáriox
()
x

Tenho interesse em conversar

Se você está querendo gerar resultados através da tecnologia, preencha este formulário que um de nossos consultores entrará em contato com você:

O seu insight foi excluído com sucesso!

O seu insight foi excluído e não está mais disponível.

O seu insight foi salvo com sucesso!

Ele está na fila de espera, aguardando ser revisado para ter sua publicação programada.