How to bind DataGrid to display only 25 records of a table having more than 1000 records ..??

Question

I have a datagrid control which is bound to a table which containing more than 1000 records. And i want to show only 25 records once a time. I have used paging in datagrid. But each time the next page index is set, query is fired again. This takes lots of time. So what is the easiest way to bound data in this case to improve performance..??

Answer 1

have you considered caching your data set? then you would only need to query the data if the cache is empty or expired.

Answer 2

I don't recommend using caching since the whole data will be returned to the server anyway for the first time.

You can improve performance by using custom paging queries to the database.

Assuming you're working with at least SQL Server 2005, Here's a great article for your purpose with different benchmarking results

Answer 3

When you handle your Page Changed event, you need to pull in the new page information. You will need to create a stored procedure that takes CurrentPageNumber and PageSize as arguments.

This is in addition to any other arguments you already supply when bringing down your data.

In the SP, you fill up a temporary table or table variable (or you can use a CTE) with the data that you are returning, but also the RowNumber.

Based upon your CurrentPageNumber argument, you are able to return all the results between CurrentPageNumber * PageSize and (CurrentPgaeNumber + 1) * PageSize - 1.

Here's a good resource:

http://stackoverflow.com/questions/10190/how-to-return-a-page-of-results-from-sql