Melhorando a performance de consultas SQL em mais de 1400% com abordagens “set-based”

Utilizar os recursos certos, do jeito certo, de qualquer tecnologia é determinante para a boa performance de uma aplicação. Tratando-se de bancos de dados, essa constatação costuma ser ainda mais perceptível.

Muitas vezes, abordagens ingênuas resultam em problemas de performance que tornam a utilização das aplicações insustentável. Não raro, a “culpa” recai sobre o banco de dados ou no volume de informações. Geralmente, o problema é puro desconhecimento.

O cenário aqui compartilhado, por exemplo, é inspirado em um caso real. Fomos acionados para otimizar uma consulta que demandava 12 horas de processamento. Após a revisão, os mesmos resultados foram gerados em apenas 5 minutos (otimização de 144 vezes).

Setup para simulação do problema

Não será reproduzido aqui, evidentemente, o modelo do banco de dados, tampouco a consulta que foi otimizada. Em seu lugar será criado um modelo mais simples e então realizado uma carga de dados suficiente para evidenciar o problema que foi solucionado.

O script abaixo cria uma tabela com um milhão de linhas no banco de dados tempDB:

use tempDB
go
drop table if exists dbo.ProcessoDemorado

create table dbo.ProcessoDemorado
(
  id int not null,
  typeID int not null,
  orderDate datetime not null,
  value numeric(18, 2) not null,
  line int null
)

insert into dbo.ProcessoDemorado
select
  A.n as id,
  1.0 + floor(5 * rand(convert(varbinary, newid()))) as typeID,
  isnull(convert(date, getdate() - (checksum(newid()) / 1000000)), getdate()) as orderDate,
isnull(abs(convert(numeric(18,2), (checksum(newid()) / 1000000.5))), 0) as value,
  null as line
from master.dbo.GetNums(1, 1000000) as A

alter table dbo.ProcessoDemorado
  add constraint PK_ProcessoDemorado_id primary key (id)

Para auxiliar na geração das linhas, foi utilizado a função GetNums, proposta por Itzik Ben-Gan.

use master
go

create or alter function dbo.GetNums(@low as bigint = 1, @high as bigint)
returns table
as
  return
    with
      L0 as (select 1 as c from (values (1),(1),(1),(1),(1),(1),(1),(1), (1),(1),(1),(1),(1),(1),(1),(1)) as D(c)),
      L1 as (select 1 as c from L0 as A cross join L0 as B),
      L2 as (select 1 as c from L1 as A cross join L1 as B),
      L3 as (select 1 as c from L2 as A cross join L2 as B),
      Nums as (select row_number() over (order by (select null)) as rownum from L3)
  select top (@high - @low + 1)
    rownum AS rn,
    @high + 1 - rownum AS op,
    @low - 1 + rownum AS n
  from Nums
  order by rownum;

O problema

O código de exemplo que segue é uma reprodução de um padrão recorrente que identificamos na consulta que foi otimizada: o uso ineficiente de cursores. Em nosso ambiente, consumia 25 segundos para sua execução.

Repare que a tabela é percorrida utilizando-se um cursor. Toda vez que um registro atende uma condição, um contador é incrementado e seu valor atual é persistido no respectivo registro.

use tempDB
go

set nocount on

declare @line int = 0;
declare @id int = null
declare @typeID int = null

declare cursorProcesso cursor
for
   select
     A.id,
     A.typeID
   from dbo.ProcessoDemorado as A
   order by A.orderDate, A.value, A.typeID, A.id

open cursorProcesso

fetch cursorProcesso into @id, @typeID

while @@fetch_status = 0
begin

   if @typeID not in (1, 2) set @line = @line + 1
   update dbo.ProcessoDemorado set line = @line where id = @id

   fetch cursorProcesso into @id, @typeID

end

close cursorProcesso
deallocate cursorProcesso

