Leistung von Parametern in vorbereiteten Anweisungen für den JDBC-Treiber

JDBC-Treiber herunterladen

In diesem Artikel wird erläutert, wie sich vorbereitete Anweisungsparameter auf die serverseitige Leistung im Microsoft JDBC-Treiber für SQL Server auswirken und bietet Leitlinien zur Optimierung der Parameternutzung.

Verständnis von Parametern vorbereiteter Anweisungen

Vorbereitete Anweisungen bieten erhebliche Leistungsvorteile, indem SQL Server eine Abfrage einmal analysieren, kompilieren und optimieren und dann den Ausführungsplan mehrmals wiederverwenden kann. Die Art und Weise, wie Sie Parameter angeben, kann sich jedoch erheblich auf diesen Leistungsvorteil auswirken.

Wenn Sie eine vorbereitete Anweisung erstellen, generiert SQL Server basierend auf den Parametermetadaten einen Ausführungsplan, einschließlich:

• Datentyp
• Genauigkeit (für numerische Typen)
• Skalierung (für Dezimaltypen)
• Maximale Länge (für Zeichenfolgen- und Binärtypen)

Diese Metadaten sind von entscheidender Bedeutung, da SQL Server ihn zum Optimieren des Abfrageausführungsplans verwendet. Änderungen an diesen Parametermerkmalen können SQL Server zwingen, den vorhandenen Plan zu verwerfen und eine neue zu erstellen, was zu leistungseinbußen führt.

Auswirkungen von Parameteränderungen auf die Leistung

Parametertypänderungen

Wenn sich der Parametertyp einer vorbereiteten Anweisung zwischen Ausführungen ändert, muss SQL Server die Anweisung erneut erstellen. Diese Neuvorbereitung umfasst:

1. Analysieren der SQL-Anweisung erneut.
2. Kompilieren eines neuen Ausführungsplans.
3. Zwischenspeichern des neuen Plans (wenn die Zwischenspeicherung aktiviert ist).

Betrachten Sie das folgenden Beispiel:

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

In diesem Szenario wechselt von setInt zu setString, wodurch sich der Parametertyp von int zu varchar ändert, was den SQL Server zwingt, die Anweisung neu vorzubereiten.

Genauigkeits- und Skalierungsänderungen

Bei numerischen Typen wie decimal und numeric, Änderungen an Genauigkeit oder Skalierung lösen auch die Neuauswertung aus:

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 erstellt unterschiedliche Ausführungspläne für unterschiedliche Genauigkeits- und Skalierungskombinationen, da sich Genauigkeit und Skalierung darauf auswirken, wie das Datenbankmodul die Abfrage verarbeitet.

Bewährte Methoden für die Parameterverwendung

Um die Leistung vorbereiteter Anweisungen zu maximieren, befolgen Sie die folgenden bewährten Verfahren:

Parametertypen explizit angeben

Verwenden Sie nach Möglichkeit die expliziten Settermethoden, die ihren Datenbankspaltentypen entsprechen:

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

Verwenden konsistenter Parametermetadaten

Beibehalten einer konsistenten Genauigkeit und Skalierung für numerische Parameter:

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

Grundlegendes zum Runden von Daten mit numerischen Typen

Die Verwendung falscher Genauigkeit und Skalierung für numerische Parameter kann zu unbeabsichtigten Datenrundungen führen. Die Genauigkeit und Skalierung muss sowohl für den Parameterwert als auch für die Verwendung in der SQL-Anweisung geeignet sein.

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

Sie benötigen zwar eine angemessene Genauigkeit und Skalierung für Ihre Daten, vermeiden Sie jedoch, diese Werte für jede Ausführung einer vorbereiteten Anweisung zu ändern. Jede Änderung der Genauigkeit oder Skalierung führt dazu, dass die Anweisung auf dem Server neu vorbereitet wird, wodurch die Leistungsvorteile von vorbereiteten Anweisungen negiert werden.

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

So stimmen Sie Korrektheit und Leistung ab:

1. Ermitteln Sie die geeignete Genauigkeit und Skalierung für Ihre Geschäftlichen Anforderungen.
2. Normalisieren Sie alle Parameterwerte, um konsistente Genauigkeit und Skalierung zu verwenden.
3. Verwenden Sie explizite Rundungsmodi, um zu steuern, wie Werte angepasst werden.
4. Überprüfen Sie, ob Ihre normalisierten Werte den Zielspaltendefinitionen entsprechen.

Hinweis

Sie können die calcBigDecimalPrecision Verbindungsoption verwenden, um Parametergenauigkeiten automatisch zu optimieren. Wenn diese Option aktiviert ist, berechnet der Treiber die für jeden BigDecimal-Wert erforderliche Mindestgenauigkeit, was dazu beiträgt, unnötige Rundungen zu vermeiden. Dieser Ansatz kann jedoch mehr Statement-Prepares verursachen, wenn sich die Daten ändern, da unterschiedliche Genauigkeitswerte zu einer erneuten Vorbereitung führen. Die manuelle Definition der optimalen Genauigkeit und Skalierung in Ihrem Anwendungscode ist die beste Option, da sie sowohl die Datengenauigkeit als auch die konsistente Wiederverwendung von Anweisungen ermöglicht.

Vermeiden des Mischens von Parametereinstellungsmethoden

Wechseln Sie nicht zwischen verschiedenen Settermethoden für dieselbe Parameterposition über verschiedene Ausführungen hinweg.

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

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

Verwenden von setNull() mit expliziten Typen

Wenn Sie Nullwerte festlegen, geben Sie den SQL-Typ an, um die Konsistenz beizubehalten:

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

Überwachen der parameterbezogenen Leistung

