Optimized Paging and Sorting in ASP.NET GridView

Total votes: 2
Views: 56,637
Comments: 19
Category: Gridview
Print: Print Article

Please login to rate or to leave a comment.

Published: 28 Sep 2009
By: Akhtar Shiekh
Download Sample Code

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'.

Contents [hide]
1 Introduction 2 What are conventional steps for Paging and Sorting? 3 Drawbacks 4 Hmmm...Sounds good but HOW?? 5 An Optimized Way to implement Paging and Sorting 6 High Level Overview 7 Implementation Details 7.1 Database Layer 7.2 Data Access layer 7.3 Business Logic Layer 8 Presentation Layer 9 Comparison 10 Summary 11 Feedback

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 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

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:

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.

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.

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.

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 is that the last parameter totalEmployee of GetAllEmployee is passed by reference. It is an optimization; we don't want to perform two separate database calls to get employees records and total count. For the same reason we are returning both employee data and total count from a single stored procedure. In the presentation layer it will be clearer to you why this approach is necessary and how it will help us.

Business Logic Layer

Business logic layer will only be calling the Data Access Layer code to fetch the records. Source code is available to download.

Presentation Layer

As we discussed earlier in the article, we will use the virtual paging feature of ObjectDataSource. Object Data Source exposes some interesting properties which are used for virtual paging purposes. To benefit from these properties you should set the EnablePaging property of ObjectDataSource to True. These properties are

StartRowIndexParameterName: Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page index. For example, if Grid View page size is 10 and the user clicks page number 3 to view the 3rd page, then the ObjectDataSource will calculate the value of StartRowIndexParameter by using the (page Size * page Index) formula. In this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means that if we don't mention any value for this property then the Select method should have the startRowIndex parameter.
MaximumRowsParameterName: Specifies the parameter name of the Select method of ObjectDataSurce’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page Index. ObjectDataSource source gets its value from GridView's PageSize property. Default value of this property is maximumRow, which means that if we don't set any value for this property then the Select method should have the maximumRow parameter.
SelectCountMethod: Specifies the method name of ObjectDataSource’s Type. This method will be called by ObjectDataSource to get the total number of records in the database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod returns 100 as total number of employees in the database then ObjectDataSource will display 10 page indexes in the GridView's pager area, even if we didn't get all the 100 records from the database.
SortParameterName: Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user clicks on any column header to sort the data according to that column. ObjectDataSource fetches the value from the SortExpression property of the particular GridView column.

Now if you see all these three properties together then you will understand that SelectCountMethod will use them to display virtual page indexes in the GridView, and that on each page index change it will use the values of StartRowIndexParameterName and MaximumRowsParameterName parameters to query the database and fetch only the desired page of data.

We have already written stored procedures, data access and business logic code that accepts these parameters and returns only the sorted page of data. So it's time to pass values to these parameters.

Enough theory; now let's write something in C# and ASP.NET.

Listing 1: 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>

Listing 2: EmployeeData.cs: A class which is bound to ObjectDataSource

Nothing special here to explain because we are calling the already explained code to get one page of sorted data by passing the ObjectDataSource provided values. One thing that I want to explain from an optimization point of view is that we are getting employeesCount in the GetAllEmployees() method, and in GetTotalEmployeesCount() we only return that value, instead of fetching the count from database. What people usually do is making separate database calls to fetch the count, which is an unnecessary step because we can write our stored procedure, data access and business logic layer so that we can get the actual data and the total count in a single database call.

Comparison

I've made a comparison between the conventional and the optimized implementation of paging and sorting and there is a HUGE difference between them.

One noticeable thing from this performance table is that as we are increasing the amount of data, the conventional implementation is taking more time to fetch the records from the database, while there is no noticeable difference in the optimized way.

Table 1: Paging Performance

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

Table 2: Sorting Performance

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

