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

Marcelo Michelon

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!

Em resumo

O problema
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. Um exemplo claro é a utilização incorreta de cursores.
O insight
Técnicas comuns e que funcionam bem para o desenvolvimento de aplicações não são necessariamente boas para bancos de dados. Considerando que o banco de dados tem grande impacto na performance, é essencial estudar seu funcionamento, para fazer uso mais apropriado de seus recursos. Um bom exemplo são consultas “set-based”

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 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?