Performances des paramètres d’instruction préparées pour le pilote JDBC

Télécharger le pilote JDBC

Cet article explique comment les paramètres d’instruction préparés affectent les performances côté serveur dans Microsoft JDBC Driver pour SQL Server et fournit des conseils sur l’optimisation de l’utilisation des paramètres.

Comprendre les paramètres des instructions préparées

Les instructions préparées offrent des avantages significatifs en matière de performances en permettant à SQL Server d’analyser, de compiler et d’optimiser une requête une seule fois, puis de réutiliser le plan d’exécution plusieurs fois. Toutefois, la façon dont vous spécifiez des paramètres peut affecter considérablement cet avantage de performances.

Lorsque vous créez une instruction préparée, SQL Server génère un plan d’exécution basé sur les métadonnées de paramètre, notamment :

• Type de données
• Précision (pour les types numériques)
• Échelle (pour les types décimaux)
• Longueur maximale (pour les types chaîne et binaire)

Ces métadonnées sont cruciales, car SQL Server l’utilise pour optimiser le plan d’exécution de requête. Les modifications apportées à l’une de ces caractéristiques de paramètre peuvent forcer SQL Server à ignorer le plan existant et à en créer une, ce qui entraîne une pénalité de performances.

Comment les modifications des paramètres affectent les performances

Modifications du type de paramètre

Quand le type de paramètre d’une instruction préparée change entre les exécutions, SQL Server doit repreparer l’instruction. Cette repreparation inclut :

1. Analyse à nouveau de l’instruction SQL.
2. Compilation d’un nouveau plan d’exécution.
3. Mise en cache du nouveau plan (si la mise en cache est activée).

Prenons l'exemple suivant :

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

Dans ce scénario, passer de setInt à setString modifie le type de paramètre de int à varchar, ce qui force SQL Server à re-préparer l'instruction.

Modifications de précision et d’échelle

Pour les types numériques tels que decimal et numeric, les modifications de précision ou d’échelle déclenchent également la repréparation :

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 crée différents plans d’exécution pour différentes combinaisons de précision et d’échelle, car la précision et l’échelle affectent la façon dont le moteur de base de données traite la requête.

Meilleures pratiques pour l’utilisation des paramètres

Pour optimiser les performances des instructions préparées, suivez les bonnes pratiques suivantes :

Spécifier explicitement les types de paramètres

Si possible, utilisez les méthodes setter explicites qui correspondent à vos types de colonnes de base de données :

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

Utiliser des métadonnées de paramètre cohérentes

Maintenez une précision et une échelle cohérentes pour les paramètres numériques :

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

Comprendre l’arrondi de données avec des types numériques

L’utilisation d’une précision et d’une mise à l’échelle incorrectes pour les paramètres numériques peut entraîner une arrondi involontaire des données. La précision et l’échelle doivent être appropriées pour la valeur du paramètre et l’emplacement où elles sont utilisées dans l’instruction 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

Bien que vous ayez besoin d’une précision et d’une mise à l’échelle appropriées pour vos données, évitez de modifier ces valeurs pour chaque exécution d’une instruction préparée. Chaque modification de précision ou d’échelle entraîne la réécriture de l’instruction sur le serveur, ce qui annule les avantages en matière de performances des instructions préparées.

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

Pour équilibrer l’exactitude et les performances :

1. Déterminez la précision et l’échelle appropriées pour vos besoins métier.
2. Normalisez toutes les valeurs de paramètre pour utiliser une précision et une échelle cohérentes.
3. Utilisez des modes d’arrondi explicites pour contrôler la façon dont les valeurs sont ajustées.
4. Vérifiez que vos valeurs normalisées correspondent aux définitions de colonne cible.

Éviter de mélanger les méthodes de réglage des paramètres

Ne basculez pas entre différentes méthodes setter pour la même position de paramètre entre les exécutions :

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

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

Utiliser setNull() avec des types explicites

Lorsque vous définissez des valeurs Null, spécifiez le type SQL pour maintenir la cohérence :

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

Surveillance des performances liées aux paramètres

Détection des problèmes de répréparation

Pour identifier si les modifications de paramètre provoquent des problèmes de performances :

1. Utilisez SQL Server Profiler ou les Événements étendus pour surveiller les événements SP:CacheMiss et SP:Recompile.
2. Passez en revue le DMV pour vérifier la sys.dm_exec_cached_plans réutilisation du plan.
3. Analysez les indicateurs de performance des requêtes SQL pour identifier les instructions avec des recompilations fréquentes.

Exemple de requête pour vérifier la réutilisation du 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;

Compteurs de performance

Surveillez ces compteurs de performances SQL Server :

• Statistiques SQL : Compilations SQL/s : indique la fréquence à laquelle les instructions sont recompilées.
• Statistiques SQL : compilations SQL/s - montre la fréquence de création de nouveaux plans.
• Cache de plan : rapport d’accès au cache : indique la façon dont les plans sont réutilisés efficacement.

Pour plus d’informations sur les compteurs et sur la façon de les interpréter, consultez SQL Server, Objet Plan Cache.

Considérations avancées

Requêtes paramétrables et pollution du cache de plan

La pollution du cache de plan se produit lorsque la précision décimale ou la précision des valeurs numériques varie, et que SQL Server crée plusieurs plans d’exécution pour la même requête. Ce problème gaspille la mémoire et réduit l’efficacité de réutilisation du 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();

Pour éviter la pollution du cache de plan, conservez une précision et une échelle cohérentes pour les paramètres numériques :

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

Les variations de valeur de chaîne et d’entier n’entraînent pas de pollution du cache de plan : seules les modifications de précision et d’échelle pour les types numériques créent ce problème.

Propriétés de chaîne de connexion

Le pilote JDBC fournit des propriétés de connexion qui affectent le comportement et les performances des instructions préparées :

• enablePrepareOnFirstPreparedStatementCall - (Par défaut : false) Contrôle si le pilote appelle sp_prepexec sur la première ou la deuxième exécution. La préparation sur la première exécution améliore légèrement les performances si une application exécute de manière cohérente la même instruction préparée plusieurs fois. La préparation sur la deuxième exécution améliore les performances des applications qui exécutent principalement des instructions préparées une seule fois. Cette stratégie supprime la nécessité d’un appel distinct pour annuler la préparation si l’instruction préparée n’est exécutée qu’une seule fois.
• prepareMethod - (par défaut : prepexec) Spécifie le comportement à utiliser pour la préparation (prepare ou prepexec). La définition de prepareMethod à prepare entraîne un déplacement initial distinct vers la base de données pour préparer l'instruction, sans que des valeurs initiales soient prises en compte par la base de données dans le plan d'exécution. Réglez sur pour utiliser comme méthode de préparation. Cette méthode combine l’action de préparation avec la première exécution, ce qui réduit les allers-retours réseau. Il fournit également à la base de données des valeurs de paramètre initiales que la base de données peut prendre en compte dans le plan d’exécution. Selon la façon dont vos index sont optimisés, un paramètre peut fonctionner mieux que l’autre.
• serverPreparedStatementDiscardThreshold - (Par défaut : 10) Contrôle le traitement par lots d’opérations sp_unprepare . Cette option peut améliorer les performances en regroupant les appels sp_unprepare en lot. Une valeur plus élevée laisse les instructions préparées demeurer sur le serveur plus longtemps.

Pour plus d'informations, consultez Définition des propriétés de connexion.

Résumé

Pour optimiser les performances des instructions préparées pour les paramètres :

1. Utilisez des méthodes setter explicites qui correspondent à vos types de colonnes de base de données.
2. Conservez les métadonnées des paramètres (type, précision, échelle, longueur) cohérentes entre les exécutions.
3. Ne basculez pas entre différentes méthodes setter pour le même paramètre.
4. Spécifiez explicitement des types SQL lorsque vous utilisez setObject ou setNull.
5. Réutilisez les instructions préparées au lieu de créer de nouvelles instructions.
6. Surveillez les statistiques du cache de plan pour identifier les problèmes de repréparation.
7. Envisagez les propriétés de connexion qui affectent les performances des instructions préparées.

En suivant ces pratiques, vous réduisez la préparation côté serveur et profitez pleinement des avantages de performance des instructions préparées.

Voir aussi

Mise en cache des métadonnées des instructions préparées pour le driver JDBC
Amélioration des performances et de la fiabilité avec le pilote JDBC
Définition des propriétés de connexion