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

Marcelo Michelon

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

Otimização de banco de dados para aplicações mais velozes e estáveis.

NOVOS HORIZONTES PARA O SEU NEGÓCIO

Nosso time está preparado para superar junto com você grandes desafios tecnológicos.

Entre em contato e vamos juntos utilizar a tecnologia do jeito certo para gerar mais resultados.

Insights EximiaCo

Confira os conteúdos de negócios e tecnologia desenvolvidos pelos nossos consultores:

Engenharia de Software

Três vantagens reais de utilizar orquestradores BPM para serviços

Arquiteto de software e solução com larga experiência corporativa
Desenvolvimento de Software

Os principais desafios ao adotar testes

Especialista em Testes e Arquitetura de Software
Arquitetura de Dados

Insights de um DBA na análise de um plano de execução

Especialista em performance de Bancos de Dados de larga escala

Acesse nossos canais

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

EximiaCo 2022 – Todos os direitos reservados

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

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

Para se candidatar nesta turma aberta, preencha o formulário a seguir:

Condição especial de pré-venda: R$ 14.000,00 - contratando a mentoria até até 31/01/2023 e R$ 15.000,00 - contratando a mentoria a partir de 01/02/2023, em até 12x com taxas.

Tenho interesse nessa capacitação

Para solicitar mais informações sobre essa capacitação para a sua empresa, preencha o formulário a seguir:

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.

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ê:

Tenho interesse nessa solução

Se você está procurando este tipo de solução para o seu negócio, preencha este formulário que um de nossos consultores entrará em contato com você:

Tenho interesse neste serviço

Se você está procurando este tipo de solução para o seu negócio, preencha este formulário que um de nossos consultores entrará em contato com você:

× Precisa de ajuda?