O problema aqui não é infraestrutura subdimensionada, tampouco índices mal definidos. De fato, a “dor” é a adoção de uma feature e abordagem inadequada para a implementação desse tipo de solução.

O código procedural, exposto no exemplo, seria razoável rodando no servidor da aplicação, escrito em uma linguagem como C#. Entretanto, é ineficiente em um banco de dados, onde é mais apropriado tratar o dado em blocos (set-based)

Melhorando a performance de cursores

Nossa recomendação padrão é evitar utilizar cursores. Entretanto, se eles forem indispensáveis, considere declará-los como fast_forward.

declare cursorProcesso cursor local fast_forward

Declarar o cursor assim, não faz milagres, mas pode promover ganhos interessantes. Para saber mais, consulte o estudo de Aaron Bertrand sobre esse tema.

A solução

Resolvemos o problema substituindo a lógica procedural por outra utilizando blocos (set-based).

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

Esta consulta consome apenas 2 segundos (um ganho de mais de 10 vezes).

Conclusão e recomendações

No cenário real, a modificação seguindo os padrões que indicamos reduziu o tempo de processamento de 12 horas para apenas 5 minutos. Os ganhos foram notáveis tanto para quem utiliza o sistema como para quem o mantém, afinal, além do ganho de tempo de processamento, também houve economia de CPU e memória.

Ao otimizar consultas em SQL, muitas vezes é necessário mais do que “melhorar o código”. Muitas vezes, a abordagem inteira está incorreta, logo, é necessário uma reescrita completa.

Técnicas comuns e que funcionam bem para o desenvolvimento de aplicações não são necessariamente boas para bancos de dados. Na verdade, o código que melhoramos aqui foi claramente escrito por um programador de aplicações. Considerando que o banco de dados tem grande impacto na performance, é essencial estudar seu funcionamento, para fazer uso mais apropriado de seus recursos.

Como dissemos, utilizar os recursos certos, do jeito certo, de qualquer tecnologia é determinante para a boa performance de uma aplicação. Usar o banco de dados da maneira errada geralmente implica em problemas desagradáveis que poderiam ser evitados. Antes de botar a culpa no banco, use-o direito!

Compartilhe este insight:

Comentários

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

Subscribe
Notify of
guest
1 Comentário
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Rodrigo
Rodrigo
1 ano atrás

Para melhorar o desempenho desse código, uma alternativa seria substituir o uso do cursor por uma solução baseada em conjunto de resultados (set-based solution). Isso poderia ser feito usando a função “ROW_NUMBER()” para atribuir um valor incremental progressivo à coluna “line”, sem a necessidade de iterar através das linhas com um loop.

Veja como ficaria esse código:

USE tempDB
GO

SET NOCOUNT ON;

UPDATE dbo.ProcessoDemorado
SET line =
(
SELECT ROW_NUMBER() OVER (ORDER BY orderDate, value, typeID, id)
FROM dbo.ProcessoDemorado
WHERE typeID NOT IN (1, 2) AND id <= A.id
)
FROM dbo.ProcessoDemorado AS A;
Nesse código, a função "ROW_NUMBER()" é usada dentro da subconsulta para atribuir um número de linha progressivo às linhas onde o valor da coluna "typeID" não é 1 ou 2. A cláusula "ORDER BY" é usada para classificar as linhas em ordem crescente de "orderDate", "value", "typeID" e "id".

A condição "id <= A.id" na subconsulta é usada para garantir que apenas as linhas anteriores ou iguais a cada linha atual sejam usadas para calcular o valor da coluna "line". Isso garante que o valor da coluna "line" atribuído a cada linha seja único e progressivo.

Essa solução baseada em conjunto de resultados tem um melhor desempenho do que o uso de um cursor, pois evita a necessidade de iterar através das linhas uma por uma.

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
1
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 a performance de consultas SQL em mais de 1400% com abordagens “set-based”

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

Melhorando a performance de consultas SQL em mais de 1400% 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?