Diskusi tentang penerapan paging kumpulan hasil besar di ASP.NET

Dalam aplikasi web, memberi nomor halaman pada kumpulan hasil database yang besar adalah masalah yang umum terjadi. Sederhananya, Anda tidak ingin semua data query ditampilkan dalam satu halaman, jadi tampilan dengan paging lebih tepat. Meskipun ini bukan tugas yang mudah di ASP tradisional, di ASP.NET, kontrol DataGrid menyederhanakan proses ini menjadi hanya beberapa baris kode. Oleh karena itu, di asp.net, paging sangat sederhana, tetapi acara paging DataGrid default akan membaca semua catatan dari database dan memasukkannya ke dalam aplikasi web asp.net. Ketika Anda memiliki lebih dari satu juta data, ini akan menyebabkan masalah kinerja yang serius (jika Anda tidak mempercayainya, Anda dapat menjalankan kueri di aplikasi Anda dan melihat konsumsi memori aspnet_wp.exe di situasi pengelola tugas) Inilah alasannya perlu untuk menyesuaikan perilaku paging, untuk memastikan bahwa hanya catatan data yang diperlukan oleh halaman saat ini yang diperoleh.

Ada banyak artikel dan postingan tentang masalah ini di Internet, serta beberapa solusi yang matang. Tujuan saya menulis artikel ini bukan untuk menunjukkan kepada Anda prosedur tersimpan yang akan menyelesaikan semua masalah Anda, tetapi untuk mengoptimalkan metode yang ada dan memberi Anda aplikasi untuk diuji sehingga Anda dapat melakukannya sesuai dengan kebutuhan Anda.

Namun saya tidak begitu puas dengan metode yang saat ini diperkenalkan secara online. Pertama, ADO tradisional digunakan, yang jelas-jelas ditulis untuk ASP "kuno". Metode yang tersisa adalah prosedur tersimpan SQL Server, dan beberapa di antaranya tidak dapat digunakan karena waktu respons yang terlalu lambat, seperti yang Anda lihat dari hasil kinerja di akhir artikel, namun ada beberapa yang menarik perhatian saya.

Generalisasi

Saya ingin menganalisis dengan cermat tiga metode yang umum digunakan saat ini, yaitu tabel sementara (TempTable), SQL dinamis (DynamicSQL) dan jumlah baris (Rowcount). Berikut ini, saya lebih suka menyebut metode kedua sebagai metode Asc-Desc (ascending-descending). Menurut saya SQL dinamis bukanlah nama yang bagus karena Anda juga dapat menerapkan logika SQL dinamis dalam metode lain. Masalah umum dengan semua prosedur tersimpan ini adalah Anda harus memperkirakan kolom mana yang akan Anda urutkan, bukan hanya kolom kunci utama (Kolom PK), yang dapat menyebabkan serangkaian masalah - untuk setiap kueri, Anda perlu tampilkan melalui paging, yang berarti bahwa untuk setiap kolom pengurutan yang berbeda Anda harus memiliki banyak kueri paging yang berbeda, yang berarti Anda melakukan prosedur tersimpan yang berbeda untuk setiap kolom pengurutan (terlepas dari metode paging mana yang digunakan), atau Anda harus melakukannya letakkan fungsionalitas ini dalam prosedur tersimpan dengan bantuan SQL dinamis. Kedua metode ini berdampak kecil pada performa, namun meningkatkan kemudahan pemeliharaan, terutama jika Anda perlu menggunakan metode ini untuk menampilkan kueri yang berbeda. Oleh karena itu, dalam artikel ini saya akan mencoba menggunakan SQL dinamis untuk meringkas semua prosedur tersimpan, tetapi karena beberapa alasan, kami hanya dapat mencapai universalitas parsial, jadi Anda masih harus menulis prosedur tersimpan independen untuk kueri kompleks.

Masalah kedua dalam mengizinkan semua bidang pengurutan, termasuk kolom kunci utama, adalah jika kolom tersebut tidak diindeks dengan benar, metode berikut tidak akan membantu. Dalam semua metode ini, sumber halaman harus diurutkan terlebih dahulu. Untuk tabel data yang besar, biaya pengurutan menggunakan kolom non-indeks dapat diabaikan. Dalam hal ini, semua prosedur tersimpan tidak dapat digunakan dalam situasi sebenarnya karena waktu respons yang lama. (Waktu yang sesuai bervariasi dari beberapa detik hingga beberapa menit, tergantung pada ukuran tabel dan rekaman pertama yang diperoleh). Indeks pada kolom lain dapat menimbulkan masalah kinerja tambahan yang tidak diinginkan, misalnya indeks dapat menjadi sangat lambat jika Anda mengimpor banyak data setiap hari.

