What's the best way to paginate a query result for a gridview?

  • Thread starter Thread starter someone
  • Start date Start date
S

someone

I have added pagination controls to the gridview, but the query to the
database is taking too much time so I will have to change that controls.
So my problem now is other.
I think I need to paginate the query to the database and then to control
the pagination controls by hand.
My question is: What's the best way to paginate the query result to then
be able to control the pages I want to go?
Or there is a way to use a cache for the query to make it faster...

Thanks in advance for any help.
Regards,
Marcelo.
 
I have added pagination controls to the gridview, but the query to the
database is taking too much time so I will have to change that
controls. So my problem now is other.
I think I need to paginate the query to the database and then to
control the pagination controls by hand.
My question is: What's the best way to paginate the query result to
then be able to control the pages I want to go?
Or there is a way to use a cache for the query to make it faster...
Click to expand...

It depends.

if your final result set is millions of rows, then you have to go to a
custom pager. This is fairly easy if you are JUST paging. It gets more
difficult if you include the ability to sort on various columns.

The best custom pager, if just paging, is generally to use the primary
key and the first and last for the page. You then can grab next X
records where primarykeyId > last id.

If this is still slow, you will need to look at tuning up your database
server.

if the result set is not huge, you can cache it on the server side and
then use a custom pager to move to the next X records, using DataViews
to bind to the grid when the user clicks the buttons. This method is a
bit easier to add sorting in, in some respects, but sorting can still
munge things up.

how to cache? Each app is different but some options are:

1. Viewstate - only if result set is not huge
2. Session - not with apps where tons of users hit the app at the same
time, as you will consume too much and overdo it 3. Custom caching -
Things like Velocity (MS research) can help (available in .NET 4.0? -
believe so, need to check) or there are third party caching solutions 4.
ASP.NET cache - possible also

In general, if you have huge sets of data, making the database server
optimized and then using custom caching is better than consuming all the
memory, but that is another "it depends" answer.

Hope this helps.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top