Erkennen von Problemen bei der Neubesetzung

So ermitteln Sie, ob Parameteränderungen Leistungsprobleme verursachen:

1. Verwenden Sie SQL Server Profiler oder Erweiterte Ereignisse, um SP:CacheMiss- und SP:Recompile-Ereignisse zu überwachen.
2. Überprüfen Sie die sys.dm_exec_cached_plans DMV, um die Wiederverwendung des Plans zu kontrollieren.
3. Analysieren Sie Abfrageleistungsmetriken, um Anweisungen mit häufigen Wiederholungen zu identifizieren.

Beispielabfrage zum Überprüfen der Planwiederverwendung:

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;

Leistungsindikatoren

Überwachen Sie die folgenden SQL Server-Leistungsindikatoren:

• SQL-Statistik: SQL Rekompilierung/Sek . Zeigt, wie oft Anweisungen neu kompiliert werden.
• SQL-Statistik: SQL-Kompilierungen/Sek . Zeigt, wie oft neue Pläne erstellt werden.
• Plan-Cache: Cache-Trefferquote – Gibt an, wie effektiv Pläne wiederverwendet werden.

Weitere Informationen zu den Zählern und ihrer Interpretation finden Sie unter SQL Server, Plan Cache-Objekt.

Erweiterte Überlegungen

Parametrisierte Abfragen und Plancache-Verschmutzung

Die Plan-Cache-Verschmutzung tritt auf, wenn unterschiedliche Dezimal- oder numerische Präzision dazu führt, dass SQL Server mehrere Ausführungspläne für dieselbe Abfrage erstellt. Dieses Problem verschwendet Arbeitsspeicher und reduziert die Planwiederverwendungseffizienz:

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

Um die Plan-Cache-Verschmutzung zu vermeiden, behalten Sie für numerische Parameter eine konsequente Genauigkeit und Skalierung bei.

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

Zeichenfolgenlängen- und Ganzzahlwertvariationen verursachen keine Plancacheverschmutzung – nur Genauigkeits- und Skalenänderungen für numerische Datentypen verursachen dieses Problem.

Eigenschaften der Verbindungszeichenfolge

Der JDBC-Treiber bietet Verbindungseigenschaften, die das Verhalten und die Leistung von Prepared Statements beeinflussen.

• enablePrepareOnFirstPreparedStatementCall - (Standard: false) Steuert, ob der Treiber sp_prepexec bei der ersten oder zweiten Ausführung aufruft. Die Vorbereitung auf die erste Ausführung verbessert die Leistung leicht, wenn eine Anwendung dieselbe vorbereitete Anweisung mehrmals ausführt. Die Vorbereitung auf die zweite Ausführung verbessert die Leistung von Anwendungen, die meist einmal vorbereitete Anweisungen ausführen. Mit dieser Strategie wird die Notwendigkeit eines separaten Aufrufs aufgehoben, wenn die vorbereitete Anweisung nur einmal ausgeführt wird.
• prepareMethod - (Standard: prepexec) Gibt das Verhalten an, das für die Vorbereitung verwendet werden soll (prepare oder prepexec). Das Festlegen prepareMethod auf prepare resultiert in einem separaten, anfänglichen Zugriff auf die Datenbank, um die Anweisung vorzubereiten, ohne dass Anfangswerte im Ausführungsplan berücksichtigt werden. Stellen Sie prepexec ein, um sp_prepexec als Vorbereitungsmethode zu verwenden. Diese Methode kombiniert die Vorbereiten-Aktion mit der ersten Ausführung, wodurch Netzwerk-Roundtrips reduziert werden. Außerdem stellt sie die Datenbank mit anfänglichen Parameterwerten bereit, die die Datenbank im Ausführungsplan berücksichtigen kann. Je nachdem, wie Ihre Indizes optimiert sind, kann eine Einstellung besser als die andere abschneiden.
• serverPreparedStatementDiscardThreshold - (Standard: 10) Steuert die Batchverarbeitung von sp_unprepare-Vorgängen. Diese Option kann die Leistung verbessern, indem sp_unprepare-Aufrufe gebündelt werden. Ein höherer Wert führt dazu, dass vorbereitete Anweisungen länger auf dem Server verbleiben.

Weitere Informationen zum Festlegen der Verbindungseigenschaften finden Sie unter Festlegen von Verbindungseigenschaften.

Zusammenfassung

So optimieren Sie die Leistung vorbereiteter Statements für Parameter:

1. Verwenden Sie explizite Settermethoden, die ihren Datenbankspaltentypen entsprechen.
2. Behalten Sie Parametermetadaten (Typ, Genauigkeit, Skalierung, Länge) für alle Ausführungen konsistent.
3. Wechseln Sie nicht zwischen verschiedenen Settermethoden für denselben Parameter.
4. Geben Sie SQL-Typen explizit an, wenn Sie verwenden setObject oder setNull.
5. Verwenden Sie vorbereitete Anweisungen wieder, anstatt neue zu erstellen.
6. Überwachen Sie die Plancachestatistiken, um Wiederaufbereitungsprobleme zu identifizieren.
7. Berücksichtigen Sie Verbindungseigenschaften, die sich auf die Leistung vorbereiteter Anweisungen auswirken.

Indem Sie diesen Vorgehensweisen folgen, minimieren Sie die serverseitige Neuausbereitung und erhalten den leistungsstärkeren Nutzen von vorbereiteten Anweisungen.

Siehe auch

Zwischenspeichern von Metadaten vorbereiteter Anweisungen für den JDBC-Treiber
Verbessern von Leistung und Zuverlässigkeit mit dem JDBC-Treiber
Festlegen von Verbindungseigenschaften