Tabel sementara

Pertama, saya akan berbicara tentang metode tabel sementara. Ini adalah solusi yang banyak direkomendasikan yang saya temui beberapa kali dalam proyek saya. Mari kita lihat inti dari metode ini:

CREATE TABLE #Temp(
ID ke KUNCI UTAMA IDENTITAS,
PK /*ini dia jenisPK*/
)

MASUKKAN KE #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

dengan menyalin semua baris ke sementara Di tabel, kita dapat mengoptimalkan kueri lebih lanjut (PILIH TOP EndRow...), tetapi kuncinya adalah skenario terburuk - tabel yang berisi 1 juta catatan akan menghasilkan tabel sementara dengan 1 juta catatan.

ascending-descending

dalam pengujian saya.

Metode ini menggunakan pengurutan default di subquery dan pengurutan terbalik di query utama.

DEKLARASIKAN @temp TABLE(
PK /* Tipe PK */
BUKAN NULL UTAMA
)

MASUKKAN KE @temp PILIH TOP @PageSize PK DARI
(
PILIH ATAS(@StartRow + @PageSize)
PK,
SortColumn /* Jika kolom pengurutan berbeda dengan PK, SortColumn harus
diambil juga, jika tidak, yang diperlukan hanyalah PK
*/

ORDER BERDASARKAN Kolom Sortir
/*
urutan default–biasanya ASC
*/
)

ORDER BERDASARKAN Kolom Sortir
/*
urutan default terbalik–biasanyaDESC
*/

PILIH DARI Tabel GABUNG @Temp temp PADA Tabel .PK= temp .PK
ORDER BERDASARKAN Kolom Sortir
/*
pesanan default
*/

penghitungan baris

bergantung pada ekspresi SET ROWCOUNT dalam SQL, sehingga baris yang tidak diperlukan dapat dilewati dan catatan baris yang diperlukan dapat diperoleh:

DECLARE @Sort /* jenis kolom pengurutan */

SET ROWCOUNT @ StartRow

SELECT @Sort=SortColumn DARI Tabel ORDER OLEH SortColumn
SET ROWCOUNT @PageSize

SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

subquery

yang telah saya pertimbangkan, dan sumbernya berbeda. Yang pertama adalah Triple Query atau metode self-query yang terkenal. Dalam artikel ini, saya juga menggunakan logika umum serupa yang mencakup semua prosedur tersimpan lainnya. Idenya di sini adalah untuk terhubung ke seluruh proses, saya membuat beberapa pengurangan pada kode asli karena jumlah catatan tidak diperlukan dalam pengujian saya)

SELECT FROM Table WHERE PK IN(
PILIH TOP @PageSize PK DARI Tabel DIMANA PK TIDAK MASUK
(
PILIH TOP @StartRow PK DARI Tabel ORDER BY SortColumn)
DIPESAN BERDASARKAN Kolom Sortir)
ORDER BY SortColumn

Cursor

Saat melihat grup diskusi google, saya menemukan metode terakhir. Metode ini menggunakan kursor dinamis sisi server. Banyak orang mencoba menghindari penggunaan kursor karena tidak relevan dan tidak efisien karena keteraturannya. Namun melihat ke belakang, paging sebenarnya adalah tugas yang teratur. Apa pun metode yang Anda gunakan, Anda harus mengembalikannya ke baris awal. Pada metode sebelumnya, pertama-tama Anda memilih semua baris sebelum mulai merekam, menambahkan baris yang diperlukan untuk direkam, lalu menghapus semua baris sebelumnya. Kursor dinamis memiliki opsi FETCH RELATIVE yang melakukan lompatan ajaib. Logika dasarnya adalah sebagai berikut:

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ BUKAN KUNCI UTAMA NULL
)

DECLARE PagingCursor CURSOR DYNAMICREAD_ONLY UNTUK
PILIH @PK DARI Tabel ORDER BY SortColumn

