Paging a datagrid

[bizzydint]

Am a right in thinking that every time you change page on a "paged" datagrid, it will make another call to the DB and re-retrieve the data?

 

Does it do some fantastic thing in the background, or is it as inefficient as it seems?

 

Or is there a way of keeping the data (currently in a dataTable) and using that instead of calling the db?? (i'm guessing it gets wiped on page reload??)

 

cheers.

[dsgreen57]

Yeah its as inefficient as it seems, what you can do is re-write your queries so you only retirieve as many records as your page allows.

 

Also you are able to cache the DataSet returned, look up Cache object on msdn and there are loads of samples on how to do it.

[JoshWithrow]

Actually, as long as you don't leave the current page, doesn't the datagrid read from the dataset that you've loaded? it only reads/writes back if you tell it to doesn't it?

[wyrd]

Actually, as long as you don't leave the current page, doesn't the datagrid read from the dataset that you've loaded? it only reads/writes back if you tell it to doesn't it?

 

I haven't toyed with ASP.NET much, but my understanding is that DataSet objects are loaded into the servers memory until they're done being used. Basically once the page is done being sent to the user who requested it, the DataSet (along with any other objects used) will be marked for garbage collection. Despite being extremely useful and efficient in Client/Server apps, it seems to be a different story in ASP.NET. When used correctly though, they can be highly efficient in ASP.NET (so I've read). There are a couple of ways to force the server to keep reusing the DataSet, and one way is the cache as dsgreen57 mentioned.

 

Maybe I misunderstood your question or I'm just confused period. Hopefully someone with actual knowledge of this can clear it up for us. :)

 

Am a right in thinking that every time you change page on a "paged" datagrid, it will make another call to the DB and re-retrieve the data?

 

From what I've read this seems to be the way it's done and it's also the way I've implemented it in my current program. You determine a head of time how many records the user will need to view per page and only retrieve that amount of items per call to the database. I've also used this paging technique back in the day of when I was doing PHP and ASP scripting.

 

Does it do some fantastic thing in the background, or is it as inefficient as it seems?

 

It's actually not inefficient at all, quite the opposite. From the benchmarking I've done on my own program with 1,000,000 records that were made up of two joined tables, I was able to implement a paging mechanism that retrieved 50 records per page in the blink of an eye.

 

Or is there a way of keeping the data (currently in a dataTable) and using that instead of calling the db?? (i'm guessing it gets wiped on page reload??)

 

dsgreen57's suggested a good solution (the cache). However, I would like to just add a little warning about this. Usually you want to use the cache to hold data that will be displayed over and over to several users and page requests. This may not be a good thing to implement if the data changes frequently or is not consistent, and also if you have to have a seperate cache per user request.

 

There's a reason for my madness; Imagine you implement a cache for searches, and 10 users come up and search your 2 million record table. On average, the results come back with 100 thousand records. Since 10 users requested a search on different things, you now have 1,000,000 records being held in memory just for paging. Imagine if that was 100 users on a popular page :eek: In this situation I do think using a typical Command.ExecuteReader() would be more efficient for your paging.

 

Don't get me wrong though! I do think caching/datasets have a good and efficient place. Imagine running an online store and there's a top 10 daily list of best sellers. Well, these top 10 sellers are only updated once a day and you know what you need to retrieve them each time the user requests to see the item list, so in this cache just cache it. That way if a billion users request this same piece of data, it'll be handy already in memory and you won't have to retrieve it from the database.

 

I don't know, maybe I'm wrong. I'm just blabbin here and hopefully a minor spec of this will be useful in some way.

[bizzydint]

quick question cos i'm being lazy (I only just woke up and brain hasnt quite kicked in yet)

 

On a DB query - I know how to get the first 20 results

ie SELECT TOP 20 * from tbl_Whatever

But how would i get the *next* 20 for the following calls to the db?

 

 

wyrd - thanks for the advice. You seem to have understood me this time ;-)

[dsgreen57]

Take a look at the following article

http://www.dotnetjohn.com/articles/articleid28.aspx

[wyrd]

dsgreen57's link is pretty good, unfortunately it's based off of identity columns. Very rarely have I seen records in a table retrieved in order by their unique id, usually you'll want them in alphabetical order. Not to mention it also assumes that there are no gaps in your identity column either. Using this, if you deleted an item, it'd display the incorrect number of records for a page.

 

I've just done a paging mechanism for my program, and below is the code I used. This code assumes you want records in alphabetical order (sorting by item name), and also assumes that your item name is not unique (so this will include an order by id as well).

 

NOTE: The article dsgreen57 posted shows how to keep track of number of pages and records, the only thing I'm showing is another way to do the selection queries.

 

Assume we have the following table:

Table: Products

Column: ID (primary key, unique)

Column: Name (clustered index, not unique)

 

To get the first page is easy as you've already shown us:

 

SELECT TOP 20 * FROM Products ORDER BY Name, ID

 

Now this is where it gets tricky. To implement the next page mechanism you'll need to keep track of the last item on the previous page. So when the user clicks next, you grab the last item of whatever items are current showing, and use that as a criteria for getting the next page.

 

@LastItemName = Name of the last item in the previous page.

@LastItemID = ID of the last item in the previous page.

 

SELECT TOP 20 * FROM Products 
WHERE (Name > @LastItemName) OR (Name = @LastItemName AND ID > @LastItemID)
ORDER BY Name, ID

 

The previous mechanism can start to make things even more tricky. Instead of keeping track of the last item on the previous page, you want to keep track of the first item on the next page. Basically when the user clicks the previous button, you want to grab the first item in the current list and select the previous items based on it.

 

Also since we're going backwards, you'll need to first get the records in descending order, then re-order them in ascending order, otherwise you'll have your previous items displayed in the wrong order. This requires a derived table.

 

@FirstItemName = Name of first item in the next page.

@FirstItemID = ID of first item in the next page.

 

SELECT * FROM
  (SELECT TOP 20 * FROM Products
  WHERE (Name < @FirstItemName) OR (Name = @FirstItemName AND ID < @FirstItemID)
  ORDER BY Name DESC, ID DESC)) Items
ORDER BY Name, ID

 

The last page mechanism is similar to the previous page, except we don't need to base the selection off of any first or last item. Rather, we need to limit the TOP # otherwise we'll get items from the previous page.

 

To calculate the TOP # of items to retrieve, we need to know how many records there are and also the number of items to display per page.

 

NOTE: All numbers and calculations below are assuming the values are integers, NOT doubles, floats, etc.

 

@NumberOfRecords = 101

@ItemsPerPage = 50

 

From this, we can look at it and assume there will be 1 item on the last page, right? So the SELECT TOP # should come out to SELECT TOP 1 after the calculations are complete. The calculation is quite simple;

 

@TopNumber = @NumberOfRecords % @ItemsPerPage

 

I'm sure you're familiar with modular divison, it simply returns the remainder of the of the division. In this case, our remainder should be 1, which is exactly what we're looking for.

 

SELECT * FROM 
  (SELECT TOP @TopNumber FROM Products
  ORDER BY Name DESC, ID DESC) Items
ORDER BY Name, ID

 

All of the above selection queries are the exact code I've used to implement paging into my application (give or take a few bugs I may have introduced while typing this out). Despite it looking like it may be inefficient, it isn't. I've tested this code against a 1 million record table and it retrieves records very, very fast. It's almost as if I wasn't working with a 1 million record table at all.

 

I hope this helps.

 

If anyone has a more efficient way of doing paging then I'm all ears as well. :)

[mazlina]

i'm having problem in datagrid paging while i'm using a dropdownlist to specify a value to which data to be shown.....on my first page, it's viewing the right data but not on the following pages.