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()

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

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

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
Arquitetura de Dados

A realidade do Arquiteto de Dados dentro das organizações

Especialista em performance de Bancos de Dados de larga escala
Arquitetura de Dados

LGPD: Protegendo as informações no Banco de Dados

Especialista em performance de Bancos de Dados de larga escala
EximiaCo 2024 - Todos os direitos reservados
0
Queremos saber a sua opinião, deixe seu comentáriox
()
x
Oferta de pré-venda!

Mentoria em
Arquitetura de Software

Práticas, padrões & técnicas para Arquitetura de Software, de maneira efetiva, com base em cenários reais para profissionais envolvidos no projeto e implantação de software.

Muito obrigado!

Deu tudo certo com seu envio!
Logo entraremos em contato

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

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

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?