Desempenho de parâmetro de instrução preparada para o driver JDBC

Baixar driver JDBC

Este artigo explica como os parâmetros da instrução preparada afetam o desempenho do lado do servidor no Microsoft JDBC Driver para SQL Server e fornece orientações sobre a otimização do uso dos parâmetros.

Compreensão dos parâmetros da declaração preparada

As instruções preparadas oferecem benefícios significativos de desempenho ao permitir que o SQL Server analise, compile e otimize uma consulta uma vez, e depois reutilize o plano de execução várias vezes. No entanto, a forma como especifica os parâmetros pode afetar significativamente este benefício de desempenho.

Quando cria uma instrução preparada, o SQL Server gera um plano de execução baseado nos metadados dos parâmetros, incluindo:

• Tipo de dados
• Precisão (para tipos numéricos)
• Escala (para tipos decimais)
• Comprimento máximo (para tipos de string e binários)

Estes metadados são cruciais porque o SQL Server os utiliza para otimizar o plano de execução da consulta. Alterações a qualquer uma destas características de parâmetros podem obrigar o SQL Server a descartar o plano existente e criar um novo, o que resulta numa penalização de desempenho.

Como as alterações de parâmetros afetam o desempenho

Alterações no tipo de parâmetro

Quando o tipo de parâmetro de uma instrução preparada muda entre execuções, o SQL Server deve repreparar a instrução. Esta repreparação inclui:

1. A analisar novamente a instrução SQL.
2. Compilando um novo plano de execução.
3. Guardar em cache o novo plano (se o cache estiver ativado).

Considere o seguinte exemplo:

String sql = "SELECT * FROM Employees WHERE EmployeeID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with Integer
pstmt.setInt(1, 100);
pstmt.executeQuery();

// Second execution with String - causes re-preparation
pstmt.setString(1, "100");
pstmt.executeQuery();

Neste cenário, mudar de setInt para setString altera o tipo de parâmetro de int para varchar, o que obriga o SQL Server a repreparar a instrução.

Alterações de precisão e escala

Para tipos numéricos como decimal e numeric, alterações de precisão ou escala também desencadeiam a repreparação:

String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with specific precision
BigDecimal price1 = new BigDecimal("19.99"); // precision 4, scale 2
pstmt.setBigDecimal(1, price1);
pstmt.setInt(2, 1);
pstmt.executeUpdate();

// Second execution with different precision - causes re-preparation
BigDecimal price2 = new BigDecimal("1999.9999"); // precision 8, scale 4
pstmt.setBigDecimal(1, price2);
pstmt.setInt(2, 2);
pstmt.executeUpdate();

O SQL Server cria diferentes planos de execução para diferentes combinações de precisão e escala porque a precisão e a escala afetam a forma como o motor de base de dados processa a consulta.

Melhores práticas para a utilização de parâmetros

Para maximizar o desempenho da declaração preparada, siga estas melhores práticas:

Especificar explicitamente os tipos de parâmetros

Sempre que possível, utilize os métodos de setter explícitos que correspondem aos tipos de colunas da sua base de dados:

// Good: Explicit type matching
pstmt.setInt(1, employeeId);
pstmt.setString(2, name);
pstmt.setBigDecimal(3, salary);

// Avoid: Using setObject() without explicit types
pstmt.setObject(1, employeeId); // Type inference might vary

Usar metadados consistentes dos parâmetros

Mantenha precisão consistente e escala para parâmetros numéricos:

// Good: Consistent precision and scale
BigDecimal price1 = new BigDecimal("19.99").setScale(2);
BigDecimal price2 = new BigDecimal("29.99").setScale(2);

// Avoid: Varying precision and scale
BigDecimal price3 = new BigDecimal("19.9");    // scale 1
BigDecimal price4 = new BigDecimal("29.999");  // scale 3

Compreenda arredondamento de dados com tipos numéricos

Usar precisão e escala incorretas para parâmetros numéricos pode resultar num arredondamento não intencional dos dados. A precisão e a escala devem ser adequadas tanto para o valor do parâmetro como para o local onde é usado na instrução SQL.

// Example: Column defined as DECIMAL(10, 2)
// Good: Matching precision and scale
BigDecimal amount = new BigDecimal("12345.67").setScale(2, RoundingMode.HALF_UP);
pstmt.setBigDecimal(1, amount);

// Problem: Scale too high causes rounding
BigDecimal amount2 = new BigDecimal("12345.678"); // scale 3
pstmt.setBigDecimal(1, amount2); // Rounds to 12345.68

// Problem: Precision too high
BigDecimal amount3 = new BigDecimal("123456789.12"); // Exceeds precision
pstmt.setBigDecimal(1, amount3); // Might cause truncation or error

Embora precise de precisão e escala adequadas para os seus dados, evite alterar esses valores em cada execução de uma declaração preparada. Cada alteração de precisão ou escala faz com que o comando seja repreparado no servidor, anulando os benefícios de desempenho dos comandos preparados.

// Good: Consistent precision and scale across executions
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO Orders (OrderID, Amount) VALUES (?, ?)");

for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Always use scale 2 for currency
    BigDecimal amount = order.getAmount().setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(2, amount);
    pstmt.executeUpdate();
}

// Avoid: Changing scale for each execution
for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Different scale each time - causes re-preparation
    pstmt.setBigDecimal(2, order.getAmount()); // Variable scale
    pstmt.executeUpdate();
}

Para equilibrar exatidão e desempenho:

