Otimizando o cálculo de totais no Laravel/Lumen usando agregação condicional

Aviso aos navegantes: esse papo aqui é dev para dev, morô? então se quiser continuar lendo, é por sua conta e risco, saiba que vai ter muito termo técnico aqui, e se não entender algum, pare o texto, pesquise o que significa e volte para aqui ❤

Olá pessoal, eu sou o Gianluca (ou Gian), desenvolvedor full stack aqui no aiqfome. Nesse artigo vou falar sobre algo que todo dev já deve ter trabalhado na vida: otimização/melhoria de consultas SQL no backend. Mais especificamente, a otimização de consultas de cálculos de totais.

Nós devs, temos a tendência de pensar que todas as requisições feitas ao backend de aplicação são independentes. Assim, se uma requisição for lenta, é improvável que ela afete a outra… certo?

Nem sempre!

Não é o que acontece com o banco de dados de uma aplicação.

O banco de dados executa todas as consultas de forma coletiva e existem limites (conexões, CPU, memória), que definem o número de consultas que pode ser executado de uma vez. Assim, todos endpoints da sua aplicação são importantes na perspectiva do desempenho do banco de dados.

Devemos tratar o BD como um recurso compartilhado por todos os usuários da sua aplicação, onde cada consulta executada pelo banco de dados utiliza um pouco desse recurso finito. Rode muitas consultas pesadas ao mesmo tempo e ele pode chegar no limite.

Então, precisamos encontrar as consultas que demandam mais recursos e otimizá-las. Normalmente essas consultas são aquelas que calculam algum tipo de total e principalmente quando múltiplos totais são necessários na mesma requisição. Vamos ao exemplo:

Digamos que temos uma tabela no banco de dados chamada orders com dados neste formato:

Desejamos mostrar os seguintes totais de pedidos para o usuário:

• Aprovados: X
• Finalizados: X
• Cancelados: X
• Enviados: X
• Total: X

Uma das maneiras mais fáceis para realizar o cálculo desses totais é a seguinte:

O problema dessa maneira é a execução de cinco consultas no banco de dados, o que sabemos que não é muito bom. Pra resolver, podemos tentar otimizar o código utilizando as Collections do Eloquent, ficaria assim:

Com essa otimização temos apenas uma consulta no banco de dados. Problema resolvido, então? Nope! Esse método é pior do que o anterior. Isso ocorre pois, apesar de termos somente uma consulta ao banco de dados, a contagem dos resultados está sendo executada pelo Collection do Eloquent, ou seja, pelo PHP.

Agregação condicional

Nesse caso, a agregação condicional pode ser utilizada para calcular todos os totais com somente uma consulta, como no exemplo:

Resultado da execução da consulta:

Convertendo essa consulta SQL para o Eloquent temos:

Muito massa né?

Tente usar as agregações condicionais na próxima vez que criar um painel ou algum outro tipo de resumo de dados em sua aplicação. Seu banco de dados vai ficar feliz!

Abaixo temos a comparação do desempenho das 3 maneiras apresentadas para cálculo de totais, utilizando um banco de dados com 500mil registros:

• 5 consultas separadas: 0.14 ms;
• 1 consulta e count no Eloquent Collections: 20.14 ms;
• 1 consulta usando agregação condicional: 0.09 ms;

Lembre-se de que qualquer consulta que você possa fazer com mais rapidez ou evitar totalmente ajudará o desempenho de todo o sistema. Toda consulta é importante!

Pra dúvidas por favor falar com o Clodoaldo. Ele tá sempre no twitter, instagram, face, e até no linkedin do aiq.

Mentira, o Crô é mó ocupado, pode me responder por aqui mesmo ou conversar comigo no Linkedin!