The Paging time is calculated on GridView page index changed event.
The 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 the Database Layer we used the ROWNUMBER() method which helps us to select only one page of data. And in the Presentation layer we used ObjectDataSource's virtual paging feature by using the StartRowIndexParameterName, MaximumRowsParameterName and SelectCountMethod,SortParameterName parameters.

Feedback

You feedback will really help me to be better, so please leave your comments and suggestions. You can also send me any query at akhhttar at gmail dot com.

Happy Programming!

<< Previous Article

Continue reading and see our next or previous articles

About Akhtar Shiekh

I am Microsoft Certified Technology Specialist for Web Application Development. I have 4 year experience of Web and Distributed application development.I have considerable experience developing client / server software for major corporate clients using the Windows operating systems and .NET platform...

This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.

You might also be interested in the following related blog posts

VS 2010 Code Intellisense Improvements (VS 2010 and .NET 4.0 Series) read more
Gaia Ajax 3.6 Beta Released! Free download of new Ajax GridView, 35++ Ajax Components and 100++ New Samples read more
Optimized Paging and Sorting in ASP.NET GridView read more
Multiple child views with RadGridView for WinForms read more
WPF Release History : Q1 2009 SP2 (version 2009.1.526) read more
Silverlight Release History : Q1 2009 SP2 (version 2009.1.526) read more
Silverlight Release History : Q1 2009 SP1 (version 2009.1.413) read more
WPF Release History : Q1 2009 SP1 (version 2009.1.413) read more
Improved ASPxGridView Exporting for 2008 vol 2 read more
C# GridView Sorting/Paging w/o a DataSourceControl DataSource read more

Top

Discussion

