CODEWITHSUNDEEP
performancedatagriddata-paging
David Robbins
David Robbins

Reputation: 10046

What is the best method for paging with datagrid performance?

On a site with a high number of users, should paging be handled in code, or with a stored procedure. If you have employed caching, please include your success factors.

Upvotes: 1

Views: 1539

Answers (5)

netadictos
netadictos

Reputation: 7722

I would do it at database level. Talking about sql server 2005, i would use the new ROW_NUMBER() function, look at: Paging SQL Server 2005 Results

Where a typical sql would be:

SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
FROM Users
WHERE RowID Between 0 AND 9

Here https://web.archive.org/web/20210510021915/http://aspnet.4guysfromrolla.com/articles/031506-1.aspx you can see how it works and examine a little benchmark by Scott Mitchell.

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062955

Most database vendors offer rich paging support at the database. Make use of it ;-p Note that it doesn't have to be a stored-procedure to do this (I'll sideline the ever-running stored-proc vs ad-hoc command debate).

As an aside, many frameworks will also do this for you efficiently. For example, in .NET 3.5 (with LINQ), you can use Skip() and Take() to do paging that is used at the db.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171431

99.9% of the time, paging should be done on your database server. However, stored procedures are not required to do this, and, in fact, many stored procedure solutions rely on cursors and are quite inefficient. Ideally, use a single SQL statement tailored to your database platform to retrieve just the records you need and no more.

Upvotes: 3

caltuntas
caltuntas

Reputation: 10852

I think It depends on number of records to be paged. For example you have 100 records to be paged I think there is no need SQL paging stuff to do this. I am always trying to keep in my mind KISS principle and Premature optimization.

Upvotes: 0

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422026

Personally, I never page stuff outside SQL Server. I do this at database level as if you have a million records to be paged, if you retrieve it in application layer and page it there, you are already paying a huge cost.

Upvotes: 3

Related Questions