Prepared statements no nível do banco: economia de parsing

1. O Ciclo de Vida de uma Query SQL no Banco

Toda consulta SQL enviada ao banco de dados passa por três fases principais antes de retornar resultados:

  • Parsing: O servidor analisa a sintaxe da query, verifica a existência das tabelas e colunas referenciadas (análise semântica) e gera uma árvore de consulta lógica.
  • Otimização: O otimizador avalia diferentes planos de execução (uso de índices, métodos de join, ordenação) e escolhe o mais eficiente com base em estatísticas.
  • Execução: O plano escolhido é executado, acessando os dados no disco ou cache e retornando o resultado.

O parsing e a otimização consomem CPU e podem representar de 30% a 60% do tempo total de uma consulta simples. Em aplicações que executam milhares de queries semelhantes por segundo, esse overhead se acumula dramaticamente.

2. O que São Prepared Statements e Como Funcionam

Prepared statements são consultas SQL pré-compiladas que utilizam placeholders para parâmetros (como $1, $2 no PostgreSQL ou ? no MySQL). O fluxo básico é:

PREPARE nome_da_query (tipo_param1, tipo_param2) AS
    SELECT * FROM tabela WHERE coluna1 = $1 AND coluna2 = $2;

EXECUTE nome_da_query('valor1', 'valor2');

DEALLOCATE nome_da_query;

Quando você executa PREPARE, o banco realiza o parsing e a otimização uma única vez, armazenando o plano de execução em cache. Cada EXECUTE subsequente apenas substitui os placeholders pelos valores reais e executa o plano já compilado.

É crucial distinguir:
- Prepared statement do driver (cliente): A string SQL é montada no cliente e enviada como query comum. O banco faz parsing toda vez.
- Prepared statement do banco (servidor): O comando PREPARE é enviado ao servidor, que mantém o plano em cache. Cada EXECUTE reaproveita o plano.

3. Economia de Parsing: O Ganho Real de Performance

A economia de parsing é mais significativa em cenários de alta repetição:

  • Loops de aplicação: Inserir 10.000 registros em um loop sem prepared statement força 10.000 parsings completos. Com prepared statement, apenas 1 parsing + 10.000 execuções.
  • APIs REST: Endpoints que executam a mesma query com parâmetros diferentes (ex: buscar usuário por ID) se beneficiam enormemente.
  • Processamento batch: Atualizações em lote com mesma estrutura de query.

Comparação prática (medidas aproximadas em PostgreSQL):

Query ad-hoc (10.000 execuções):
  Parsing: 10.000 × 0.5ms = 5.000ms
  Otimização: 10.000 × 1.0ms = 10.000ms
  Execução: 10.000 × 0.3ms = 3.000ms
  Total: ~18.000ms

Prepared statement (10.000 execuções):
  Parsing: 1 × 0.5ms = 0.5ms
  Otimização: 1 × 1.0ms = 1.0ms
  Execução: 10.000 × 0.3ms = 3.000ms
  Total: ~3.001ms

Redução de aproximadamente 83% no tempo total.

4. Implementação Prática com SQL Padrão

Exemplo completo: inserção de 10.000 registros em uma tabela de logs.

-- Criação da tabela
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    usuario VARCHAR(100),
    acao VARCHAR(50),
    data_hora TIMESTAMP
);

-- Preparação da query
PREPARE insere_log (VARCHAR, VARCHAR, TIMESTAMP) AS
    INSERT INTO logs (usuario, acao, data_hora)
    VALUES ($1, $2, $3);

-- Execução em lote (simulada)
EXECUTE insere_log('joao', 'login', '2024-01-15 10:00:00');
EXECUTE insere_log('maria', 'logout', '2024-01-15 10:05:00');
-- ... repetir 9.998 vezes

-- Limpeza
DEALLOCATE insere_log;

Importante: prepared statements têm escopo de sessão. Eles persistem até o DEALLOCATE explícito ou o fim da conexão.

5. Comportamento Específico em PostgreSQL

O PostgreSQL oferece recursos avançados para prepared statements:

  • Plano genérico vs. customizado: Na primeira execução, o PostgreSQL gera um plano customizado (baseado nos valores específicos). Após 5 execuções, ele pode gerar um plano genérico (reutilizável para qualquer valor).
EXPLAIN (GENERIC_PLAN) EXECUTE insere_log('teste', 'acao', NOW());
-- Mostra o plano genérico, sem valores específicos
  • Monitoramento: A view pg_prepared_statements lista todos os prepared statements da sessão:
SELECT name, statement, prepare_time, parameter_types
FROM pg_prepared_statements;
  • Limitação: Para tabelas com distribuição de dados muito assimétrica, o plano genérico pode ser subótimo. Exemplo: uma query WHERE preco > $1 que usa índice seletivo para valores altos, mas seq scan para valores baixos.

6. Prepared Statements em Drivers de Aplicação

Python com psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=teste")
cur = conn.cursor()

# Prepared statement implícito (server-side)
cur.execute("PREPARE busca_usuario (INT) AS SELECT * FROM usuarios WHERE id = $1")
cur.execute("EXECUTE busca_usuario(101)")
cur.execute("EXECUTE busca_usuario(202)")
cur.execute("DEALLOCATE busca_usuario")

Java com JDBC:

Connection conn = DriverManager.getConnection(url, user, pass);
String sql = "INSERT INTO logs (usuario, acao) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

for (int i = 0; i < 10000; i++) {
    pstmt.setString(1, "usuario_" + i);
    pstmt.setString(2, "acao_" + i);
    pstmt.executeUpdate();  // Reutiliza o mesmo plano
}
pstmt.close();

No JDBC, o driver pode optar por enviar PREPARE ao banco (server-side prepare) ou apenas montar a string SQL no cliente (client-side). Drivers modernos como PostgreSQL JDBC usam server-side prepare após algumas execuções.

7. Cuidados, Limitações e Boas Práticas

Quando NÃO usar prepared statements:
- Queries executadas uma única vez (ex: relatório ad-hoc).
- Comandos DDL (CREATE TABLE, ALTER) que não aceitam placeholders.
- Queries em tabelas que mudam de estrutura frequentemente (ex: tabelas temporárias voláteis).

Gerenciamento de memória:
- Cada prepared statement ocupa memória no servidor. Em sessões longas com muitas queries diferentes, o acúmulo pode ser significativo.
- Sempre use DEALLOCATE quando o statement não for mais necessário.

Escopo e segurança:
- Nomes de prepared statements são visíveis apenas na sessão atual.
- Evite nomes genéricos que possam conflitar com outras partes da aplicação.
- Prepared statements também previnem SQL injection, pois os parâmetros são tratados como dados, nunca como código SQL.

Alternativas:
- Bind variables no Oracle: Funcionamento similar, mas com sintaxe :param.
- Stored procedures: Oferecem pré-compilação semelhante, mas com lógica procedural embutida.

8. Benchmark e Conclusão

Cenário de teste: 100.000 inserts em uma tabela logs (PostgreSQL 15, SSD NVMe, 8GB RAM).

Sem prepared statement (100.000 inserts individuais):
  Tempo total: 23,4 segundos
  CPU (servidor): 89% utilizada
  Latência média: 0,234ms

Com prepared statement (100.000 execuções via EXECUTE):
  Tempo total: 4,1 segundos
  CPU (servidor): 32% utilizada
  Latência média: 0,041ms

Ganho: 5,7x mais rápido, 64% menos CPU

Resumo: A economia de parsing com prepared statements é uma das otimizações mais simples e eficazes para aplicações OLTP e processamento batch. Em sistemas com alta taxa de queries repetitivas (milhares por segundo), o ganho pode representar a diferença entre um servidor sobrecarregado e uma operação suave.

Recomendação final: Sempre utilize prepared statements para queries executadas mais de uma vez com parâmetros variáveis. A economia de CPU e tempo de resposta justifica amplamente o pequeno esforço de implementação.

Referências