Title: Optimized Paging and Sorting using Object Data Source
Author: Muhammad Akhtar Shiekh
Email: akhhttar@gmail.com
Member ID: 2243665
Language: C# 2.0
Platform: Windows, .NET
Technology: ASP.NET
Level: Intermediate
Description: An article on optimized paging and sorting in GridView.
Section ASP.NET
SubSection GridView
License: CPL
Introduction
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 do following steps to enable paging and sorting in our GridView,
- Set
AllowPaging and AllowSorting Properties of GridView to True to enable paging and sorting respectively
e.g
<asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true" >
</asp:GridView>
- Set
PageSize property to mention how many records will be display on each page.
- Set
SortExpression property of each column. By default each DataBound columns has bounded column name as default value for SortExpression property.
- Handle
PageIndexChanging and Sorting Events of GridView to respond Paging and sorting actions respectively
e.g
<asp:GridView ID="GridView1" runat="server" AllowPaging="true"
AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging"
onsorting="GridView1_Sorting" >
</asp:GridView>
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
}
- Put Some Logic in Event Handler to do their jobs
a. In PageIndexChanging Event Handler method, we usually get the data from database or somewhere from Cache and rebind our Grid with that data. And after rebinding we change PageIndex property of GridView to new page index to display the page that was selected by user.
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.DataSource = GetData();
GridView1.DataBind();
GridView1.PageIndex = e.NewPageIndex;
}
b. In 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 sorted records.
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
GridView1.DataSource = GetData(e.SortExpression);
GridView1.DataBind();
}
And that’s it.
Drawbacks
In conventional way of paging and sorting we get complete set of data instead of getting part of data that is required to display on current/requested page.
As you can see that on each pageIndexChanging call, we are getting all the data from our data source and then bind it to GridView. Ideally we should get only that data that is required to display on 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 required data, but practically if we bind only one page data
with GridView then it would assume that this is the only data that it needs to
display, so how GridView even display page numbers and total records count? It
is a genuine question, so let’s try to answer this question!!!
An Optimized Way of Paging and Sorting
As in the start of this article, we discuss conventional 5 steps to implement paging and sorting in ASP.NET GridView. In this purposed solution we will be using first 3 steps AS IT IS and instead of performing 4th and 5th steps by our self, we will use ObjectDataSource that will perform these steps for us in optimized way.
High Level Overview
We will optimize the code on both Database and Presentation layer
At the Database Level we will write a stored procedure in such a way that it would return only one page records. That stored procedure take Page Size, Page Index and Sort Expression as input parameters and return sorted record for 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 expose some properties and method that allow us to bind only one page data with GridView and to define total number of records in database (not in one page), so that GridView can extract out total number of pages that needs to be display in page area of 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 I would recommend
that you should first read some article on that. Here are some articles
Implementation Details:
Database Layer:
We have an employee table in database with following schema
and we wrote a following stored procedure that has two select statements. First select statement will return the total number of employee in Employee table and second dynamic select statement will return one page sorted record according to the provided start index, page size, and sortby by parameters
Create PROCEDURE spGetAllEmployee
(
@startIndex int,
@pageSize int,
@sortBy nvarchar(30),
@totalEmployees int OUTPUT
)
AS
SET NOCOUNT ON
DECLARE
@sqlStatement nvarchar(max),
@upperBound int
IF @startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @upperBound = @startIndex + @pageSize
Select @totalEmployees=Count(*) From Employee
SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
FROM Employee
) AS E
WHERE rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
rowNumber < ' + CONVERT(varchar(9), @upperBound)
exec (@sqlStatement)
One thing that i want to explain in above stored procedure is ROW_NUMBER() function that make it possible for us to select only one page data. ROW_NUMBER() method is included in 2005 release of TSQL, it actually add an integer column in selected record set, that contain the record number for each record . It seems very simple but infact it very helpful while we are going to perform nested queries. As we did in our stored procedure, in nested query we select all employees record sorted by the provided sort expression and add a row number for each record using ROW_NUMBER() method, and in outer query we filter the resulted rows by using lower and upper bound indexes so that we return only those rows which lies in lower and upper bounds.
Data Access layer:
In Data Access Layer we will write a class that will be responsible to call spGetAllEmployee stored procedure to get employee records and return employee list to the business logic layer. To avoid the complexity and length of article i am only posting the code is used to fetch the records from database, I am not posting any helper code/classes, however complete code is available for download .
public List<EmployeeInfo> GetAllEmployee(int startIndex, int pageSize, string sortBy,ref int totalEmployees) {
IDbConnection connection=null ;
IDbCommand selectCommand=null ;
List<EmployeeInfo> employeeInfoList;
DataSet employeeDS = new DataSet();
IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();
try
{
using (connection = DataObjectFactory.CreateConnectionObject())
{
using (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connection, "spGetAllEmployee"))
{
selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@startIndex", SqlDbType.Int, startIndex));
selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@pageSize", SqlDbType.Int, pageSize));
selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@sortBy", SqlDbType.NVarChar, 30, sortBy));
selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@totalEmployees", SqlDbType.Int, 0));
((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;
if (connection.State != ConnectionState.Open)
connection.Open();
dataAdapter.SelectCommand = selectCommand;
dataAdapter.Fill(employeeDS);
totalEmployees = Convert.ToInt32(((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Value);
}
}
}
catch (SqlException ex)
{
if (connection.State != ConnectionState.Closed)
connection.Close();
DALException mineException = new DALException();
mineException.ConnectingString = connection.ConnectionString;
mineException.StoredProcedureName = selectCommand.CommandText;
mineException.Source = "GetAllEmployee";
throw mineException;
}
employeeInfoList = ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);
return employeeInfoList;
}
One thing you might notice that last parameter totalEmployee of GetAllEmployee is by reference, it is because of the optimization, we don’t want to perform two seprate database calls to get employees records and total count. Because of the same reason we are returning both things (employee data and total count) from one stored procedure. In presentation layer it will be more clearer to you that what was the necessity of this approach and how it will be helping us.
Business Logic Layer:
Business logic layer will only be calling Data Access layer code to fetch the records. Source code is available in download.
Presentation Layer:
As we discusses earlier in the artcile that we will use the virtual paging feature of ObjectDataSource.
Object Data Source exposes some interesting properties which is used for virtual paging purpose.
To get benefits from these properties you should set EnablePaging property of ObjectDataSource to True. These properties are
StartRowIndexParameterName: Specify the parameter name of Select method of ObjectDataSource’s
bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page index. For example, if Grid View page size is 10 and user click page number 3 to view 3rd page then the ObjectDataSource will calculate the value of StartRowIndexParameter by using ( page Size * page Index) formula , in this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means if we don’t mention any value for this property then Select method should have startRowIndex parameter.MaximumRowsParameterName: Specify the parameter name of Select method of ObjectDataSurce’s
bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page Index. ObjectDataSource source get its value from GridView’s PageSize property. Default value of this property is maximumRow. which means if we don’t mention any value for this property then Select method should have maximumRow parameter.SelectCountMethod: Specify the method name of ObjectDataSource’s Type. This method will be called by ObjectDataSource to get the total number of records in database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod return 100 as total number of employees in database then ObjectDataSource will display 10 page indexes in GridView page areas ( However we didn’t get all 100 records from data base)SortParameterName: Specify the parameter name of Select method of ObjectDataSource’s
bounded Type. ObjectDataSource will pass the value to this parameter when user click on any column header to sort the data according to that column. ObjectDataSource fetch the value from SortExpression property of particular GirdView column
Now if you see all these three properties togather then you will understand that SelectCountMethod will use to display virtual page indexes in GridView and on each page index change we will use values of StartRowIndexParameterName and MaximumRowsParameterName parameters to query database to fetch desired page data only.
As you know that we already written stored procedures, data access and business logic code which accpet these parameters and return the only one page sorted data. So it's time to use them by passing these parameters values to them.
It’s enough in theory, now let’s talk something in C# and ASPX,
ASPX: Markup of GridView and ObjectDataSource
<asp:GridView ID="GridView1" DataKeyNames="EmployeeID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" GridLines="None" AllowSorting="True" PageSize="5" >
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="true" SortExpression="EmployeeID" />
<asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode" SortExpression="EmployeeCode" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
<asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees" EnablePaging="true"
TypeName="EmployeeData" StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize" SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >
</asp:ObjectDataSource>
EmployeeData.cs: A class which is bound to ObjectDataSource
public class EmployeeData
{
private static int employeesCount;
public EmployeeData()
{
}
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<EmployeeInfo> GetAllEmployees(int startIndex, int pageSize, string sortBy)
{
EmployeeBLL objEmployeeBLL = new EmployeeBLL();
List<EmployeeInfo> result;
int totalEmployees=0;
if (string.IsNullOrEmpty(sortBy))
sortBy = "EmployeeID";
result = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy, ref totalEmployees);
employeesCount = totalEmployees;
return result;
}
public static int GetTotalEmployeesCount()
{
return employeesCount;
}
}
Nothing special here to explain because we are calling already explained code to get one page sorted data by passing ObjectDataSource provided values. But one thing that I want to
explain from optimization point of view, We are getting employeesCount in GetAllEmployees() method, and in GetTotalEmployeesCount() we only return that value, instead of fetching the count from database. What people usually do is , they send seprate database call to fetch the count which is an unnecessary step because we can write our stored procedure and data acess and business logic layer in such a way in which we can get the actual data and total count in one database call.
Comparison
I did comparison between conventional and optimized implementation of paging and sorting and there is a
HUGE difference between them, Here are the results
One noticeable thing from this performance table is, as we are increasing the data up-optimized implementation is taking more time to fetch the records from database while there is no noticeable difference in optimized way.
|
No. of Records
|
Optimized Paging
( in seconds) |
Un-optimized Paging ( in seconds) |
|
500000 |
0.21 |
07.41 |
|
1000000 |
0.28 |
17.27 |
|
2000000 |
0.32 |
36.28 |
|
3000000 |
0.33 |
54.03 |
|
No. of Records
|
Optimized Sorting
( in seconds) |
Un-optimized Sorting ( in seconds) |
|
500000 |
01.61 |
10.14 |
|
1000000 |
03.13 |
23.22 |
|
2000000 |
09:38 |
47.39 |
|
3000000 |
13.12 |
70:25 |
* Paging time is calculated on GridView page index changed event.
* Sorting time is calculated on GridView sorting event.
Summary
In this article we learned an End to End solution to optimized paging and sorting in ASP.NET GridView. In Database Layer we used ROWNUMBER() method which help us to select only one page data. And In Presentation layer we used ObjectDataSource Virtual paging feature by using its properties StartRowIndexParameterName, MaximumRowsParameterName, SelectCountMethod,SortParameterName.
Hope it helps.
Feedback
You feedback will really help me to be better, so please leave your comments, suggestions. You can also send me any query at akhhttar at gmail dot com.
Happy Programming!!!
