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!