Výkon parametrů připravených příkazů pro ovladač JDBC

Stáhnout ovladač JDBC

Tento článek vysvětluje, jak připravené parametry příkazu ovlivňují výkon na straně serveru v ovladači Microsoft JDBC pro SQL Server a poskytuje pokyny k optimalizaci využití parametrů.

Vysvětlení parametrů připravených příkazů

Připravené příkazy nabízejí významné výhody výkonu tím, že SQL Serveru umožní analyzovat, kompilovat a optimalizovat dotaz jednou a pak opakovaně použít plán provádění. Způsob, jakým zadáte parametry, ale může výrazně ovlivnit tuto výhodu výkonu.

Když vytvoříte připravený příkaz, SQL Server vygeneruje plán provádění na základě metadat parametrů, včetně:

  • Datový typ
  • Přesnost (pro číselné typy)
  • Měřítko (pro desetinné typy)
  • Maximální délka (pro řetězcové a binární typy)

Tato metadata jsou zásadní, protože SQL Server ho používá k optimalizaci plánu provádění dotazů. Změny některé z těchto charakteristik parametrů můžou vynutit, aby SQL Server zahodil existující plán a vytvořil nový, což má za následek snížení výkonu.

Vliv změn parametrů na výkon

Změny typu parametru

Když se mezi spuštěními změní typ parametru připraveného příkazu, sql Server musí příkaz znovu připravit. Tato opětovná příprava zahrnuje:

  1. Opětovná analýza příkazu SQL
  2. Kompilace nového plánu provádění
  3. Ukládání nového plánu do mezipaměti (pokud je povoleno ukládání do mezipaměti).

Představte si následující příklad:

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

V tomto scénáři přepnutím z setInt na setString se změní typ parametru z int na varchar, což přinutí SQL Server znovu připravit příkaz.

Změny přesnosti a škálování

U číselných typů jako decimal a numericzměny přesnosti nebo škálování také aktivují opětovné přípravy:

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 vytváří různé plány provádění pro různé kombinace přesnosti a škálování, protože přesnost a škálování ovlivňují, jak databázový stroj zpracovává dotaz.

Osvědčené postupy pro použití parametrů

Pokud chcete maximalizovat výkon připravených příkazů, postupujte podle těchto osvědčených postupů:

Explicitní zadání typů parametrů

Pokud je to možné, použijte explicitní metody setter, které odpovídají typům sloupců databáze:

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

Použití konzistentních metadat parametrů

Zachování konzistentní přesnosti a měřítka pro číselné parametry:

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

Principy zaokrouhlení dat pomocí číselných typů

Použití nesprávné přesnosti a škály číselných parametrů může vést k nezamýšleným zaokrouhlením dat. Přesnost a měřítko musí být vhodné jak pro hodnotu parametru, tak pro místo, kde se používá v příkazu 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

I když potřebujete odpovídající přesnost a škálování dat, vyhněte se změnám těchto hodnot pro každé spuštění připraveného příkazu. Každá změna přesnosti nebo škálování způsobí, že se příkaz znovu připraví na serveru a neguje výhody výkonu připravených příkazů.

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

Vyvážení správnosti a výkonu:

  1. Určete odpovídající přesnost a škálování pro vaše obchodní požadavky.
  2. Normalizujte všechny hodnoty parametrů, aby měly konzistentní přesnost a měřítko.
  3. Pomocí explicitních režimů zaokrouhlení můžete řídit, jak se hodnoty upraví.
  4. Ověřte, že normalizované hodnoty odpovídají definicům cílového sloupce.

Poznámka:

Pomocí možnosti připojení calcBigDecimalPrecision můžete automaticky optimalizovat přesnost parametrů. Pokud je tato možnost povolená, ovladač vypočítá minimální přesnost potřebnou pro každou hodnotu BigDecimal, což pomáhá vyhnout se zbytečnému zaokrouhlení. Tento přístup ale může způsobit častější přípravy příkazů, jakmile se data změní, protože různé hodnoty přesnosti mohou vést k opětovné přípravě. Ruční definování optimální přesnosti a škálování v kódu aplikace je nejlepší v případě potřeby, protože poskytuje přesnost dat i konzistentní opakované použití příkazů.

Vyhněte se kombinování metod nastavení parametrů

Nepřepínejte mezi různými metodami setter pro stejnou pozici parametru napříč prováděními:

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

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

Použití setNull() s jejími explicitními typy

Při nastavování hodnot null zadejte typ SQL pro zachování konzistence:

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

Zjišťování problémů s opětovnou přípravou

