I was searching for the way to optimize the output on one my site.
Found the follwoing article quite useful for Optimization in Paging and Sorting.
Here are the abstracts of the article:
Paging and sorting are most commonly used features of ASP.NET GridView. And it is very easy to use/implement these features in GridView with small chunk of lines. In this article I am going to demonstrate what are the performance drawbacks of using conventional way to page and sort your GridView and then I will demonstrate 'An Optimized way to implement Paging and Sorting'.
What are conventional steps for Paging and Sorting?
Usually we perform the following steps to enable paging and sorting in our GridView.
1. Set
AllowPaging and
AllowSorting Properties of GridView to True to enable paging and sorting respectively e.g
1.<asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true" >
2. asp:GridView>
2. Set the
PageSize property to mention how many records will be display on each page.
3. Set the
SortExpression property of each column. By default each Data Bound columns has the bounded column name as default value for the
SortExpression property.
4. Handle
PageIndexChanging and
Sorting Events of GridView to respond to paging and sorting actions respectively, like so:
01.<asp:GridView ID="GridView1" runat="server" AllowPaging="true"
02. AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging"
03. onsorting="GridView1_Sorting" >
04. asp:GridView>
05. protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
06.{
07.}
08.protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
09.{
10.}
5. Put some logic in the event handlers to do their jobs
5a. In the
PageIndexChanging Event Handler method, we usually get the data from database or somewhere from the Cache and rebind our Grid with that data. After rebinding we change the
PageIndex property of the GridView to a new page index to display the page that was selected by the user.
1.protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
2. {
3. GridView1.DataSource = GetData();
4. GridView1.DataBind();
5. GridView1.PageIndex = e.NewPageIndex;
6. }
5b. In the
Sorting event handler method, we get the sorted data according to the sort expression from our data source (data source could be database/cache/session etc) and then rebind the Grid to display the sorted records.
And that's it.
Drawbacks
In conventional way of paging and sorting we get complete set of data instead of getting only the portion of data that is required to display on current/requested page. As you can see on each
pageIndexChanging call we are getting all the data from our data source and then binding it to the GridView.
Ideally we should get only the data that we need to display on the requested page.
Hmmm...Sounds good but HOW??
The question that may arise in your mind could be "It seems good in theory that we should only get the required data, but practically if we bind only one page of data with GridView then it would assume that this is the only data that it needs to display. So how does the GridView even display page numbers and total records count? It is a genuine question, so let's try to answer!
An Optimized Way to implement Paging and Sorting
As in the start of this article, we discuss the conventional 5 steps to implement paging and sorting in ASP.NET GridView . In this solution we will use the first 3 steps as described above, and perform the 4th and 5th steps by ourselves. We will use an
ObjectDataSource that will perform these steps for us in an optimized way.
High Level Overview
We will optimize the code on both Database and Presentation layers.
At the Database Level we will write a stored procedure in such a way that it would return only one page of records. The stored procedure takes the page size, page index and a sort expression as input parameters and returns sorted records for a particular page index.
At the Presentation layer, we will use
ObjectDataSource’s virtual paging feature to optimize the paging. Virtual paging is not a term defined by Microsoft. I used it by myself because
ObjectDataSource exposes some properties and methods that allow us to bind only one page of data with GridView and to define the total number of records in database (not in one page), so that the GridView can extract out the total number of pages that need to be display in the page area of the GridView. In the next sections we will see what these properties and methods are, and how to use them.
If you are not familiar with
ObjectDataSource then you should first read some articles on that. Here are some articles:
Implementation Details
Database Layer
We have an employee table in a database with the following schema:
And we wrote the following stored procedure that has two
select statements. The first select statement will return the total number of employees in the Employee table and the second dynamic
select statement will return the sorted records for one page according to the provided start index, page size, and sortby parameters.
01.Create PROCEDURE spGetAllEmployee
02. (
03. @startIndex int,
04. @pageSize int,
05. @sortBy nvarchar(30),
06. @totalEmployees int OUTPUT
07. )
08.AS
09. SET NOCOUNT ON
10. DECLARE
11. @sqlStatement nvarchar(max),
12. @upperBound int
13. IF @startIndex < 1 SET @startIndex = 1
14. IF @pageSize < 1 SET @pageSize = 1
15. SET @upperBound = @startIndex + @pageSize
16. Select @totalEmployees=Count(*) From Employee
17. SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
18. FROM (
19. SELECT ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
20. FROM Employee
21. ) AS E
22. WHERE rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
23. rowNumber < ' + CONVERT(varchar(9), @upperBound)
24. exec (@sqlStatement)
One thing that I want to explain in the above stored procedure is the
ROW_NUMBER() function that makes it possible for us to select only one page of data. The
ROW_NUMBER() method is included in the 2005 release of TSQL. It actually adds an integer column in the selected record set, that contains the record number for each record . It seems very simple but in fact it's very helpful as we are going to perform nested quires. As we did in our stored procedure, in the nested query we select all employees records sorted by the provided sort expression, and add a row number for each record using the
ROW_NUMBER() method. In the outer query we filter the result rows by using lower and upper bound indexes so that we return only the rows between lower and upper bounds.
Data Access layer
In the Data Access Layer we will write a class that will be responsible to call the
spGetAllEmployee sorted procedure to get employee records and return the employee list to the business logic layer. To avoid increasing the complexity and length of the article I am only posting the code that is used to fetch the records from the database. I am not posting any helper code/classes; however the complete code is available for download.
If you wanna read the original article just click
here
Is the above link useful to you? Let us know your feedback, it will help us to improve our posting(s). or You can send your feedback
linkOblast.
