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!

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

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
12/03/2021
12/03
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.