Cara menggunakan batching untuk meningkatkan performa aplikasi Azure SQL Database dan Azure SQL Managed Instance

Artikel
06/02/2023

Berlaku untuk: Azure SQL Database Azure 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.

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

    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.

Bagikan melalui

Cara menggunakan batching untuk meningkatkan performa aplikasi Azure SQL Database dan Azure SQL Managed Instance

Mengapa batching penting untuk Azure SQL Database dan Azure SQL Managed Instance?

Strategi batching

Catatan tentang hasil pengaturan waktu dalam artikel ini

Transaksi

Parameter bernilai tabel

Salin massal SQL

Pernyataan INSERT berparameter multi baris

DataAdapter

Entity Framework

XML

Pertimbangan pembuatan batch

Tradeoffs

Ukuran batch

Pemrosesan paralel

Skenario batching

Buffering

Detail master

UPSERT

Ringkasan rekomendasi

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Cara menggunakan batching untuk meningkatkan performa aplikasi Azure SQL Database dan Azure SQL Managed Instance

Mengapa batching penting untuk Azure SQL Database dan Azure SQL Managed Instance?

Strategi batching

Catatan tentang hasil pengaturan waktu dalam artikel ini

Transaksi

Parameter bernilai tabel

Salin massal SQL

Pernyataan INSERT berparameter multi baris

DataAdapter

Entity Framework

XML

Pertimbangan pembuatan batch

Tradeoffs

Ukuran batch

Pemrosesan paralel

Faktor performa terkait

Skenario batching

Buffering

Detail master

UPSERT

Ringkasan rekomendasi

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan: