Desempeño de los parámetros de las sentencias preparadas para el controlador JDBC

Descargar controlador JDBC

En este artículo se explica cómo los parámetros de instrucción preparados afectan al rendimiento del lado servidor en Microsoft JDBC Driver for SQL Server y proporciona instrucciones sobre cómo optimizar el uso de parámetros.

Comprensión de los parámetros de la instrucción preparada

Las instrucciones preparadas ofrecen ventajas de rendimiento significativas al permitir que SQL Server analice, compile y optimice una consulta una vez y vuelva a usar el plan de ejecución varias veces. Sin embargo, la forma de especificar parámetros puede afectar significativamente a esta ventaja de rendimiento.

Al crear una instrucción preparada, SQL Server genera un plan de ejecución basado en los metadatos del parámetro, entre los que se incluyen:

• Tipo de dato
• Precisión (para tipos numéricos)
• Escala (para tipos decimales)
• Longitud máxima (para tipos binarios y de cadena)

Estos metadatos son cruciales porque SQL Server lo usa para optimizar el plan de ejecución de consultas. Los cambios en cualquiera de estas características de parámetros pueden forzar a SQL Server a descartar el plan existente y crear uno nuevo, lo que da como resultado una penalización de rendimiento.

Cómo afectan los cambios de parámetro al rendimiento

Cambios en el tipo de parámetro

Cuando el tipo de parámetro de una instrucción preparada cambia entre ejecuciones, SQL Server debe volver a preparar la instrucción. Esta repreparación incluye:

1. Vuelva a analizar la instrucción SQL.
2. Compilar un nuevo plan de ejecución.
3. Almacenamiento en caché del nuevo plan (si está habilitado el almacenamiento en caché).

Considere el ejemplo siguiente:

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

En este escenario, cambiar de setInt a setString cambia el tipo de parámetro de int a varchar, lo que obliga a SQL Server a preparar nuevamente la instrucción.

Cambios de precisión y escala

En el caso de tipos numéricos como decimal y numeric, los cambios en precisión o escala también desencadenan la repreparación:

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 crea diferentes planes de ejecución para diferentes combinaciones de precisión y escala, ya que la precisión y la escala afectan al modo en que el motor de base de datos procesa la consulta.

Procedimientos recomendados para el uso de parámetros

Para maximizar el rendimiento de las instrucciones preparadas, siga estos procedimientos recomendados:

Especificar explícitamente tipos de parámetros

Cuando sea posible, use los métodos de establecedor explícitos que coincidan con los tipos de columna de base de datos:

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

Uso de metadatos de parámetro coherentes

Mantenga una precisión y una escala coherentes para los 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

Descripción del redondeo de datos con tipos numéricos

El uso de precisión y escala incorrectas para los parámetros numéricos puede dar lugar a un redondeo de datos no deseado. La precisión y la escala deben ser adecuadas para el valor del parámetro y donde se usa en la instrucción 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

Aunque necesita una precisión y una escala adecuadas para los datos, evite cambiar estos valores para cada ejecución de una instrucción preparada. Cada cambio de precisión o escala hace que la instrucción se vuelva a preparar en el servidor, negando las ventajas de rendimiento de las instrucciones 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 la corrección y el rendimiento:

1. Determine la precisión y escala adecuadas para sus requisitos empresariales.
2. Normalice todos los valores de parámetro para usar la precisión y la escala coherentes.
3. Use modos de redondeo explícitos para controlar cómo se ajustan los valores.
4. Compruebe que los valores normalizados coinciden con las definiciones de columna de destino.

Evitar la combinación de métodos de configuración de parámetros

No cambie entre distintos métodos establecedores para la misma posición de parámetro entre ejecuciones:

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

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

Uso de setNull() con tipos explícitos

Al establecer valores NULL, especifique el tipo SQL para mantener la coherencia:

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

Supervisión del rendimiento relacionado con parámetros

Detección de problemas de repreparación

Para identificar si los cambios de parámetro están causando problemas de rendimiento:

1. Utiliza SQL Server Profiler o Extended Events para supervisar los eventos SP:CacheMiss y SP:Recompile.
2. Compruebe la DMV para verificar la reutilización del plan sys.dm_exec_cached_plans.
3. Analice las métricas de rendimiento de las consultas para identificar sentencias con repreparaciones frecuentes.

