Performa parameter pernyataan yang disiapkan untuk driver JDBC

Unduh driver JDBC

Artikel ini menjelaskan bagaimana parameter pernyataan yang disiapkan memengaruhi performa sisi server di Microsoft JDBC Driver untuk SQL Server dan memberikan panduan tentang mengoptimalkan penggunaan parameter.

Memahami parameter pernyataan yang disiapkan

Pernyataan yang disiapkan menawarkan manfaat performa yang signifikan dengan memungkinkan SQL Server mengurai, mengkompilasi, dan mengoptimalkan kueri sekali, lalu menggunakan kembali rencana eksekusi beberapa kali. Namun, cara Anda menentukan parameter dapat secara signifikan memengaruhi manfaat performa ini.

Saat Anda membuat pernyataan yang disiapkan, SQL Server menghasilkan rencana eksekusi berdasarkan metadata parameter, termasuk:

  • Jenis data
  • Presisi (untuk jenis numerik)
  • Skala (untuk jenis desimal)
  • Panjang maksimum (untuk string dan jenis biner)

Metadata ini sangat penting karena SQL Server menggunakannya untuk mengoptimalkan rencana eksekusi kueri. Perubahan pada salah satu karakteristik parameter ini dapat memaksa SQL Server untuk membuang rencana yang ada dan membuat yang baru, yang menghasilkan penalti performa.

Bagaimana perubahan parameter memengaruhi performa

Perubahan jenis parameter

Ketika jenis parameter pernyataan yang disiapkan berubah di antara eksekusi, SQL Server harus mereprepare pernyataan. Repreparasi ini meliputi:

  1. Mengurai pernyataan SQL lagi.
  2. Mengkompilasi rencana eksekusi baru.
  3. Penembolokan rencana baru (jika penembolokan diaktifkan).

Pertimbangkan contoh berikut:

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

Dalam skenario ini, beralih dari setInt ke setString mengubah jenis parameter dari int ke varchar, yang memaksa SQL Server untuk mereprepare pernyataan.

Perubahan presisi dan skala

Untuk jenis numerik seperti decimal dan numeric, perubahan presisi atau skala juga memicu repreparasi:

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 membuat rencana eksekusi yang berbeda untuk kombinasi presisi dan skala yang berbeda karena presisi dan skala memengaruhi cara mesin database memproses kueri.

Praktik terbaik untuk penggunaan parameter

Untuk memaksimalkan performa pernyataan yang disiapkan, ikuti praktik terbaik berikut:

Tentukan jenis parameter secara eksplisit

Jika memungkinkan, gunakan metode setter eksplisit yang cocok dengan jenis kolom database Anda:

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

Menggunakan metadata parameter yang konsisten

Pertahankan presisi dan skala yang konsisten untuk parameter numerik:

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

Memahami pembulatan data dengan tipe numerik

Menggunakan presisi dan skala yang salah untuk parameter numerik dapat mengakibatkan pembulatan data yang tidak diinginkan. Presisi dan skala harus sesuai untuk nilai parameter dan di mana itu digunakan dalam pernyataan 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

Meskipun Anda memerlukan presisi dan skala yang sesuai untuk data Anda, hindari mengubah nilai-nilai ini untuk setiap eksekusi pernyataan yang disiapkan. Setiap perubahan presisi atau skala menyebabkan pernyataan disiapkan ulang di server, menghilangkan keuntungan performa dari pernyataan yang telah disiapkan.

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

Untuk menyeimbangkan kebenaran dan performa:

  1. Tentukan presisi dan skala yang sesuai untuk kebutuhan bisnis Anda.
  2. Menormalkan semua nilai parameter untuk menggunakan presisi dan skala yang konsisten.
  3. Gunakan mode pembulatan eksplisit untuk mengontrol bagaimana nilai disesuaikan.
  4. Validasi bahwa nilai yang dinormalisasi cocok dengan definisi kolom target.

Nota

Anda dapat menggunakan calcBigDecimalPrecision opsi koneksi untuk mengoptimalkan presisi parameter secara otomatis. Saat diaktifkan, driver menghitung presisi minimum yang diperlukan untuk setiap nilai BigDecimal, yang membantu menghindari pembulatan yang tidak perlu. Namun, pendekatan ini mungkin menimbulkan lebih banyak persiapan pernyataan saat data berubah karena nilai presisi yang berbeda menyebabkan persiapan ulang. Menentukan presisi dan skala optimal secara manual dalam kode aplikasi Anda adalah opsi terbaik jika memungkinkan, karena memberikan akurasi data dan penggunaan kembali pernyataan yang konsisten.

Hindari mencampur metode pengaturan parameter

Jangan beralih di antara metode setter yang berbeda untuk posisi parameter yang sama di seluruh eksekusi:

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

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

Menggunakan setNull() dengan jenis eksplisit

Saat Anda mengatur nilai null, tentukan jenis SQL untuk mempertahankan konsistensi:

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

Mendeteksi masalah persiapan ulang

