This post originated from an RSS feed registered with .NET Buzz by Doug Thews. Original Post: Skip the SQL When Paging To Improve Performance Feed Title: IlluminatiLand Feed URL: http://apps5.oingo.com/apps/domainpark/domainpark.cgi?client=netw8744&s=JETBRAINS.COM Feed Description: A technology blog for people enlightened enough to think for themselves Latest .NET Buzz Posts Latest .NET Buzz Posts by Doug Thews Latest Posts From IlluminatiLand

If you've read any examples about how do paging on an ASP.NET data grid, you've seen the sample where the SQL is queried after each postback caused by hitting a page button (here's a snippet from MSDN):



Sub BindDatagrid()

 Dim myAdapter As SqlDataAdapter

 Dim DS As DataSet

 EndIndex = StartIndex + DataGrid1.PageSize

 myAdapter = New SqlDataAdapter _

  ("Select * From Products Where ProductID > @startIndex " &_

  "And ProductID <= @endIndex Order by ProductID", cn)

 myAdapter.SelectCommand.Parameters.Add("@startIndex", StartIndex)

 myAdapter.SelectCommand.Parameters.Add("@endIndex", EndIndex)

 DS = New DataSet()

 myAdapter.Fill(DS)

 DataGrid1.DataSource = DS

 DataGrid1.DataBind()

End Sub



Public Sub datagrid1_PageIndexChanged _

 (ByVal source As System.Object, ByVal e As _

 System.Web.UI.WebControls.DataGridPageChangedEventArgs)

 StartIndex = (e.NewPageIndex * DataGrid1.PageSize)

 DataGrid1.CurrentPageIndex = e.NewPageIndex

 BindDatagrid()

End Sub


Of course, this example only shows the PageIndexChanged event. A lot of people use other buttons for custom paging to handle paging. You can see that BindDataGrid is called and queries the data for every page change request. Well, when dealing with a data source that is infrequently updated, you might think about keeping the data in a cached dataset, rather than re-querying it to improve performance.


To do something like this, you'll need to do something that could be considered bad. Create a Shared dataset in your Page class. What that will do is keep a single instance of that Page class available and in memory, with the dataset ready for reference between postbacks. You could also use the Session object to save off a serialized copy of the dataset, but that adds a little overhead too.


So, now you've got your Shared data set in your Page class:


Public Class MyWebPage

 Inherits System.Web.UI.Page

Private Shared _ds1 as DataSet


Now, your paging event handlers are reduced the the following code:


Protected Sub DataGrid1_PageIndexChanged _

 (ByVal sender As System.Object, _

 ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) _

 Handles DataGrid1.PageIndexChanged

  DataGrid1.CurrentPageIndex = e.NewPageIndex

End Sub


Keep in mind that this isn't an everyday solution. You need to understand that the data will not be refreshed when paging, so it should not be updated frequently (you might even want to put a Refresh button somewhere on the screen). Also, Shared members inside a class are not to be taken lightly. It's not an everyday occurence, and some folks plain refuse to implement shared members - but I think they have their place, if implemented with care & forethought. And, if you don't like the Shared dataset member, you can always Session off a serialized copy (make sure it's serialized so that it can be saved and loaded more quickly).