OPEN PagingCursor
MENGAMBIL RELATIF @StartRow DARI PagingCursor KE @PK

SAAT @PageSize>0 DAN @@FETCH_STATUS =0
MULAI
MASUKKAN @tblPK(PK) NILAI(@PK)
FETCH BERIKUTNYA DARI PagingCursor KE @PK
SET @UkuranHalaman = @UkuranHalaman - 1
AKHIR

TUTUP
Kursor Paging
DEALOKASI
PagingCursor

PILIH DARI Tabel GABUNG @tblPK temp PADA Tabel .PK= temp .PK

Generalisasi kueri kompleks

di ORDER BY SortColumn

Saya telah menunjukkan sebelumnya bahwa semua prosedur tersimpan menggunakan SQL dinamis untuk mencapai generalisasi, jadi secara teori mereka dapat menggunakan segala jenis kueri kompleks. Di bawah ini adalah contoh kueri kompleks berdasarkan database Northwind.

PILIH Pelanggan.NamaKontak SEBAGAI Pelanggan, Pelanggan.Alamat + ' , ' + Pelanggan.Kota + ', '+ Pelanggan.Negara
Alamat AS, SUM([Detail Pesanan].Harga Unit*[Detail Pesanan] .Kuantitas)
AS [Totalmoneyspent]

DARI Pelanggan

INNER JOIN Pesanan ON Customers.CustomerID = Pesanan.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'USA' DAN Customers.Country <> 'Meksiko '

KELOMPOK BERDASARKAN Pelanggan.NamaKontak,Pelanggan.Alamat,Pelanggan.Kota, Pelanggan.Negara
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC

mengembalikan panggilan penyimpanan paging pada halaman kedua sebagai berikut:

EXEC ProcedureName

/*Tables */

'
Pelanggan
INNER JOIN Pesanan PADA Customers.CustomerID=Orders.CustomerID
INNER GABUNG [Detail Pesanan] PADA Pesanan.OrderID=[Detail Pesanan].OrderID
'
,
/*PK */

'
Pelanggan.ID Pelanggan
'
,
/* ORDER BY */

'
Pelanggan.NamaKontak DESC,Pelanggan.AlamatDESC
'
,

/*Nomor Halaman */

2
,

/*Ukuran Halaman */

10
,

/*Bidang */

'
Pelanggan. Nama Kontak SEBAGAI Pelanggan,
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,

/*Filter */