Untuk mengidentifikasi apakah perubahan parameter menyebabkan masalah performa:

  1. Gunakan SQL Server Profiler atau Extended Events untuk memantau SP:CacheMiss dan SP:Recompile peristiwa.
  2. Periksa sys.dm_exec_cached_plans DMV untuk memeriksa perencanaan ulang.
  3. Analisis metrik performa kueri untuk mengidentifikasi pernyataan dengan repreparasi yang sering.

Contoh kueri untuk memeriksa penggunaan kembali rencana:

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;

Penghitung kinerja

Pantau penghitung kinerja SQL Server ini:

  • Statistik SQL: Kompilasi Ulang SQL/detik - Menunjukkan seberapa sering pernyataan dikompilasi ulang.
  • Statistik SQL: Kompilasi SQL/detik - Menunjukkan seberapa sering rencana baru dibuat.
  • Cache Rencana: Rasio Hit Cache - Menunjukkan seberapa efektif rencana digunakan kembali.

Untuk detail selengkapnya tentang penghitung dan cara menginterpretasikannya, lihat SQL Server, Objek Cache Rencana.

Pertimbangan tingkat lanjut

Kueri berparameter dan merencanakan polusi cache

Polusi cache pada plan terjadi ketika berbagai presisi desimal atau numerik menyebabkan SQL Server membuat beberapa eksekusi plan untuk kueri yang sama. Masalah ini membuang memori dan mengurangi efisiensi reusabilitas rencana.

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

Untuk menghindari polusi pada cache rencana, jaga presisi dan skala yang konsisten untuk parameter numerik.

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

Panjang string dan variasi nilai bilangan bulat tidak menyebabkan polusi cache rencana—hanya perubahan presisi dan skala untuk jenis numerik yang membuat masalah ini.

Properti string koneksi

Driver JDBC menyediakan properti koneksi yang memengaruhi perilaku dan performa pernyataan yang disiapkan:

  • enablePrepareOnFirstPreparedStatementCall - (Default: false) Mengontrol apakah driver memanggil sp_prepexec pada eksekusi pertama atau kedua. Mempersiapkan eksekusi pertama sedikit meningkatkan performa jika aplikasi secara konsisten menjalankan pernyataan yang disiapkan yang sama beberapa kali. Mempersiapkan eksekusi kedua meningkatkan performa aplikasi yang sebagian besar menjalankan pernyataan yang disiapkan sekali. Strategi ini menghilangkan kebutuhan akan panggilan terpisah untuk membatalkan persiapan jika pernyataan yang dipersiapkan hanya dijalankan sekali.
  • prepareMethod - (Default: prepexec) Menentukan perilaku yang akan digunakan untuk persiapan (prepare atau prepexec). Pengaturan prepareMethod ke prepare menghasilkan permintaan awal yang terpisah ke database untuk menyiapkan perintah SQL tanpa nilai awal yang dipertimbangkan oleh database dalam rencana pelaksanaan. Atur ke prepexec untuk digunakan sp_prepexec sebagai metode persiapan. Metode ini menggabungkan tindakan persiapan dengan eksekusi pertama, mengurangi perjalanan bolak-balik jaringan. Ini juga menyediakan database dengan nilai parameter awal yang dapat dipertimbangkan database dalam rencana eksekusi. Bergantung pada bagaimana indeks Anda dioptimalkan, satu pengaturan dapat berkinerja lebih baik daripada yang lain.
  • serverPreparedStatementDiscardThreshold - (Default: 10) Mengontrol pembentukan batch dari operasi sp_unprepare. Opsi ini dapat meningkatkan performa dengan menggabungkan panggilan sp_unprepare. Nilai yang lebih tinggi membuat pernyataan yang telah disiapkan berlama-lama di server.

Untuk informasi selengkapnya, lihat Mengatur properti koneksi.

Ringkasan

Untuk mengoptimalkan performa pernyataan yang disiapkan untuk parameter:

  1. Gunakan metode setter eksplisit yang cocok dengan jenis kolom database Anda.
  2. Menjaga metadata parameter (jenis, presisi, skala, panjang) konsisten di seluruh eksekusi.
  3. Jangan beralih antara metode setter yang berbeda untuk parameter yang sama.
  4. Tentukan jenis SQL secara eksplisit saat Anda menggunakan setObject atau setNull.
  5. Gunakan kembali pernyataan yang disiapkan alih-alih membuat pernyataan baru.
  6. Pantau statistik cache rencana untuk mengidentifikasi masalah repreparasi.
  7. Pertimbangkan properti koneksi yang memengaruhi performa pernyataan yang disiapkan.

Dengan mengikuti praktik ini, Anda meminimalkan persiapan ulang sisi server dan mendapatkan manfaat kinerja maksimum dari pernyataan yang disiapkan.

Lihat juga

Penyimpanan sementara metadata pernyataan yang telah disiapkan untuk driver JDBC
Meningkatkan performa dan keandalan dengan driver JDBC
Mengatur properti koneksi

  • Last updated on