Subject	Author	Date
Well done	Muhammad Tariq	9/29/2009 8:48 AM
It's nice article . Buddy you have done a good job. Really impressive work . I am sure that this article will be useful for many person and this technique will make the work more standardize and fast. Hope to see some more nice article in the future. best of luck Reply \| Permanent link
RE: Well done	Akhtar Shiekh	9/29/2009 9:41 AM
Thank You Tariq. Hopefully you will see more use full articles in future. -Akhtar Reply \| Permanent link
Interesting Article	Wassim Brinsi	10/27/2009 10:24 AM
Hi Thank you for sharing this article. It's very interesting and it provide an optimized speed. But i dont udersatnd why have you used dataset instead of datareader? Do you plan to publish an article using datareader. I tried to implement it but i got a problem cauz my output param has the null value. Thank you another time for the quality of your article Wassim Reply \| Permanent link
Sorting	Armando Alfaro	11/12/2009 6:56 AM
Great explanation !!! I have already done similar implementation, but there is just one thing I have never got to work and is the sorting. I suppose my error is to let bult-in functionalities to handle it.... but when I try to sort by a specific column, it sorts based only on the records on the displayed page..... How do you get it to works fine ? Could you provide us an exaple of you sort hanlde implementation ? Thanks in advance Reply \| Permanent link
RE: Sorting	Akhtar Shiekh	11/12/2009 7:14 AM
Might be you are applying order by clause on outer query. In this case ordery will apply only on current page record. However I used orderby caluse with inner query, in this case sorting apply on all records and then only current page records return to outer query. My assumption could be wrong because i didn't see your code yet. If you can paste your code then i might be able to pin point the issue. Thanks Akhtar Reply \| Permanent link
RE: RE: Sorting	Armando Alfaro	11/12/2009 8:25 AM
Uhm.... Not sure what could I paste, but here my stocked procedure and the ObjectDataSource definition.... let me know if there is any particuler code you'd like me to paste.... PROCEDURE "GETGRANTEDUSERS" ( "ISADMIN" IN NUMBER, "STARTRWINDEX" IN NUMBER, "MAXRWNB" IN NUMBER, "CONSTADMIN" IN NUMBER, "DATA" OUT SYS_REFCURSOR) IS BEGIN -- executable part starts here OPEN DATA FOR SELECT V1.USERID, V1.NOM, V1.DOMAINE, V1.LOGIN, V1.EMAIL, V1."ROLE" FROM (SELECT ROW_NUMBER() OVER (ORDER BY NOM) AS RowRank, USERID, NOM, DOMAINE, LOGIN, EMAIL, "ROLE" FROM USERS WHERE (LPAD("ROLE", 3) = 'HRM') OR (LPAD("ROLE", 3) = 'HRD') AND (0 = "ISADMIN") OR (CONSTADMIN = "ISADMIN") ORDER BY "NOM") V1 WHERE RowRank > startRwIndex AND RowRank <= ( startRwIndex + maxRwNb); END "GETGRANTEDUSERS"; <asp:ObjectDataSource ID="OdsGrantedUsers" runat="server" DeleteMethod="DeleteUser" EnablePaging="True" SelectCountMethod="CountUsersGranted" SelectMethod="GetUsersGrantedByPaging" TypeName="TP.MOB.REGIONS.DATA.Users"> <DeleteParameters> <asp:SessionParameter Name="userID" SessionField="Del_Param_Id" Type="Int32" /> </DeleteParameters> <SelectParameters> <asp:ControlParameter ControlID="HdnUserProfile" DefaultValue="0" Name="isAdmin" PropertyName="Value" Type="Int32" /> <asp:Parameter Name="startRowIndex" Type="Int32" /> <asp:Parameter Name="maximumRows" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource> Reply \| Permanent link
RE: RE: RE: Sorting	Armando Alfaro	11/12/2009 8:28 AM
I forgot to show you the GridView !!! <asp:GridView ID="GridView1" runat="server" DataSourceID="OdsGrantedUsers" AllowPaging="True" AllowSorting="True" PageSize="15" > ...... etc </asp:GridView > Reply \| Permanent link
RE: RE: RE: Sorting unfimliar	Akhtar Shiekh	11/13/2009 8:06 AM
Hi, Syntax of TSQL in Stored procedure is unfmiliar to me,Which version of SQL Server you are using? Although syntax is unfmiliar to me but logic seems to be correct. The only thing that i can see is, you used order by clause two times in same query, Leave the Order By class in Over and remove it on second place. Thanks -Akhtar Reply \| Permanent link
very nice article	Hw Lau	11/13/2009 4:03 AM
Thanks for writing such a good article. After I've read this article, I created a 1 million records in a test table. Unfortunately, my program hit a problem: the response time is not as fast as said in the article whenever i change the column inside the OVER() keyword. The bottleneck is because of the proper index was not there. Here is my findings: 1. whenever I execute "OVER( ORDER BY inv_no )", the response time is less than 300ms. 2. whenever i execute "OVER (ORDER BY dt")", the response time is more than 1000ms. Let says, I want the 700k-th records, it will take about 3000ms+. I tried to create the following index, but it does not help. The response time is too long. create index ix_inv_master_2 on inv_master (dt); If I use 'INCLUDE" keyword in the CREATE INDEX, you will get the response time as mentioned in this article: create index ix_inv_master_2 on inv_master (dt) include(inv_no, cust, amt, inv_running_no) My table: create table inv_master ( inv_no nvarchar(15) not null, dt datetime, cust nvarchar(100), amt money ) Primary Key: inv_no Don't forget to defrag the index often by executing the following command: dbcc indexdefrag ('test', 'inv_master', 'ix_inv_master_2') Lau. Reply \| Permanent link
RE: very nice article	Akhtar Shiekh	11/13/2009 8:17 AM
Hi Lau, Thanks for sharing your experience.It would definitely be helpful for others. Thanks -Akhtar Reply \| Permanent link
Amazing Article	Sumit Thapar	11/13/2009 6:49 AM
Really i would like to appreciate the work. totally amazing article and very helpful also. Thank You so much Reply \| Permanent link
MY BAD...I just replied on wrong comments.	Akhtar Shiekh	11/13/2009 8:22 AM
I am really sorry that i replied on wrong comments...and i am surprised that there is no option to edit/delete comments :(.. Thanks -Akhtar Reply \| Permanent link
RE: MY BAD...I just replied on wrong comments.	Sonu Kapoor	11/13/2009 10:29 AM
We will add the features very soon. [MVP since 2005] [MCAD] Webmaster of DotNetSlackers Question or Suggestion? Feel free to ask my any .NET question Our Posting FAQ Reply \| Permanent link
RE: RE: MY BAD...I just replied on wrong comments.	Akhtar Shiekh	11/13/2009 11:16 AM
Thanks Sonu, I think there is a bug also in the application, because I think I replied on correct comments, because my reply titles are according to the original comments(see the reply titles ), but it added under different comments. Thanks -Akhtar Reply \| Permanent link
RE: RE: RE: MY BAD...I just replied on wrong comments.	Sonu Kapoor	11/14/2009 2:19 PM
This should be fixed now. Thanks for reporting the issue. [MVP since 2005] [MCAD] Webmaster of DotNetSlackers Question or Suggestion? Feel free to ask my any .NET question Our Posting FAQ Reply \| Permanent link
Excellent, but ...	Yang Du	12/15/2009 10:17 AM
It's good to know another way. After I implemented your method, it runs faster in the frist few pages, but significantly slower towards to end of pages, than the way I did by first putting records in a temporary table in the database and then grab those records for the specified page. Good article! Reply \| Permanent link
Nice Article but Correction needs to be done	Bhagwat Phartyal	10/2/2010 6:53 PM
Ita an nice article but it shows employeeID "5" twice in the firest page and in the second page .Please make the necessary changes in the stored procedure to rectify the same. I fixed that problem by making changes in the stored procedure IF @startIndex < 1 begin SET @startIndex = 1 end else begin SET @startIndex =@startIndex+1 end Ur comments will be highly appreciated Reply \| Permanent link
Question about the Sorting	Evan Larsen	12/13/2010 12:52 PM
Thank you so much for sharing this solution. I have applied it to my project and its working well. I have one question though. I need to not only sort by ASCENDING which is the default, but I need to sort by DESCENDING also. How can this be accomplished? Here is some SQL which I've wrote that might facilitate it.. but I cant figure out the ASP.net side of it. ALTER PROCEDURE [Common].[Select_SearchForFacilityByName] @StartRow INT, @EndRow INT, @OrderBy varchar(10), @prefix varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT NameOfFacility, StreetAddress, FacilityCity, FacilityTelephone, FacilityFileNumber, ClientCode FROM ( SELECT fi.ORG_NAME as NameOfFacility, fi.ST_ADDR1 as StreetAddress, fi.ST_CITY as FacilityCity, fi.ST_PHONE_NBR as FacilityTelephone, fi.FILE_NBR as FacilityFileNumber, fi.CLIENT_CODE as ClientCode, ROW_NUMBER() OVER ( ORDER BY CASE WHEN @OrderBy = 'NameAsc' THEN fi.ORG_NAME END ASC, CASE WHEN @OrderBy = 'NameDesc' THEN fi.ORG_NAME END DESC, CASE WHEN @OrderBy = 'FileNbrAsc' THEN fi.FILE_NBR END ASC, CASE WHEN @OrderBy = 'FileNbrDesc' THEN fi.FILE_NBR END DESC ) as RowNumber FROM Common.nhfrv_facl_info fi WHERE fi.ORG_NAME Like @prefix + '%' ) faclInfo WHERE RowNumber > @StartRow - 1 AND RowNumber < @EndRow + 1 ; END Reply \| Permanent link
Nice Article	Prasad Ak	3/6/2012 12:07 AM
Thanks for the nice information. http://prasadaknair.blogspot.com Reply \| Permanent link

Please login to rate or to leave a comment.

Free Agile Project Management Tool from Telerik
TeamPulse Community Edition helps your team effectively capture requirements, manage project plans, assign and track work, and most importantly, be continually connected with each other.

Login

Lost Password?