JDBC 驱动程序的预处理语句参数性能

下载 JDBC 驱动程序

本文介绍准备的语句参数如何影响 Microsoft JDBC Driver for SQL Server 中的服务器端性能，并提供优化参数用法的指导。

了解准备好的语句参数

准备好的语句允许 SQL Server 分析、编译和优化查询一次，然后多次重复使用执行计划，从而提供显著的性能优势。但是，指定参数的方式可能会显著影响此性能优势。

创建准备好的语句时，SQL Server 会基于参数元数据生成执行计划，包括：

数据类型
精度（对于数值类型）
小数位数（对于十进制类型）
最大长度（对于字符串和二进制类型）

此元数据至关重要，因为 SQL Server 使用它来优化查询执行计划。对上述任何参数特征的更改可能会强制 SQL Server 放弃现有计划并创建一个新计划，这会导致性能损失。

参数更改如何影响性能

参数类型更改

当准备好的语句的参数类型在执行之间发生更改时，SQL Server 必须重新准备该语句。此重新准备包括：

再次分析 SQL 语句。
编译新的执行计划。
缓存新计划（如果启用了缓存）。

请看下面的示例：

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

在此情境中，从setInt切换到setString会将参数类型从int更改为varchar，这会强制 SQL Server 重新编译语句。

精度和规模变化

对于像decimal和numeric这样的数值类型，精度或小数位数的更改也会触发重新预编译：

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

SQL Server 为不同的精度和缩放组合创建不同的执行计划，因为精度和缩放会影响数据库引擎处理查询的方式。

参数用法的最佳做法

若要最大程度地提高准备好的语句性能，请遵循以下最佳做法：

显式指定参数类型

如果可能，请使用与数据库列类型匹配的显式设置器方法：

// 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

使用一致的参数元数据

保持数值参数的一致精度和比例：

// 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

了解使用数值类型的数据舍入

对数值参数使用不正确的精度和比例可能会导致无意识的数据舍入。精度和刻度必须适当，以适用于参数值及其在 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

虽然需要为数据提供适当的精确度和范围，但请避免在每次执行某个准备好的语句时更改这些值。精度或小数位数的每个更改都会导致在服务器上重新准备语句，从而否定已准备语句的性能优势。

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

为了兼顾正确性和性能：

确定适合业务需求的精度和规模。
规范化所有参数值以使用统一的精度和范围。
使用显式舍入模式来控制值的调整方式。
验证规范化值是否与目标列定义匹配。

注释

可以使用 calcBigDecimalPrecision 连接选项自动优化参数精度。启用后，驱动程序将计算每个 BigDecimal 值所需的最小精度，这有助于避免不必要的舍入。但是，由于不同的精度值导致重新准备，此方法可能会随着数据更改而增加语句准备的次数。在应用程序代码中手动定义最佳精度和比例尺是最佳选择，因为这样可以提供数据准确性并确保语句的一致性重用。

避免混合参数设置方法

不要在执行过程中为相同参数位置切换不同的 setter 方法。

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

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

将 setNull（）与显式类型配合使用

设置 null 值时，请指定 SQL 类型以保持一致性：

// 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

检测重新准备问题

若要确定参数更改是否导致性能问题：

使用 SQL Server Profiler 或扩展事件来监视 SP:CacheMiss 和 SP:Recompile 事件。
sys.dm_exec_cached_plans查看 DMV 以检查计划重用。
分析查询性能指标，以识别那些需要频繁重新准备的语句。

用于检查计划重用的示例查询：

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;

性能计数器

监视这些 SQL Server 性能计数器：

SQL 统计信息：SQL 重新编译数/秒 - 显示重新编译语句的频率。
SQL 统计信息：SQL 编译数/秒 - 显示创建新计划的频率。
计划缓存：缓存命中率 - 指示重用计划的效率。

有关计数器以及如何解释这些计数器的更多详细信息，请参阅 SQL Server 计划缓存对象。

高级考量

参数化查询和计划缓存污染

当不同的十进制或数值精度导致 SQL Server 为同一查询创建多个执行计划时，会发生计划缓存污染。此问题会浪费内存并减少计划重用效率：

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

为避免计划缓存污染，请确保数值参数的精度和刻度一致：

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

字符串长度和整数值变体不会导致计划缓存污染，只有数值类型的精度和小数位数更改才会造成此问题。

连接字符串属性

JDBC 驱动程序提供影响已准备语句行为和性能的连接属性：

enablePrepareOnFirstPreparedStatementCall - （默认值： false）控制驱动程序是在第一次执行还是第二次执行时调用 sp_prepexec 。如果应用程序多次执行相同的已准备语句，则首次执行时的准备会稍微提高性能。准备第二次执行可以提高主要执行一次已准备语句的应用程序的性能。如果准备好的语句只被执行一次，那么该策略就无需再单独调用 unprepare。
prepareMethod - （默认值： prepexec）指定要用于准备的行为（prepare 或 prepexec）。 prepareMethod 设置为 prepare 会导致分别访问数据库，以准备语句，而在执行计划中无需考虑任何初始数据库值。将prepexec设置为sp_prepexec以用作准备方法。此方法将准备动作与首次执行相结合，从而减少网络往返。它还为数据库提供数据库可以在执行计划中考虑的初始参数值。根据索引的优化方式，一个设置可能比另一个设置表现更佳。
serverPreparedStatementDiscardThreshold - （默认值：） 10控制批处理操作sp_unprepare 。此选项可以通过对 sp_unprepare 调用进行批处理来提高性能。较高的值会导致预处理语句在服务器上停留更长时间。

有关详细信息，请参阅设置连接属性。

概要

优化参数的预处理语句性能：

使用与数据库列类型匹配的显式 setter 方法。
使参数元数据（类型、精度、小数位数、长度）在整个执行中保持一致。
不要在同一参数的不同 setter 方法之间进行切换。
使用setObject或setNull时，请显式指定 SQL 类型。
重复使用准备好的语句，而不是创建新语句。
监视计划缓存统计信息，以确定重新准备问题。
考虑会影响预准备语句性能的连接属性。

通过遵循这些做法，可以尽量减少服务器端的重新准备，并从预准备语句中获得最佳性能收益。

另请参阅

JDBC 驱动程序的已准备语句元数据缓存
 通过 JDBC 驱动程序提升性能和可靠性
 设置连接属性

反馈

此页面是否有帮助？

Last updated on 2025-12-22