'
Pelanggan.Negara<>'' AS '' DAN Pelanggan.Negara<> '' Meksiko ''' ,

/*GroupBy */

'
Pelanggan.ID Pelanggan,Pelanggan.NamaKontak,Pelanggan.Alamat,
Pelanggan.Kota, Pelanggan.Negara
MEMILIKI(JUMLAH([Detail Pesanan].Harga Satuan*[Detail Pesanan].Jumlah))>1000
'

Perlu dicatat bahwa Anda menggunakan alias dalam pernyataan ORDER BY dalam kueri asli, namun sebaiknya Anda tidak melakukan ini dalam prosedur tersimpan berhalaman, karena melewatkan baris sebelum mulai merekam memakan waktu. Sebenarnya ada banyak metode untuk implementasinya, namun prinsipnya tidak memasukkan semua field di awal, tetapi hanya menyertakan kolom kunci utama (setara dengan kolom pengurutan pada metode RowCount), yang dapat mempercepat penyelesaian tugas. Hanya di halaman permintaan semua bidang wajib diperoleh. Selain itu, tidak ada alias bidang dalam kueri terakhir, dan dalam kueri baris lewati, kolom indeks harus digunakan terlebih dahulu.

Ada masalah lain dengan prosedur tersimpan RowCount. Untuk mencapai generalisasi, hanya satu kolom yang diperbolehkan dalam pernyataan ORDER BY. Ini juga merupakan masalah dengan metode ascending-descending dan metode kursor, meskipun mereka dapat mengurutkan beberapa kolom harus dipastikan bahwa hanya ada satu field di kunci utama. Saya kira ini bisa diselesaikan dengan SQL yang lebih dinamis, tapi menurut saya itu tidak sepadan. Meskipun situasi seperti itu mungkin terjadi, hal itu tidak sering terjadi. Biasanya Anda dapat menggunakan prinsip-prinsip di atas untuk juga secara mandiri mengatur prosedur tersimpan.

Pengujian Kinerja

Dalam pengujian, saya menggunakan empat metode, jika Anda memiliki metode yang lebih baik, saya tertarik untuk mengetahuinya. Bagaimanapun, saya perlu membandingkan metode ini dan mengevaluasi kinerjanya. Pertama-tama, ide pertama saya adalah menulis aplikasi pengujian asp.net yang berisi paging DataGrid, dan kemudian menguji hasil halamannya. Tentu saja, hal ini tidak mencerminkan waktu respons sebenarnya dari prosedur tersimpan, sehingga aplikasi konsol lebih cocok. Saya juga menyertakan aplikasi web, tetapi bukan untuk pengujian kinerja, tetapi sebagai contoh penomoran halaman khusus DataGrid dan prosedur tersimpan yang bekerja bersama.

Dalam pengujian saya, saya menggunakan tabel data besar yang dibuat secara otomatis dan memasukkan sekitar 500.000 data. Jika Anda tidak memiliki tabel untuk bereksperimen, Anda dapat mengklik di sini untuk mengunduh desain tabel dan skrip prosedur tersimpan untuk menghasilkan data. Daripada menggunakan kolom kunci utama yang bertambah secara otomatis, saya menggunakan pengidentifikasi unik untuk mengidentifikasi catatan. Jika saya menggunakan skrip yang saya sebutkan di atas, Anda mungkin mempertimbangkan untuk menambahkan kolom kenaikan otomatis setelah membuat tabel. Data kenaikan otomatis akan diurutkan secara numerik berdasarkan kunci utama. Ini juga berarti Anda bermaksud menggunakan prosedur tersimpan yang diberi nomor halaman dengan penyortiran kunci utama untuk mendapatkan data halaman saat ini.

Untuk mengimplementasikan uji kinerja, saya memanggil prosedur tersimpan tertentu beberapa kali melalui satu putaran dan kemudian menghitung waktu respons rata-rata. Mempertimbangkan alasan caching, untuk memodelkan situasi aktual dengan lebih akurat - waktu yang diperlukan halaman yang sama untuk mendapatkan data untuk beberapa panggilan ke prosedur tersimpan biasanya tidak cocok untuk evaluasi. nomor halaman yang diminta untuk setiap panggilan harus acak. Tentu saja kita harus berasumsi bahwa jumlah halamannya tetap, 10-20 halaman, dan data dengan nomor halaman berbeda dapat diperoleh berkali-kali, tetapi secara acak.

Satu hal yang dapat kita perhatikan dengan mudah adalah bahwa waktu respons ditentukan oleh jarak data halaman yang akan diperoleh relatif terhadap posisi awal kumpulan hasil, semakin jauh dari posisi awal kumpulan hasil, semakin banyak catatan yang dihasilkan dilewati. Ini juga Alasan mengapa saya tidak memasukkan 20 teratas dalam urutan acak saya. Sebagai alternatif, saya akan menggunakan 2^n halaman, dan ukuran loop adalah jumlah halaman berbeda yang dibutuhkan * 1000, sehingga setiap halaman diambil hampir 1000 kali (pasti akan ada penyimpangan karena alasan acak)

Hasil

Di Sini adalah hasil tes saya:






Kesimpulan

Pengujian dilakukan secara berurutan dari yang berkinerja terbaik hingga terburuk - jumlah baris, kursor, naik-turun, subkueri. Satu hal yang menarik adalah biasanya orang jarang mengunjungi halaman setelah lima halaman pertama, jadi metode subquery mungkin sesuai dengan kebutuhan Anda dalam hal ini, tergantung pada ukuran kumpulan hasil Anda dan seberapa jauh jaraknya untuk memprediksi frekuensi kemunculan halaman , Anda juga cenderung menggunakan kombinasi metode ini. Jika itu saya, saya lebih suka metode penghitungan baris, metode ini berfungsi cukup baik, bahkan untuk halaman pertama, "kasus apa pun" di sini mewakili beberapa kasus di mana generalisasi sulit dilakukan, dalam hal ini, saya akan menggunakan sebuah kursor. (Saya mungkin akan menggunakan metode subquery untuk dua metode pertama, dan metode kursor setelahnya)