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)

declare cursorProcesso cursor local fast_forward

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!