Cara menggunakan batching untuk meningkatkan performa aplikasi Azure SQL Database dan Azure SQL Managed Instance
Berlaku untuk:
Azure SQL DatabaseAzure SQL Managed Instance
Operasi batching ke Azure SQL Database dan Azure SQL Managed Instance secara signifikan meningkatkan kinerja dan skalabilitas aplikasi Anda. Untuk memahami manfaatnya, bagian pertama dari artikel ini mencakup beberapa hasil pengujian sampel yang membandingkan permintaan berurutan dan batched ke database di Azure SQL Database atau Azure SQL Managed Instance. Bagian artikel selanjutnya menunjukkan teknik, skenario, dan pertimbangan untuk membantu Anda menggunakan batching dengan sukses di aplikasi Azure Anda.
Mengapa batching penting untuk Azure SQL Database dan Azure SQL Managed Instance?
Panggilan batching ke layanan jarak jauh adalah strategi yang terkenal dapat meningkatkan kinerja dan skalabilitas. Ada biaya pemrosesan tetap untuk setiap interaksi dengan layanan jarak jauh, seperti serialisasi, transfer jaringan, dan deserialisasi. Mengemas banyak transaksi terpisah menjadi satu batch meminimalkan biaya ini.
Dalam artikel ini, akan dijelaskan mengenai berbagai strategi dan skenario batching. Meskipun strategi ini juga penting untuk aplikasi lokal yang menggunakan SQL Server, ada beberapa alasan untuk menyoroti penggunaan batching untuk Azure SQL Database dan Azure SQL Managed Instance:
- Ada potensi latensi jaringan yang lebih besar dalam mengakses Azure SQL Database dan Azure SQL Managed Instance, terutama jika Anda mengakses Azure SQL Database atau Azure SQL Managed Instance dari luar pusat data Microsoft Azure yang sama.
- Karakteristik multitenan Azure SQL Database dan Azure SQL Managed Instance berarti bahwa efisiensi lapisan akses data berkorelasi dengan skalabilitas keseluruhan database. Jika ada penggunaan yang melebihi kuota yang telah ditentukan sebelumnya, Azure SQL Database dan Azure SQL Managed Instance dapat mengurangi throughput atau merespons dengan pengecualian pembatasan. Efisiensi, seperti batching, memungkinkan Anda untuk melakukan lebih banyak pekerjaan sebelum mencapai batas ini.
- Batching juga efektif untuk arsitektur yang menggunakan beberapa database (sharding). Efisiensi interaksi Anda dengan setiap unit database masih menjadi faktor utama dalam skalabilitas Anda secara keseluruhan.
Salah satu manfaat penggunaan Azure SQL Database atau Azure SQL Managed Instance adalah Anda tidak perlu mengelola server yang menghosting database. Namun, dengan penggunaan infrastruktur yang dikelola ini juga berarti bahwa pengoptimalan database akan berbeda. Anda tidak bisa lagi meningkatkan perangkat keras database atau infrastruktur jaringan. Microsoft Azure mengontrol lingkungan tersebut. Area utama yang dapat Anda kontrol adalah bagaimana aplikasi Anda berinteraksi dengan Azure SQL Database dan Azure SQL Managed Instance. Batching adalah salah satu pengoptimalan ini.
Bagian pertama dari artikel ini menjelaskan berbagai teknik batching untuk aplikasi .NET yang menggunakan Azure SQL Database atau Azure SQL Managed Instance. Dua bagian terakhir mencakup pedoman dan skenario batching.
Strategi batching
Catatan tentang hasil pengaturan waktu dalam artikel ini
Catatan
Hasil bukan merupakan tolok ukur tetapi dimaksudkan untuk menunjukkan performa relatif. Waktu didasarkan pada rata-rata setidaknya 10 uji coba. Operasi merupakan sisipan ke dalam tabel kosong. Tes ini diukur pre-V12, dan tidak selalu sesuai dengan throughput yang mungkin Anda alami dalam database V12 menggunakan tingkat layanan DTU baru atau tingkat layanan vCore. Manfaat relatif dari teknik batching seharusnya sama.
Transaksi
Memulai ulasan batching dengan membahas transaksi mungkin tampak aneh. Tetapi penggunaan transaksi dari sisi klien memiliki sedikit efek batching sisi server yang meningkatkan performa. Selain itu, transaksi dapat ditambahkan hanya dengan beberapa baris kode, sehingga transaksi tersebut memberikan cara yang cepat untuk meningkatkan performa operasi berurutan.
Pertimbangkan kode C# berikut yang berisi urutan operasi sisipkan dan perbarui pada tabel sederhana.
List<string> dbOperations = new List<string>();
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 1");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 2");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 3");
dbOperations.Add("insert MyTable values ('new value',1)");
dbOperations.Add("insert MyTable values ('new value',2)");
dbOperations.Add("insert MyTable values ('new value',3)");
Kode ADO.NET berikut secara berurutan melakukan operasi ini.
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
conn.Open();
foreach(string commandString in dbOperations)
{
SqlCommand cmd = new SqlCommand(commandString, conn);
cmd.ExecuteNonQuery();
}
}
Cara terbaik untuk mengoptimalkan kode ini adalah dengan menerapkan beberapa bentuk batching sisi klien dari panggilan ini. Namun ada cara sederhana untuk meningkatkan performa kode ini dengan hanya menyatukan urutan panggilan dalam sebuah transaksi. Berikut ini adalah kode yang sama yang menggunakan transaksi.
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
conn.Open();
SqlTransaction transaction = conn.BeginTransaction();
foreach (string commandString in dbOperations)
{
SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
Sebenarnya, transaksi digunakan dalam kedua contoh ini. Dalam contoh pertama, setiap panggilan individu merupakan transaksi implisit. Dalam contoh kedua, transaksi eksplisit meliputi semua panggilan. Untuk dokumentasi log transaksi write-ahead, catatan log dialihkan ke disk ketika transaksi dilakukan. Maka, dengan memasukkan lebih banyak panggilan dalam transaksi, penulisan ke log transaksi dapat menunda sampai transaksi dilakukan. Efeknya, batching untuk penulisan ke log transaksi server diaktifkan.
Tabel berikut ini memperlihatkan beberapa hasil pengujian ad hoc. Pengujian ini melakukan sisipan berurutan yang sama dengan transaksi dan tanpa transaksi. Untuk perspektif lebih lanjut, set pengujian pertama berjalan dari jarak jauh dari laptop ke database di Microsoft Azure. Set pengujian kedua berjalan dari layanan cloud dan database yang keduanya berada dalam pusat data Microsoft Azure (WEST US) yang sama. Tabel berikut menunjukkan durasi dalam milidetik dari sisipan berurutan dengan transaksi dan tanpa transaksi.
Lokal ke Azure:
| Operasional | Tanpa transaksi (milidetik) | Dengan transaksi (milidetik) |
|---|---|---|
| 1 | 130 | 402 |
| 10 | 1208 | 1226 |
| 100 | 12662 | 10395 |
| 1000 | 128852 | 102917 |
Azure ke Azure (pusat data yang sama):
| Operasional | Tanpa transaksi (milidetik) | Dengan transaksi (milidetik) |
|---|---|---|
| 1 | 21 | 26 |
| 10 | 220 | 56 |
| 100 | 2145 | 341 |
| 1000 | 21479 | 2756 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Berdasarkan hasil pengujian sebelumnya, menggabungkan operasi tunggal dalam sebuah transaksi sebenarnya dapat menurunkan performa. Namun ketika Anda meningkatkan jumlah operasi dalam satu transaksi, peningkatan performa menjadi lebih menonjol. Perbedaan performa juga lebih terlihat ketika semua operasi terjadi dalam pusat data Microsoft Azure. Peningkatan latensi penggunaan Azure SQL Database atau Azure SQL Managed Instance dari luar pusat data Microsoft Azure melebihi perolehan performa penggunaan transaksi.
Meskipun penggunaan transaksi dapat meningkatkan performa, tetap amati praktik terbaik untuk transaksi dan koneksi. Buat transaksi sesingkat mungkin, dan tutup koneksi database setelah pekerjaan selesai. Pernyataan penggunaan dalam contoh sebelumnya memastikan bahwa koneksi tertutup ketika blok kode berikutnya selesai.
Contoh sebelumnya menunjukkan bahwa Anda dapat menambahkan transaksi lokal ke kode ADO.NET apa pun dengan dua baris. Transaksi memberikan cara cepat untuk meningkatkan performa kode yang membuat sisipan berurutan, pembaruan, dan penghapusan operasi. Namun, untuk performa tercepat, pertimbangkan untuk mengubah kode lebih lanjut untuk memanfaatkan batching sisi klien, seperti parameter bernilai tabel.
Untuk informasi lebih lanjut tentang transaksi di ADO.NET, lihat Transaksi Lokal di ADO.NET.
Parameter bernilai tabel
Parameter bernilai tabel mendukung tipe tabel yang ditentukan pengguna sebagai parameter dalam pernyataan T-SQL, prosedur tersimpan, dan fungsi. Teknik batching sisi klien ini memungkinkan Anda untuk mengirim beberapa baris data dalam parameter bernilai tabel. Untuk menggunakan parameter bernilai tabel, pertama-tama tentukan tipe tabel. Pernyataan T-SQL berikut ini membuat tipe tabel bernama MyTableType.
CREATE TYPE MyTableType AS TABLE
( mytext TEXT,
num INT );
Dalam kode, Anda membuat DataTable dengan nama dan tipe tabel yang sama persis. Teruskan DataTable di parameter dalam sebuah kueri teks atau panggilan prosedur tersimpan. Contoh berikut menunjukkan teknik ini:
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
connection.Open();
DataTable table = new DataTable();
// Add columns and rows. The following is a simple example.
table.Columns.Add("mytext", typeof(string));
table.Columns.Add("num", typeof(int));
for (var i = 0; i < 10; i++)
{
table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
}
SqlCommand cmd = new SqlCommand(
"INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
connection);
cmd.Parameters.Add(
new SqlParameter()
{
ParameterName = "@TestTvp",
SqlDbType = SqlDbType.Structured,
TypeName = "MyTableType",
Value = table,
});
cmd.ExecuteNonQuery();
}
Dalam contoh sebelumnya, objek SqlCommand menyisipkan baris dari parameter bernilai tabel, @TestTvp. Objek DataTable yang dibuat sebelumnya ditetapkan ke parameter ini dengan metode SqlCommand.Parameters.Add. Membuat batch untuk sisipan dalam satu panggilan secara signifikan meningkatkan performa di atas sisipan berurutan.
Untuk lebih menjelaskan contoh sebelumnya, gunakan prosedur tersimpan, bukan perintah berbasis teks. Perintah T-SQL berikut ini membuat prosedur tersimpan yang menggunakan parameter bernilai tabel SimpleTestTableType.
CREATE PROCEDURE [dbo].[sp_InsertRows]
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num)
SELECT mytext, num FROM @TestTvp
END
GO
Kemudian ubah deklarasi objek SqlCommand di contoh kode sebelumnya menjadi yang berikut ini.
SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;
Sering kali, parameter bernilai tabel memiliki performa yang setara atau lebih baik daripada teknik batching lainnya. Parameter bernilai tabel sering kali lebih disukai, karena lebih fleksibel daripada pilihan lainnya. Misalnya, teknik lain, seperti salin massal SQL, hanya mengizinkan penyisipan baris baru. Namun dengan parameter bernilai tabel, Anda dapat menggunakan logika dalam prosedur tersimpan untuk menentukan baris mana yang merupakan pembaruan dan mana yang merupakan sisipan. Tipe tabel juga dapat dimodifikasi untuk berisi kolom "Operasi" yang menunjukkan apakah baris yang ditentukan harus disisipkan, diperbarui, atau dihapus.
Tabel berikut ini memperlihatkan hasil pengujian ad hoc untuk penggunaan parameter bernilai tabel dalam milidetik.
| Operasional | Lokal ke Azure (milidetik) | Pusat data yang sama dengan Azure (milidetik) |
|---|---|---|
| 1 | 124 | 32 |
| 10 | 131 | 25 |
| 100 | 338 | 51 |
| 1000 | 2615 | 382 |
| 10000 | 23830 | 3586 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Perolehan performa dari batching segera terlihat. Dalam pengujian berurutan sebelumnya, 1000 operasi membutuhkan waktu 129 detik di luar pusat data dan 21 detik dari dalam pusat data. Tetapi dengan parameter bernilai tabel, 1000 operasi hanya membutuhkan waktu 2,6 detik di luar pusat data dan 0,4 detik dalam pusat data.
Untuk informasi selengkapnya tentang parameter bernilai tabel, lihat Parameter Bernilai Tabel.
Salin massal SQL
Salin massal SQL adalah cara lain untuk menyisipkan data dalam jumlah besar ke dalam database target. Aplikasi .NET dapat menggunakan kelas SqlBulkCopy untuk melakukan operasi sisipan massal. SqlBulkCopy memiliki fungsi yang mirip dengan alat baris perintah, Bcp.exe, atau pernyataan T-SQL, BULK INSERT. Contoh kode berikut ini memperlihatkan cara menyalin baris secara massal di sumber DataTable, tabel, ke tabel tujuan, MyTable.
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "MyTable";
bulkCopy.ColumnMappings.Add("mytext", "mytext");
bulkCopy.ColumnMappings.Add("num", "num");
bulkCopy.WriteToServer(table);
}
}
Dalam beberapa kasus, salin massal lebih disukai daripada parameter bernilai tabel. Lihat tabel perbandingan parameter Table-Valued versus operasi BULK INSERT dalam artikel Parameter Bernilai Tabel.
Hasil pengujian ad hoc berikut menunjukkan performa batching dengan SqlBulkCopy dalam milidetik.
| Operasional | Lokal ke Azure (milidetik) | Pusat data yang sama dengan Azure (milidetik) |
|---|---|---|
| 1 | 433 | 57 |
| 10 | 441 | 32 |
| 100 | 636 | 53 |
| 1000 | 2535 | 341 |
| 10000 | 21605 | 2737 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Dalam ukuran batch yang lebih kecil, penggunaan parameter yang bernilai tabel mengungguli kelas SqlBulkCopy. Namun, SqlBulkCopy tampil 12-31% lebih cepat dari parameter bernilai tabel untuk pengujian 1.000 dan 10.000 baris. Seperti parameter bernilai tabel, SqlBulkCopy adalah pilihan yang baik untuk sisipan batch, terutama jika dibandingkan dengan performa operasi yang tidak dibuat batch.
Untuk informasi selengkapnya tentang salin massal di ADO.NET, lihat Operasi Salin Massal.
Pernyataan INSERT berparameter multi baris
Salah satu alternatif untuk batch kecil adalah dengan membuat pernyataan INSERT berparameter besar yang menyisipkan banyak baris. Contoh kode berikut menunjukkan teknik ini.
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
connection.Open();
string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
"VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)";
SqlCommand cmd = new SqlCommand(insertCommand, connection);
for (int i = 1; i <= 10; i += 2)
{
cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
}
cmd.ExecuteNonQuery();
}
Contoh ini dibuat untuk menunjukkan konsep dasar. Skenario yang lebih realistis akan mengulang entitas yang diperlukan untuk membuat untai (karakter) kueri dan parameter perintah secara bersamaan. Jumlah kueri terbatas pada total 2100 parameter kueri, jadi jumlah total baris yang dapat diproses dengan cara ini juga dibatasi.
Hasil pengujian ad hoc berikut menunjukkan performa jenis pernyataan sisipan ini dalam milidetik.
| Operasional | Parameter bernilai tabel (milidetik) | INSERT pernyataan tunggal (milidetik) |
|---|---|---|
| 1 | 32 | 20 |
| 10 | 30 | 25 |
| 100 | 33 | 51 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Pendekatan ini bisa sedikit lebih cepat untuk batch yang kurang dari 100 baris. Meskipun peningkatannya kecil, teknik ini adalah pilihan lain yang mungkin bekerja dengan baik dalam skenario aplikasi spesifik Anda.
DataAdapter
Kelas DataAdapter memungkinkan Anda mengubah objek DataSet lalu mengirimkan perubahan tersebut sebagai operasi INSERT, UPDATE, dan DELETE. Jika Anda menggunakan DataAdapter dengan cara ini, penting untuk dicatat bahwa panggilan terpisah dibuat untuk setiap operasi yang berbeda. Untuk meningkatkan performa, gunakan properti UpdateBatchSize ke jumlah operasi yang harus dibuat batch pada satu waktu. Untuk informasi selengkapnya, lihat Melakukan Operasi Batch Menggunakan DataAdapters.
Entity Framework
Entity Framework Core mendukung pembuatan batch.
XML
Agar lebih lengkap, XML merupakan hal yang penting sebagai strategi pembuatan batch. Namun, penggunaan XML tidak lebih menguntungkan daripada metode lain dan memiliki beberapa kelemahan. Pendekatan ini mirip dengan parameter bernilai tabel, tetapi file atau untai (karakter) XML diteruskan ke prosedur tersimpan, bukan tabel yang ditentukan pengguna. Prosedur tersimpan memilah perintah dalam prosedur tersimpan.
Ada beberapa kelemahan untuk pendekatan ini:
- Bekerja dengan XML bisa jadi rumit dan rawan kesalahan.
- Memilah XML pada database dapat meningkatkan penggunaan CPU.
- Sering kali, metode ini lebih lambat daripada parameter bernilai tabel.
Karena itu, penggunaan XML untuk kueri batch tidak disarankan.
Pertimbangan pembuatan batch
Bagian berikut ini memberikan panduan lebih lanjut untuk penggunaan batching di Azure SQL Database dan Azure SQL Managed Instance.
Tradeoffs
Tergantung pada arsitektur Anda, pembuatan batch dapat melibatkan tradeoff antara performa dan ketahanan. Misalnya, pertimbangkan skenario ketika peran Anda turun secara tidak terduga. Jika Anda kehilangan satu baris data, dampaknya lebih kecil dari dampak kehilangan batch besar baris yang tidak dikirimkan. Ada risiko yang lebih besar saat Anda mem-buffer baris sebelum mengirimnya ke database dalam batas waktu yang ditentukan.
Karena tradeoff ini, evaluasi jenis operasi yang Anda batch-kan. Batch lebih agresif (batch yang lebih besar dan jendela waktu yang lebih lama) dengan data yang kurang penting.
Ukuran batch
Dalam pengujian kami, biasanya tidak ada keuntungan saat memecahkan batch besar menjadi bagian-bagian yang lebih kecil. Bahkan, memecahnya menjadi bagian-bagian kecil seperti ini sering menghasilkan performa yang lebih lambat daripada mengirimkan satu batch besar. Misalnya, pada skenario saat Anda ingin menyisipkan 1000 baris. Tabel berikut ini memperlihatkan berapa lama waktu yang diperlukan untuk menggunakan parameter bernilai tabel untuk menyisipkan 1000 baris saat dibagi menjadi batch yang lebih kecil.
| Ukuran batch | Perulangan | Parameter bernilai tabel (milidetik) |
|---|---|---|
| 1000 | 1 | 347 |
| 500 | 2 | 355 |
| 100 | 10 | 465 |
| 50 | 20 | 630 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Anda dapat melihat bahwa performa terbaik untuk 1000 baris adalah dengan mengirimkan semuanya sekaligus. Dalam pengujian lain (tidak ditunjukkan di sini), ada perolehan performa yang kecil ketika batch 10000 baris dipecah menjadi dua batch 5000 baris. Namun skema tabel untuk pengujian ini relatif sederhana, jadi Anda harus melakukan pengujian pada data spesifik Anda dan ukuran batch untuk memverifikasi temuan ini.
Faktor lain yang perlu dipertimbangkan adalah bahwa jika total batch terlalu besar, Azure SQL Database atau Azure SQL Managed Instance mungkin akan terbatasi dan menolak untuk melakukan batch. Untuk hasil terbaik, uji skenario spesifik Anda untuk menentukan apakah ada ukuran batch yang ideal. Buat ukuran batch dapat dikonfigurasi saat runtime untuk mengaktifkan penyesuaian cepat berdasarkan performa atau kesalahan.
Terakhir, seimbangkan ukuran batch dengan risiko yang terkait dengan batching. Jika ada kesalahan sementara atau kegagalan peran, pertimbangkan konsekuensi dari mencoba kembali operasi atau kehilangan data dalam batch.
Pemrosesan paralel
Bagaimana jika Anda mencoba mengurangi ukuran batch tetapi menggunakan beberapa alur untuk menjalankan pekerjaan? Sekali lagi, pengujian kami menunjukkan bahwa beberapa batch multi-alur yang lebih kecil biasanya menunjukkan performa yang lebih buruk daripada satu batch yang lebih besar. Pengujian berikut ini mencoba memasukkan 1000 baris dalam satu atau beberapa batch paralel. Pengujian ini menunjukkan bagaimana batch yang lebih simultan benar-benar menurunkan performa.
| Ukuran batch [Perulangan] | Dua utas (milidetik) | Empat utas (milidetik) | Enam utas (milidetik) |
|---|---|---|---|
| 1000 [1] | 277 | 315 | 266 |
| 500 [2] | 548 | 278 | 256 |
| 250 [4] | 405 | 329 | 265 |
| 100 [10] | 488 | 439 | 391 |
Catatan
Hasil bukan tolok ukur. Lihat catatan tentang hasil pengaturan waktu dalam artikel ini.
Ada beberapa alasan yang berpotensi menyebabkan penurunan performa karena paralelisme:
- Ada beberapa panggilan jaringan simultan, bukan satu.
- Beberapa operasi terhadap tabel tunggal dapat mengakibatkan sanggahan dan pemblokiran.
- Ada overhead yang terkait dengan multi-alur.
- Usaha yang harus dilakukan untuk membuka beberapa koneksi lebih besar dari manfaat yang didapatkan dengan pemrosesan paralel.
Jika Anda menargetkan tabel atau database yang berbeda, performa mungkin akan meningkat dengan strategi ini. Database sharding atau federation adalah skenario untuk pendekatan ini. Sharding menggunakan beberapa database dan merutekan data yang berbeda ke setiap database. Jika setiap batch kecil dirutekan ke database yang berbeda, operasi secara paralel bisa dilakukan dengan lebih efisien. Namun, perolehan perfoma tidak cukup signifikan untuk digunakan sebagai dasar pengambilan keputusan untuk menggunakan database sharding sebagai solusi Anda.
Dalam beberapa desain, eksekusi paralel batch yang lebih kecil dapat mengakibatkan peningkatan throughput permintaan dalam sistem yang dimuat. Dalam hal ini, meskipun lebih cepat untuk memproses satu batch yang lebih besar, memproses beberapa batch secara paralel mungkin lebih efisien.
Jika Anda menggunakan eksekusi paralel, pertimbangkan untuk mengontrol jumlah maksimum thread pekerja. Jumlah yang lebih kecil dapat mengakibatkan sanggahan yang lebih sedikit dan waktu eksekusi yang lebih cepat. Selain itu, pertimbangkan beban tambahan yang ditempatkan pada database target dalam koneksi dan transaksi.
Faktor performa terkait
Panduan umum tentang performa database juga mempengaruhi batching. Misalnya, performa sisipan dikurangi untuk tabel yang memiliki kunci primer besar atau yang memiliki banyak indeks yang tidak diklusterkan.
Jika parameter bernilai tabel menggunakan prosedur tersimpan, Anda dapat menggunakan perintah SET NOCOUNT ON di awal prosedur. Pernyataan ini menekan pengembalian hitungan baris yang terpengaruh dalam prosedur. Namun, dalam pengujian kami, penggunaan SET NOCOUNT ON tidak mempunyai pengaruh atau menurunkan performa. Pengujian prosedur tersimpan ini sederhana dengan satu perintah INSERT dari parameter bernilai tabel. Ada kemungkinan bahwa prosedur tersimpan yang lebih kompleks akan mendapat manfaat dari pernyataan ini. Namun jangan berasumsi bahwa menambahkan SET NOCOUNT ON ke prosedur tersimpan secara otomatis meningkatkan performa. Untuk memahami efeknya, uji prosedur tersimpan anda dengan dan tanpa pernyataan SET NOCOUNT ON.
Skenario batching
Bagian berikut ini menjelaskan cara menggunakan parameter bernilai tabel dalam tiga skenario aplikasi. Skenario pertama menunjukkan bagaimana buffering dan batching dapat bekerja sama. Skenario kedua meningkatkan performa dengan melakukan operasi master-detail dalam satu panggilan prosedur tersimpan. Skenario terakhir menunjukkan cara menggunakan parameter bernilai tabel dalam operasi "UPSERT".
Buffering
Meskipun ada beberapa skenario yang jelas dapat digunakan untuk batching, ada banyak skenario yang dapat menguntungkan proses batching dengan pemrosesan tertunda. Namun, pemrosesan yang tertunda juga membawa risiko yang lebih besar yaitu data yang dapat hilang jika terjadi kegagalan yang tidak terduga. Penting untuk memahami risiko ini dan mempertimbangkan konsekuensinya.
Misalnya, pertimbangkan aplikasi web yang melacak riwayat navigasi setiap pengguna. Pada setiap permintaan halaman, aplikasi dapat melakukan panggilan database untuk merekam tampilan halaman pengguna. Namun performa dan skalabilitas yang lebih tinggi dapat dicapai dengan buffering aktivitas navigasi pengguna dan kemudian mengirim data ini ke database dalam batch. Anda dapat memicu pembaruan database dengan waktu yang berlalu dan/atau ukuran buffer. Misalnya, aturan dapat menentukan bahwa batch harus diproses setelah 20 detik atau ketika buffer mencapai 1000 item.
Contoh kode berikut menggunakan Reactive Extensions - Rx untuk memproses peristiwa buffer yang terjadi karena kelas pemantauan. Ketika buffer terisi atau batas waktu tercapai, batch data pengguna dikirim ke database dengan parameter bernilai tabel.
Kelas NavHistoryData berikut ini membuat model detail navigasi pengguna. Kelas ini berisi informasi dasar seperti pengidentifikasi pengguna, URL yang diakses, dan waktu akses.
public class NavHistoryData
{
public NavHistoryData(int userId, string url, DateTime accessTime)
{ UserId = userId; URL = url; AccessTime = accessTime; }
public int UserId { get; set; }
public string URL { get; set; }
public DateTime AccessTime { get; set; }
}
Kelas NavHistoryDataMonitor bertanggung jawab untuk buffering data navigasi pengguna ke database. Kelas ini berisi metode RecordUserNavigationEntry, yang merespons dengan melakukan kejadian OnAdded. Kode berikut menunjukkan logika konstruktor yang menggunakan Rx untuk membuat koleksi yang dapat diamati berdasarkan kejadian. Kemudian kode tersebut berlangganan ke koleksi yang dapat diamati ini dengan metode Buffer. Kelebihan beban menentukan bahwa buffer harus dikirim setiap 20 detik atau 1000 entri.
public NavHistoryDataMonitor()
{
var observableData =
Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");
observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}
Handler mengonversi semua item yang ter-buffer menjadi tipe bernilai tabel lalu meneruskan tipe ini ke prosedur tersimpan yang memproses batch. Kode berikut menunjukkan definisi lengkap untuk kelas NavHistoryDataEventArgs dan NavHistoryDataMonitor.
public class NavHistoryDataEventArgs : System.EventArgs
{
public NavHistoryDataEventArgs(NavHistoryData data) { Data = data; }
public NavHistoryData Data { get; set; }
}
public class NavHistoryDataMonitor
{
public event EventHandler<NavHistoryDataEventArgs> OnAdded;
public NavHistoryDataMonitor()
{
var observableData =
Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");
observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}
Handler mengonversi semua item yang ter-buffer menjadi tipe bernilai tabel lalu meneruskan tipe ini ke prosedur tersimpan yang memproses batch. Kode berikut menunjukkan definisi lengkap untuk kelas NavHistoryDataEventArgs dan NavHistoryDataMonitor.
public class NavHistoryDataEventArgs : System.EventArgs
{
if (OnAdded != null)
OnAdded(this, new NavHistoryDataEventArgs(data));
}
protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
{
DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
navHistoryBatch.Columns.Add("UserId", typeof(int));
navHistoryBatch.Columns.Add("URL", typeof(string));
navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
foreach (EventPattern<NavHistoryDataEventArgs> item in items)
{
NavHistoryData data = item.EventArgs.Data;
navHistoryBatch.Rows.Add(data.UserId, data.URL, data.AccessTime);
}
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
connection.Open();
SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter()
{
ParameterName = "@NavHistoryBatch",
SqlDbType = SqlDbType.Structured,
TypeName = "NavigationHistoryTableType",
Value = navHistoryBatch,
});
cmd.ExecuteNonQuery();
}
}
}
Untuk menggunakan kelas buffering ini, aplikasi membuat objek NavHistoryDataMonitor statis. Setiap kali pengguna mengakses halaman, aplikasi memanggil metode NavHistoryDataMonitor.RecordUserNavigationEntry. Logika buffering kemudian mengirim entri ini ke database dalam batch.
Detail master
Parameter bernilai tabel berguna untuk skenario INSERT sederhana. Namun, parameter ini bisa lebih sulit untuk sisipan batch yang melibatkan lebih dari satu tabel. Skenario "master/detail" adalah contoh yang bagus. Tabel master mengidentifikasi entitas utama. Satu atau beberapa tabel detail menyimpan lebih banyak data tentang entitas. Dalam skenario ini, hubungan kunci asing menyebabkan terjadinya hubungan detail dengan entitas master yang unik. Pertimbangkan versi tabel PurchaseOrder yang disederhanakan dan tabel OrderDetail terkait. T-SQL berikut membuat tabel PurchaseOrder dengan empat kolom: OrderID, OrderDate, CustomerID, dan Status.
CREATE TABLE [dbo].[PurchaseOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_PurchaseOrder]
PRIMARY KEY CLUSTERED ( [OrderID] ASC ))
Setiap pesanan berisi satu atau beberapa pembelian produk. Informasi ini diambil dalam tabel PurchaseOrderDetail. T-SQL berikut membuat tabel PurchaseOrderDetail dengan lima kolom: OrderID, OrderDetailID, ProductID, UnitPrice, dan OrderQty.
CREATE TABLE [dbo].[PurchaseOrderDetail](
[OrderID] [int] NOT NULL,
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
CONSTRAINT [PrimaryKey_PurchaseOrderDetail] PRIMARY KEY CLUSTERED
( [OrderID] ASC, [OrderDetailID] ASC ))
Kolom OrderID dalam tabel PurchaseOrderDetail harus mereferensikan pesanan dari tabel PurchaseOrder. Definisi kunci asing berikut ini memberlakukan batasan ini.
ALTER TABLE [dbo].[PurchaseOrderDetail] WITH CHECK ADD
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID])
Untuk menggunakan parameter bernilai tabel, Anda harus memiliki satu tipe tabel yang ditentukan pengguna untuk setiap tabel target.
CREATE TYPE PurchaseOrderTableType AS TABLE
( OrderID INT,
OrderDate DATETIME,
CustomerID INT,
Status NVARCHAR(50) );
GO
CREATE TYPE PurchaseOrderDetailTableType AS TABLE
( OrderID INT,
ProductID INT,
UnitPrice MONEY,
OrderQty SMALLINT );
GO
Kemudian tentukan prosedur tersimpan yang menerima tabel jenis ini. Prosedur ini memungkinkan aplikasi untuk membuat batch satu set pesanan dan detail pesanan secara lokal dalam satu panggilan. T-SQL berikut ini menyediakan deklarasi prosedur tersimpan yang lengkap untuk contoh pesanan pembelian ini.
CREATE PROCEDURE sp_InsertOrdersBatch (
@orders as PurchaseOrderTableType READONLY,
@details as PurchaseOrderDetailTableType READONLY )
AS
SET NOCOUNT ON;
-- Table that connects the order identifiers in the @orders
-- table with the actual order identifiers in the PurchaseOrder table
DECLARE @IdentityLink AS TABLE (
SubmittedKey int,
ActualKey int,
RowNumber int identity(1,1)
);
-- Add new orders to the PurchaseOrder table, storing the actual
-- order identifiers in the @IdentityLink table
INSERT INTO PurchaseOrder ([OrderDate], [CustomerID], [Status])
OUTPUT inserted.OrderID INTO @IdentityLink (ActualKey)
SELECT [OrderDate], [CustomerID], [Status] FROM @orders ORDER BY OrderID;
-- Match the passed-in order identifiers with the actual identifiers
-- and complete the @IdentityLink table for use with inserting the details
WITH OrderedRows As (
SELECT OrderID, ROW_NUMBER () OVER (ORDER BY OrderID) As RowNumber
FROM @orders
)
UPDATE @IdentityLink SET SubmittedKey = M.OrderID
FROM @IdentityLink L JOIN OrderedRows M ON L.RowNumber = M.RowNumber;
-- Insert the order details into the PurchaseOrderDetail table,
-- using the actual order identifiers of the master table, PurchaseOrder
INSERT INTO PurchaseOrderDetail (
[OrderID],
[ProductID],
[UnitPrice],
[OrderQty] )
SELECT L.ActualKey, D.ProductID, D.UnitPrice, D.OrderQty
FROM @details D
JOIN @IdentityLink L ON L.SubmittedKey = D.OrderID;
GO
Dalam contoh ini, tabel yang ditentukan @IdentityLink secara lokal menyimpan nilai OrderID aktual dari baris yang baru disisipkan. Pengidentifikasi pesanan ini berbeda dari nilai OrderID sementara dalam @orders dan @details parameter bernilai tabel. Karenanya, tabel @IdentityLink kemudian menyambungkan nilai OrderID dari parameter @orders ke nilai OrderID yang sebenarnya untuk baris baru dalam tabel PurchaseOrder. Setelah langkah ini, tabel @IdentityLink dapat memfasilitasi sisipan detail pesanan dengan OrderID aktual yang memenuhi batasan kunci asing.
Prosedur tersimpan ini dapat digunakan dari kode atau dari panggilan T-SQL lainnya. Lihat bagian parameter bernilai tabel dari dokumen ini untuk contoh kode. T-SQL berikut ini menunjukkan cara memanggil sp_InsertOrdersBatch.
declare @orders as PurchaseOrderTableType
declare @details as PurchaseOrderDetailTableType
INSERT @orders
([OrderID], [OrderDate], [CustomerID], [Status])
VALUES(1, '1/1/2013', 1125, 'Complete'),
(2, '1/13/2013', 348, 'Processing'),
(3, '1/12/2013', 2504, 'Shipped')
INSERT @details
([OrderID], [ProductID], [UnitPrice], [OrderQty])
VALUES(1, 10, $11.50, 1),
(1, 12, $1.58, 1),
(2, 23, $2.57, 2),
(3, 4, $10.00, 1)
exec sp_InsertOrdersBatch @orders, @details
Solusi ini memungkinkan setiap batch untuk menggunakan satu set nilai OrderID yang dimulai pada 1. Nilai OrderID sementara ini menjelaskan hubungan dalam batch, tetapi nilai OrderID aktual ditentukan pada saat operasi sisipkan. Anda dapat menjalankan pernyataan yang sama dalam contoh sebelumnya berulang kali dan menghasilkan pesanan unik dalam database. Karena itu, pertimbangkan untuk menambahkan lebih banyak kode atau logika database yang mencegah pesanan ganda saat menggunakan teknik batching ini.
Contoh ini menunjukkan bahwa operasi database yang lebih kompleks, seperti operasi master-detail, dapat dibuat batch menggunakan parameter bernilai tabel.
UPSERT
Skenario batching lainnya melibatkan pembaruan baris yang ada secara bersamaan dan penyisipan baris baru. Operasi ini terkadang disebut sebagai operasi "UPSERT" (update + insert). Daripada melakukan panggilan terpisah ke INSERT dan UPDATE, pernyataan MERGE bisa jadi penggantian yang sesuai. Pernyataan MERGE dapat melakukan operasi sisipan dan pembaruan dalam satu panggilan. Mekanisme penguncian pernyataan MERGE bekerja secara berbeda dari pernyataan INSERT dan UPDATE terpisah. Uji beban kerja spesifik Anda sebelum menyebar untuk produksi.
Parameter bernilai tabel dapat digunakan dengan pernyataan MERGE untuk melakukan pembaruan dan penyisipan. Misalnya, pertimbangkan tabel Employee yang disederhanakan yang berisi kolom berikut: EmployeeID, FirstName, LastName, SocialSecurityNumber:
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[SocialSecurityNumber] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_Employee] PRIMARY KEY CLUSTERED
([EmployeeID] ASC ))
Dalam contoh ini, Anda dapat menggunakan SocialSecurityNumber yang unik untuk melakukan MERGE beberapa karyawan. Pertama, buat tipe tabel yang ditentukan pengguna:
CREATE TYPE EmployeeTableType AS TABLE
( Employee_ID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
SocialSecurityNumber NVARCHAR(50) );
GO
Selanjutnya, buat prosedur tersimpan atau tulis kode yang menggunakan pernyataan MERGE untuk melakukan pembaruan dan penyisipan. Contoh berikut menggunakan pernyataan MERGE pada parameter bernilai tabel, @employees, dari tipe EmployeeTableType. Isi tabel @employees tidak diperlihatkan di sini.
MERGE Employee AS target
USING (SELECT [FirstName], [LastName], [SocialSecurityNumber] FROM @employees)
AS source ([FirstName], [LastName], [SocialSecurityNumber])
ON (target.[SocialSecurityNumber] = source.[SocialSecurityNumber])
WHEN MATCHED THEN
UPDATE SET
target.FirstName = source.FirstName,
target.LastName = source.LastName
WHEN NOT MATCHED THEN
INSERT ([FirstName], [LastName], [SocialSecurityNumber])
VALUES (source.[FirstName], source.[LastName], source.[SocialSecurityNumber]);
Untuk informasi selengkapnya, lihat dokumentasi dan contoh untuk pernyataan MERGE. Meskipun pekerjaan yang sama dapat dilakukan dalam panggilan prosedur tersimpan dalam beberapa langkah dengan operasi INSERT dan UPDATE terpisah, pernyataan MERGE lebih efisien. Kode database juga dapat membuat panggilan T-SQL yang menggunakan pernyataan MERGE secara langsung tanpa memerlukan dua panggilan database untuk INSERT dan UPDATE.
Ringkasan rekomendasi
Daftar berikut ini menyediakan ringkasan rekomendasi batching yang dibahas dalam artikel ini:
- Gunakan buffering dan batching untuk meningkatkan performa dan skalabilitas aplikasi Azure SQL Database dan Azure SQL Managed Instance.
- Memahami tradeoff antara batching/buffering dan ketahanan. Selama kegagalan peran, risiko kehilangan batch data penting bisnis yang tidak diproses mungkin lebih besar daripada manfaat performa pada batching.
- Coba simpan semua panggilan ke database dalam satu pusat data untuk mengurangi latensi.
- Jika Anda memilih teknik batching tunggal, parameter bernilai tabel memberikan performa dan fleksibilitas terbaik.
- Untuk performa sisipan tercepat, ikuti panduan umum ini tetapi uji skenario Anda:
- Untuk baris < 100, gunakan perintah INSERT berparameter tunggal.
- Untuk baris < 1000, gunakan parameter bernilai tabel.
- Untuk baris >= 1000, gunakan SqlBulkCopy.
- Untuk operasi memperbarui dan menghapus, gunakan parameter bernilai tabel dengan logika prosedur tersimpan yang menentukan operasi yang benar pada setiap baris dalam parameter tabel.
- Panduan ukuran batch:
- Gunakan ukuran batch terbesar yang cocok untuk aplikasi dan persyaratan bisnis Anda.
- Seimbangkan perolehan performa batch besar dengan risiko kegagalan sementara atau permanen. Apa konsekuensi dari percobaan kembali atau kehilangan data dalam batch?
- Uji ukuran batch terbesar untuk memverifikasi bahwa Azure SQL Database atau Azure SQL Managed Instance tidak menolaknya.
- Buat pengaturan konfigurasi yang mengontrol batching, seperti ukuran batch atau jendela waktu buffering. Pengaturan ini memberikan fleksibilitas. Anda dapat mengubah perilaku batching dalam produksi tanpa menyebarkan ulang layanan cloud.
- Hindari eksekusi paralel batch yang beroperasi pada satu tabel dalam satu database. Jika Anda memilih untuk membagi satu batch di beberapa thread pekerja, jalankan pengujian untuk menentukan jumlah alur yang ideal. Setelah ambang batas yang tidak ditentukan, lebih banyak alur akan mengurangi performa daripada meningkatkannya.
- Pertimbangkan buffering pada ukuran dan waktu sebagai cara untuk menerapkan batching untuk lebih banyak skenario.
Langkah berikutnya
Artikel ini berfokus pada bagaimana desain database dan teknik pengkodean yang terkait dengan batching dapat meningkatkan performa dan skalabilitas aplikasi Anda. Namun hal ini hanyalah salah satu faktor dalam strategi keseluruhan Anda. Untuk cara lain untuk meningkatkan performa dan skalabilitas, lihat Panduan performa database dan Pertimbangan harga dan performa untuk kumpulan elastis.
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk