'Paging' search results

[Jakes Daddy]

I wonder if anyone can help. I'm looking for some efficient/well thought through/language independant/whatever else you want to call it solution for the following problem:

I'm writing a web application for a client. Part of it is a search screen - search for records from the database based on some user provided criteria.

No, problem, 'tis written, and works fine.

Now the client is asking if I can add a 'paging' facility - where the user can select how many records per page are shown, with the usual Next/Previous links. Standard stuff. In addition, the page should show "Showing records x thru y of z"

The objective is two-fold:
1) make the returned list shorter and more managable for the user
2) reduce un-necessary network traffic / database load / application load (without paging, the result set could run into many thousands, and the user more than likely only wants something from the last 100)

I've written a solution that works, but I'm not that happy with it, and wanted some other opinions to ensure that it is reasonably sound.

What I've written basically does 2 queries per page hit - the first query does a SELECT COUNT(*) .... with the user entered criteria to get the total number of records (so I can display "Showing records x thru y of z")
Then I do the same query again, but specifying the fields I want instead of COUNT(*), and applying "WHERE ROWNUM < value and ROWNUM > value" clauses. I manage the upper and lower extents for the ROWNUM clauses as hidden fields. When the user clicks Next or Previous, the page reloads and the whole lot happens again but with the new upper and lower extents.

As I said, it works fine, but I need to ensure its a good solution. Bearing in mind one of the objectives is to reduce network and database traffic, 2 queries cant be a good idea ??

So I thought, OK, I'll only do one query without specifying the ROWNUM clauses (so I get all records), and manage the paging internally. I can put the entire record set into a data structure like an array (and thus get the total records), and then store the array in the session. Display the first n rows, then when the user clicks next, the page reloads, does not do the query as the session state already has the array, and just display the next n elements.
I dont like this as the database will always return the full set of records (many thousands), but chances are for the most part the user will only want to see the first page - I will have retrieved 1000's of records, only to have displayed the first 100.

Or, do it without page reloads; display the result set as a DHTML table, only displaying the number of records required. When the user clicks next the DHTML table is updated with client side JS.
Again, I dont like this for the same reason above; plus I will need to maintain a very large JS array, and write a load of carthorse client side JS to update the results table.


Anyone got any bright solutions for this ?

Cheers

Nathan

[DimPrawn]

.Net, that's the answer.

You either have to hit the database once, pull a large dataset into memory and page throught that OR just ask the database for the page (say 50) results the user wants to see at that point.

The 2nd approach is much more scalable. Using SQL Server and a store procedure one call is all that is required per page of results. You pass in the page number (page 1,2,3, whatever) and how many items per page (e.g 50). The stored proc counts the total row count and works out how many pages there are, what the rownumber is for the first row on that page and what the rownumber is for the last row on the requested page and returns these results.

The webserver converts this data to HTML, chucks it to the browser and discards the data from memory.

This scales well, even if the search results might contain 10 million results.

[janey]

even powerbuilder does this ... it's as simple as clicking the "retrieve as needed" checkbox on the datawindow.

shocking it seems so complicated to do this in things that are supposedly replacing pb. bigger shame that sybase haven't got their @rses into gear where PB is concerned

long live the pb datawindow!

[DimPrawn]

If you go with the first approach (one call and hold all X thousand rows in memory), what do you think will happen when 10, 100 or 1000 users hit the search button within the sessions timout period?

Yes, the web server will be caching 1000 x 10,000 rows in memory, the whole thing will die horribly.

For scalabilty sake, it is better to take the roundtrip hit to the DB each time a search or paging operation occurs and then render and discard from memory that page of results.

Storing large amounts of data in sessions state is never a good idea. It becomes stale, the RAM gets eaten up and you have to handle expired sessions.

[MrsGoof]

are the results to the initial query likely to be cached ?

if so go for the multiple hits

[privateeye]

long live the pb datawindow!

Them were the days, I remember some poor guy spent days writing a bit of code for pagination in PB - he'd come from VB. He was so proud when he showed it to me, but was brought down to earth when I showed him the checkbox to do it all for him in less than one second.

[DimPrawn]

Them were the days, I remember some poor guy spent days writing a bit of code for pagination in PB - he'd come from VB. He was so proud when he showed it to me, but was brought down to earth when I showed him the checkbox to do it all for him in less than one second.

Now the smart contractor would charge for 3 days to check that checkbox.

[privateeye]

Now the smart contractor would charge for 3 days to check that checkbox.

..and the not so smart would click the checkbox again to make sure and then wonder why it didn't work.

[janey]

Them were the days, I remember some poor guy spent days writing a bit of code for pagination in PB - he'd come from VB. He was so proud when he showed it to me, but was brought down to earth when I showed him the checkbox to do it all for him in less than one second.

oh yes I've worked on PB code written by VB developers... grrrrr, they shouldn't be let anywhere near it!

[DimPrawn]

PowerBuilder 11 and 12 are going to be fully fledge .NET development tools, which means it's worth learning the .NET framework now to make the most of your PB experience.