What is the best method for paging with datagrid performance?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

I would do it at database level. Talking about sql server 2005, i would use the new ROW_NUMBER() function, look at: http://stackoverflow.com/questions/2840/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 http://aspnet.4guysfromrolla.com/articles/031506-1.aspx you can see how it works and examine a little benchmark by Scott Mitchell.