Pokud chcete zjistit, jestli změny parametrů způsobují problémy s výkonem:

  1. K monitorování událostí SP:CacheMiss a SP:Recompile použijte SQL Server Profiler nebo rozšířené události.
  2. sys.dm_exec_cached_plans Zkontrolujte zobrazení dynamické správy a zkontrolujte opakované použití plánu.
  3. Analyzujte metriky výkonu dotazů a identifikujte příkazy s častou přepracovatelností.

Příklad dotazu pro kontrolu opakovaného použití plánu:

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;

Čítače výkonu

Monitorujte tyto čítače výkonu SQL Serveru:

  • Statistika SQL: Opakované kompilace SQL za sekundu – ukazuje, jak často se příkazy rekompilují.
  • Statistika SQL: Kompilace SQL za sekundu – ukazuje, jak často se vytvářejí nové plány.
  • Mezipaměť plánů: Poměr přístupů do mezipaměti – označuje, jak efektivně se plány znovu používají.

Další podrobnosti o čítačích a jejich interpretaci naleznete v tématu SQL Server, Plan Cache objekt.

Pokročilé aspekty

Parametrizované dotazy a znečištění mezipaměti plánů

K znečištění vyrovnávací paměti plánu dochází, když různá desetinná nebo číselná přesnost způsobí, že SQL Server vytvoří pro stejný dotaz více výkonových plánů. Tento problém ztrácí paměť a snižuje efektivitu opakovaného použití plánu:

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

Abyste se vyhnuli znečištění plánové mezipaměti, udržujte konzistentní přesnost a škálu pro numerické parametry:

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

Varianty délky řetězců a celočíselných hodnot nezpůsobují znečištění mezipaměti plánu – tento problém vytvářejí pouze změny přesnosti a škálování číselných typů.

Vlastnosti připojovacího řetězce

Ovladač JDBC poskytuje vlastnosti připojení, které ovlivňují chování a výkon připravených příkazů:

  • enablePrepareOnFirstPreparedStatementCall – (Default: false) Řídí, zda ovladač volá sp_prepexec při prvním nebo druhém spuštění. Příprava na první spuštění mírně zvyšuje výkon, pokud aplikace konzistentně provádí stejný připravený příkaz několikrát. Příprava na druhé spuštění zlepšuje výkon aplikací, které většinou spouští připravené příkazy jednou. Tato strategie eliminuje potřebu samostatného volání pro zrušení přípravy, pokud je připravený příkaz proveden pouze jednou.
  • prepareMethod - (Výchozí: prepexec) Určuje chování, které se má použít pro přípravu (prepare nebo prepexec). Nastavení prepareMethod na prepare způsobí samostatný, počáteční dotaz do databáze za účelem přípravy příkazu bez jakýchkoliv počátečních hodnot pro zvážení v plánu provádění. Nastavte na prepexec, aby se sp_prepexec použila jako metoda přípravy. Tato metoda kombinuje akci přípravy s prvním spuštěním, čímž se snižuje počet cestování po síti. Poskytuje také databázi s počátečními hodnotami parametrů, které může databáze zvážit v plánu provádění. V závislosti na tom, jak jsou indexy optimalizované, může jedno nastavení fungovat lépe než druhé.
  • serverPreparedStatementDiscardThreshold – (Výchozí: 10) Řídí dávkování sp_unprepare operací. Tato možnost může zvýšit výkon tím, že seskupí volání dohromady. sp_unprepare Vyšší hodnota ponechá připravené příkazy na serveru delší dobu.

Další informace naleznete v tématu Nastavení vlastností připojení.

Shrnutí

Optimalizování výkonu připravených příkazů pro parametry:

  1. Použijte explicitní metody setter, které odpovídají typům sloupců databáze.
  2. Zachovejte metadata parametrů (typ, přesnost, měřítko, délku) konzistentní napříč prováděními.
  3. Nepřepínejte mezi různými metodami setter pro stejný parametr.
  4. Explicitně zadejte typy SQL při použití setObject nebo setNull.
  5. Opakovaně používejte připravené příkazy místo vytváření nových příkazů.
  6. Monitorujte statistiky mezipaměti plánu a identifikujte problémy s opětovnou přípravou.
  7. Zvažte vlastnosti připojení, které ovlivňují výkon připravených příkazů.

Dodržováním těchto postupů minimalizujete opětovnou přípravu na straně serveru a získáte z připravených příkazů maximální výhody z výkonu.

Viz také

Ukládání metadat připravených příkazů do mezipaměti pro ovladač JDBC
zvýšení výkonu a spolehlivosti pomocí ovladače JDBC
Nastavení vlastností připojení

  • Last updated on