Consulta de ejemplo para comprobar la reutilización del plan:

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 rendimiento

Supervise estos contadores de rendimiento de SQL Server:

• Estadísticas de SQL: recompilaciones de SQL por segundo : muestra la frecuencia con la que se vuelven a compilar las instrucciones.
• Estadísticas de SQL: compilaciones de SQL por segundo: muestra la frecuencia con la que se crean nuevos planes.
• Plan Cache: relación de aciertos de caché - indica la eficacia con que se reutilizan los planes.

Para obtener más información sobre los contadores y cómo interpretarlos, consulte SQL Server, Plan Cache object (Objeto de caché del plan).

Consideraciones avanzadas

Consultas parametrizadas y contaminación del caché de planes

La contaminación de la caché del plan se produce cuando la precisión decimal o numérica variable hace que SQL Server cree varios planes de ejecución para la misma consulta. Este problema desperdicia memoria y reduce la eficacia de reutilización del plan:

// 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 la contaminación de la caché del plan, mantén una precisión y una escala coherentes para los 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();

Las variaciones en la longitud de una cadena y el valor de un entero no provocan la contaminación de la caché de planes; solo los cambios en la precisión y la escala de los tipos numéricos crean este problema.

Propiedades de cadena de conexión

El controlador JDBC proporciona propiedades de conexión que afectan al comportamiento y el rendimiento de las instrucciones preparadas:

• enablePrepareOnFirstPreparedStatementCall : (valor predeterminado: false) Controla si el controlador llama sp_prepexec a en la primera o segunda ejecución. La preparación de la primera ejecución mejora ligeramente el rendimiento si una aplicación ejecuta de forma coherente la misma instrucción preparada varias veces. La preparación en la segunda ejecución mejora el rendimiento de las aplicaciones que ejecutan principalmente instrucciones preparadas una vez. Esta estrategia elimina la necesidad de una llamada separada a despreparar si la instrucción preparada solo se ejecuta una vez.
• prepareMethod : (valor predeterminado: prepexec) Especifica el comportamiento que se va a usar para la preparación (prepare o prepexec). Establecer prepareMethod en prepare da como resultado un viaje inicial independiente a la base de datos para preparar la instrucción sin ningún valor inicial para que la base de datos tenga en cuenta en el plan de ejecución. Establecer en prepexec para usar sp_prepexec como método de preparación. Este método combina la acción de preparación con la primera ejecución, lo que reduce los recorridos de ida y vuelta de red. También proporciona a la base de datos valores de parámetro iniciales que la base de datos puede tener en cuenta en el plan de ejecución. En función de cómo se optimicen los índices, una configuración puede funcionar mejor que la otra.
• serverPreparedStatementDiscardThreshold : (valor predeterminado: 10) Controla el procesamiento por lotes de sp_unprepare operaciones. Esta opción puede mejorar el rendimiento al agrupar llamadas conjuntas sp_unprepare en un solo procesamiento por lotes. Un valor más alto hace que las declaraciones preparadas permanezcan en el servidor por más tiempo.

Para obtener más información, consulte Establecimiento de las propiedades de conexión.

Resumen

Para optimizar el rendimiento de las instrucciones preparadas cuando se utilizan parámetros:

1. Use métodos de establecedor explícitos que coincidan con los tipos de columna de base de datos.
2. Mantenga coherentes los metadatos de parámetros (tipo, precisión, escala, longitud) entre ejecuciones.
3. No cambie entre métodos establecedores diferentes para el mismo parámetro.
4. Especifique los tipos SQL explícitamente al usar setObject o setNull.
5. Reutilice las instrucciones preparadas en lugar de crear nuevas.
6. Supervise las estadísticas de caché del plan para identificar problemas de repreparación.
7. Considere las propiedades de conexión que afectan al rendimiento de las instrucciones preparadas.

Al seguir estas prácticas, se minimiza la repreparación del lado servidor y se obtienen las máximas ventajas de rendimiento de las sentencias preparadas.

Consulte también

Almacenamiento en caché de los metadatos de declaraciones preparadas para el controlador JDBC
Mejora del rendimiento y la confiabilidad con el controlador JDBC
Establecimiento de las propiedades de conexión