ASP.NET memiliki kontrol yang dapat digunakan untuk menampilkan data halaman demi halaman. Hal ini kelihatan sangat bagus tapi perlu dilihat di balik ini kerja SQL Server dan traffic jaringan antara SQL Server dan server web juga tinggi. Akhirnya response time ke client juga lama jika jumlah data sangat besar.
Berikut ini sedikit trik untuk meminimalkan proses pembacaan records di SQL Server dan meminimalkan data yang ditransmisikan dari SQL Server ke server web. Dalam contoh berikut digunakan database contoh dari MS SQL Server 2000 yaitu tabel Customers di database Nortwind.
CREATE PROCEDURE [dbo].[GetCustomers]
@PageSize int,
@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
CustomerID nchar(5) COLLATE database_default
)
INSERT INTO #PageIndex (CustomerID)
SELECT dbo.Customers.CustomerID FROM dbo.Customers
ORDER BY CompanyName
SELECT dbo.Customers.*
FROM dbo.Customers
INNER JOIN #PageIndex PageIndex
ON dbo.Customers.CustomerID = PageIndex.CustomerID
WHERE PageIndex.IndexID > @PageLowerBound
AND PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
SELECT COUNT(*) as TotalRecords
FROM #PageIndex
Pada contoh di atas digunakan tabel Customers yang memiliki primary key CustomerID sehingga temporary table berisi dua column (IndexID dan CustomerID). IndexID harus auto increment (identity) sehingga didapat indeks yang urut. Jika primary key tabel yang diambil lebih dari satu column atau berasal dari query yang kompleks dengan key lebih dari satu column, maka temporary table juga harus berisi columns yang digunakan sebagai key tersebut.
Berikutnya adalah select semua key dari tabel yang akan diambil dan hasilnya dimasukkan ke dalam temporary table yang sudah dibuat. Perhatikan bahwa pernyataan INSERT INTO dengan menyebutkan nama column kecuali column IndexID. Pernyataan ini akan melakukan insert key dan IndexID diisi dengan identity insert (nomor urut record).
Selanjutnya, pernyataan select yang sebenarnya yaitu mengambil record dari tabel Customers. Pernyataan ini mengambil record tabel Customers dengan filter berupa nomor IndexID dengan range nilai tertentu yang merupakan hasil INNER JOIN terhadap temporary table #PageIndex.
Terakhir adalah mengambil jumlah record sebenarnya yang ada dalam tabel Customers.
Contoh berikut adalah proses pemanggilan dan pembacaan stored procedure di atas menggunakan C# pada console application.
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetCustomers", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PageSize", 10));
cmd.Parameters.Add(new SqlParameter("@PageIndex", 3));
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine("CompanyName : {0}", dr["CompanyName"]);
}
dr.NextResult();
if (dr.Read())
{
Console.WriteLine("Total records {0}", dr["TotalRecords"]);
}
dr.Close();
conn.Close();
Pada kode di atas, pertama buat SqlConnection ke database. Set nilai connectionString sesuai dengan database yang digunakan. Selanjutnya buat SqlCommand berupa perintah call stored procedure dengan dua parameter @PageSize=10 dan @PageIndex=3. Berikutnya open connection dan baca record dengan SqlDataReader. Karena terdapat dua recordset yang dihasilkan oleh stored procedure, panggil method NextResult() untuk membaca recordset berikutnya. Dan dilanjutkan dengan pembacaan recordset berikutnya dengan method Read().
Hasil keluaran dari kode di atas adalah sebagai berikut.
CompanyName : Godos Cocina Típica
CompanyName : Gourmet Lanchonetes
CompanyName : Great Lakes Food Market
CompanyName : GROSELLA-Restaurante
CompanyName : Hanari Carnes
CompanyName : HILARION-Abastos
CompanyName : Hungry Coyote Import Store
CompanyName : Hungry Owl All-Night Grocers
CompanyName : Island Trading
CompanyName : Königlich Essen
Total records 91
Dengan query seperti ini akan sangat membantu meningkatkan response time saat menampilkan bagian data dalam jumlah besar.