Desempenho de parâmetros de instruções preparadas para o driver JDBC

Baixar o JDBC Driver

Este artigo explica como os parâmetros de instrução preparados afetam o desempenho do lado do servidor no Microsoft JDBC Driver for SQL Server e fornece diretrizes sobre como otimizar o uso de parâmetros.

Compreendendo parâmetros de declaração preparada

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

Quando você cria uma instrução preparada, o SQL Server gera um plano de execução com base nos metadados do parâmetro, incluindo:

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

Esses metadados são cruciais porque o SQL Server o usa para otimizar o plano de execução da consulta. Alterações em qualquer uma dessas características de parâmetro podem forçar o SQL Server a descartar o plano existente e criar um novo, o que resulta em uma penalidade de desempenho.

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

Alterações de tipo de parâmetro

Quando o tipo de parâmetro de uma instrução preparada é alterado entre execuções, o SQL Server deve reprepar a instrução. Essa repreparação inclui:

1. Analisando a instrução SQL novamente.
2. Compilando um novo plano de execução.
3. Armazenando em cache o novo plano (se o cache estiver habilitado).

Considere o exemplo a seguir:

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();

Nesse cenário, alternar de setInt para setString altera o tipo de parâmetro de int para varchar, o que força o SQL Server a repreparar o comando.

Alterações de precisão e escala

Para tipos numéricos como decimal e numeric, as alterações na precisão ou na escala também disparam 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 planos de execução diferentes para diferentes combinações de precisão e escala porque a precisão e a escala afetam a forma como o mecanismo de banco de dados processa a consulta.

Práticas recomendadas para uso de parâmetro

Para maximizar o desempenho da instrução preparada, siga estas práticas recomendadas:

Especificar tipos de parâmetro explicitamente

Quando possível, use os métodos setter explícitos que correspondem aos tipos de coluna de banco 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 de parâmetro consistentes

Mantenha a precisão e a escala consistentes 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

Entender o arredondamento de dados com tipos numéricos

Usar a precisão e a escala incorretas para parâmetros numéricos pode resultar em arredondamento de dados não intencional. A precisão e a escala devem ser apropriadas para o valor do parâmetro e onde ele é 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 você precise de precisão e escala apropriadas para seus dados, evite alterar esses valores para cada execução de uma instrução preparada. Cada alteração na precisão ou escala faz com que a instrução seja reapreparada no servidor, negando os benefícios de desempenho das instruções preparadas.

// 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 a correção e a performance:

1. Determine a precisão e a escala apropriadas para seus requisitos de negócios.
2. Normalize todos os valores de parâmetro para usar a precisão e a escala consistentes.
3. Use modos de arredondamento explícitos para controlar como os valores são ajustados.
4. Valide se os valores normalizados correspondem às definições da coluna de destino.

Evitar a combinação de métodos de configuração de parâmetro

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

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

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

Usar setNull() com tipos explícitos

Ao 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

Monitorando o desempenho relacionado ao parâmetro

Detectando problemas de repreparação

Para identificar se as alterações de parâmetro estão causando problemas de desempenho:

1. Use SQL Server Profiler ou Extended Events para monitorar eventos SP:CacheMiss e SP:Recompile.
2. Examine a sys.dm_exec_cached_plans DMV para verificar a reutilização do plano.
3. Analise as métricas de desempenho da consulta de banco de dados para identificar instruçõ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

Monitore estes contadores de desempenho do SQL Server:

• Estatísticas do SQL: Recompilações de SQL/s – mostra com que frequência as instruções são recompiladas.
• Estatísticas do SQL: Compilações de SQL/s – mostra a frequência com que novos planos são criados.
• Cache de Planos: Taxa de Acertos do Cache – indica a eficiência com que os planos são reutilizados.

Para obter mais detalhes sobre os contadores e como interpretá-los, consulte SQL Server, objeto Cache de Plano.

Considerações avançadas

Consultas parametrizadas e poluição do cache de planos

A poluição do cache do plano ocorre quando a precisão decimal ou numérica variável faz com que o SQL Server crie vários planos de execução para a mesma consulta. Esse problema desperdiça memória e reduz a eficiência de 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 consistentes a precisão e a escala dos 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();

O comprimento da cadeia de caracteres e as variações de valor inteiro não causam a poluição do cache do plano– somente alterações de precisão e escala para tipos numéricos criam esse problema.

Propriedades da cadeia de conexão

O driver JDBC fornece propriedades de conexão que afetam o comportamento e o desempenho da instrução preparada:

• enablePrepareOnFirstPreparedStatementCall – (Padrão: 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 um aplicativo executa consistentemente a mesma instrução preparada várias vezes. A preparação na segunda execução melhora o desempenho de aplicativos que executam principalmente instruções preparadas uma vez. Essa estratégia remove a necessidade de uma chamada separada para despreparar se a instrução preparada for executada apenas uma vez.
• prepareMethod - (Padrão: prepexec) Especifica o comportamento a ser usado para preparação (prepare ou prepexec). A configuração de prepareMethod para prepare resulta em uma viagem inicial separada ao banco de dados para preparar a instrução sem valores iniciais para o banco de dados considerá-los no plano de execução. Defina prepexec para usar sp_prepexec como método de preparação. Esse método combina a ação de preparação com a primeira execução, reduzindo as viagens de ida e volta de rede. Ele também fornece ao banco de dados valores de parâmetro iniciais que o banco de dados pode considerar no plano de execução. Dependendo de como seus índices são otimizados, uma configuração pode ter um desempenho melhor do que a outra.
• serverPreparedStatementDiscardThreshold – (Padrão: 10) Controla o processamento em lote de sp_unprepare operações. Essa opção pode melhorar o desempenho agrupando sp_unprepare chamadas em lote. Um valor mais alto faz com que as instruções preparadas permaneçam no servidor por mais tempo.

Para obter mais informações, veja Configuração das propriedades de conexão.

Resumo

Para otimizar o desempenho da declaração preparada para parâmetros:

1. Use métodos setters explícitos que correspondam aos tipos de coluna do seu banco de dados.
2. Mantenha metadados de parâmetro (tipo, precisão, escala, comprimento) consistentes entre execuções.
3. Não alterne entre diferentes métodos setter para o mesmo parâmetro.
4. Especifique os tipos SQL explicitamente quando você usa setObject ou setNull.
5. Reutilize prepared statements em vez de criar novos.
6. Monitore as estatísticas de cache do plano para identificar problemas de repreparação.
7. Considere as propriedades de conexão que afetam o desempenho da declaração preparada.

Seguindo essas práticas, você minimiza a repreparação do lado do servidor e obtém o maior número de benefícios de desempenho de instruções preparadas.

Consulte também

Cache de metadados para instruções preparadas do driver JDBC
Melhorando o desempenho e a confiabilidade com o JDBC Driver
Configuração das propriedades de conexão