1. Determine a precisão e escala adequadas às necessidades do seu negócio.
2. Normalize todos os valores dos parâmetros para usar precisão e escala consistentes.
3. Use modos de arredondamento explícitos para controlar como os valores são ajustados.
4. Valide se os seus valores normalizados correspondem às definições da coluna alvo.

Evite misturar métodos de definição de parâmetros

Não alterne entre diferentes métodos de definição para a mesma posição de parâmetro nas execuções:

// Avoid: Mixing setter methods
pstmt.setInt(1, 100);
pstmt.executeQuery();

pstmt.setString(1, "100"); // Different method - causes re-preparation
pstmt.executeQuery();

Use setNull() com tipos explícitos

Quando definir valores nulos, especifique o tipo SQL para manter a consistência:

// Good: Explicit type for null
pstmt.setNull(1, java.sql.Types.INTEGER);

// Avoid: Generic null without type
pstmt.setObject(1, null); // Type might be inferred differently

Monitorização do desempenho relacionado com parâmetros

Deteção de problemas de repreparação

Para identificar se alterações nos parâmetros estão a causar problemas de desempenho:

1. Use SQL Server Profiler ou Eventos Estendidos para monitorizar eventos SP:CacheMiss e SP:Recompile.
2. Consulte o sys.dm_exec_cached_plans DMV para verificar a reutilização do plano.
3. Analise métricas de desempenho de consultas para identificar declarações com repreparações frequentes.

Exemplo de consulta para verificar a reutilização do plano:

SELECT 
    text,
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE text LIKE '%YourQueryText%'
ORDER BY usecounts DESC;

Contadores de desempenho

Monitorize estes contadores de desempenho do SQL Server:

• SQL Statistics: SQL Re-Compilations/sec - Mostra com que frequência as instruções são recompiladas.
• SQL Statistics: SQL Compilations/sec - Mostra com que frequência novos planos são criados.
• Cache do Plano: Taxa de Acerto do Cache - Indica a eficácia com que os planos estão a ser reutilizados.

Para mais detalhes sobre os contadores e como os interpretar, consulte SQL Server, objeto Plano de Cache.

Considerações avançadas

Consultas parametrizadas e poluição do cache do plano

A poluição do cache de planos ocorre quando a precisão decimal ou numérica variável faz com que o SQL Server crie múltiplos planos de execução para a mesma consulta. Este problema desperdiça memória e reduz a eficiência da reutilização do plano:

// Avoid: Varying precision pollutes the plan cache
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Each different precision/scale creates a separate cached plan
    BigDecimal price = new BigDecimal("19." + i); // Varying scale
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

Para evitar a poluição do cache de planos, mantenha consistente precisão e escala para parâmetros numéricos.

// Good: Consistent precision and scale enables plan reuse
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Same precision and scale - reuses the same cached plan
    // Note: Round or increase to a consistent scale that aligns with your application data needs.
    BigDecimal price = new BigDecimal("19." + i).setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

As variações no comprimento das cadeias e nos valores inteiros não causam poluição no cache de planos — apenas alterações de precisão e escala para tipos numéricos causam este problema.

Propriedades da cadeia de conexão

O driver JDBC fornece propriedades de ligação que afetam o comportamento e o desempenho das instruções preparadas:

• enablePrepareOnFirstPreparedStatementCall - (Default: false) Controla se o driver chama sp_prepexec na primeira ou segunda execução. A preparação na primeira execução melhora ligeiramente o desempenho se uma aplicação executar consistentemente a mesma instrução preparada várias vezes. A preparação na segunda execução melhora o desempenho das aplicações que geralmente executam instruções preparadas uma vez. Esta estratégia elimina a necessidade de uma chamada separada para despreparar se a declaração preparada for executada apenas uma vez.
• PrepareMethod - (Padrão: prepexec) Especifica o comportamento a usar para preparação (prepare ou prepexec). Definir prepareMethod para prepare resulta numa viagem inicial separada à base de dados para preparar a instrução sem quaisquer valores iniciais para a base de dados considerar no plano de execução. Defina para prepexec usar sp_prepexec como método de preparação. Este método combina a ação de preparação com a primeira execução, reduzindo as viagens de ida e volta da rede. Ele também fornece ao banco de dados valores de parâmetros iniciais que o banco de dados pode considerar no plano de execução. Dependendo de como os seus índices estão otimizados, uma configuração pode ter um desempenho melhor do que a outra.
• serverPreparedStatementDiscardThreshold - (Padrão: 10) Controla o agrupamento das sp_unprepare operações. Esta opção pode melhorar o desempenho ao agrupar chamadas sp_unprepare. Um valor mais elevado faz com que as declarações preparadas permaneçam no servidor por mais tempo.

Para obter mais informações, consulte Definindo as propriedades de conexão.

Resumo

Para otimizar o desempenho de prepared statements para parâmetros:

1. Use métodos de setter explícitos que correspondam aos tipos de colunas da sua base de dados.
2. Mantenha metadados de parâmetros (tipo, precisão, escala, comprimento) consistentes entre execuções.
3. Não alterne entre diferentes métodos de setter para o mesmo parâmetro.
4. Especifique explicitamente os tipos SQL quando usar setObject ou setNull.
5. Reutilize declarações preparadas em vez de criar novas.
6. Monitor as estatísticas do cache do plano para identificar problemas de repreparação.
7. Considere propriedades de ligação que afetam o desempenho da declaração preparada.

Ao seguir estas práticas, minimiza a repreparação do lado do servidor e obtém os maiores benefícios de desempenho das declarações preparadas.

Consulte também

Armazenamento em cache de metadados de declarações preparadas para o driver JDBC
Melhorar o desempenho e a confiabilidade com o driver JDBC
Definindo as propriedades da conexão