General discussion

Locked

Web - Multiple Pages for a Result Set

By stefanw ·
Ok, first...does anyone have the source code for altavista? ;-)

I've been struggling with a logic problem at many client projects.

Lets say, for a web page, a user of the system selects a page that lists all items for sale in our company catalog. Lets assume the list of items is qty 1 million. Lets also assume (while filtering options are available), the user currently is browsing the entire list, rather than shortening the rowset by some pre-defined filter.

Obviously, I would like to present the user with a page (similar to search engines) that allows them to select an option of viewing 25 items per page, and flip through the items page by page, while sorting by a user selected column.

This issues I've had are:

1. Since a web page is stateless, how do a select the first 25 records from a database, then the next 25...etc, all based on the choice of sort column. With indexes this is fairly simple, but i hate to index 30 columns in a table, many of which are varchars.

2. ADO Provides this ability, but also grabs the entire recordset. (A Bad thing when pumping data across an n-tier system to a user through a 56k dialup).

3. If a user sits on one of the pages for 25 minutes, and new data is entered by another user during that time, the next page, or subsequent pages should reflect the changes and grab the current recordset.

I'm struggling with the methodology of how to walk through a very large rowset from a web front-end while maintaining uniqueness of the results, keeping the system stateless, and not having to return the entire rowset for each page (only the 25 records viewed for that page, rather than the 1 million available records).

Does anyone have a detailed idea of how to accomplish this issue? What technology is used? Temp tables, indexes, or any other features used to provide this functionality.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Web - Multiple Pages for a Result Set

by Churdoo In reply to Web - Multiple Pages for ...

First of all, what is your language of choice? Given your example of a 1 million record table, you probably want to use server-side processing, especially considering support for dialup users as you have identified in item 2 of your description.I'll use .asp in this example only because I've specifically done this using .asp, that's not to say that you can't do it on some other platform.

In .asp, you can set the following properties of the result set:
myRS.PageSize=itemsPerPageInt ' set page size
myRS.AbsolutePage=pageNoInt ' set current page
totalPagesInt=myRS.PageCount' get page count

Then process the specified page of your result set here, and of course, set up the page jump links, limited by totalPagesInt and pass the desired page number in the url, a session variable, or in a form. You can set a default and/or have the user select her desired page size (itemsPerPageInt in the example).
Yes, in this example, the server still opens the large recordset, but again, this is server-side, and if you maintain the recordset in the user session, then you can jump from page to page without having to re-open and re-query the recordset. The only thing that you're sending to the browser is the information, a page at a time.
Also, If you open the recordset dynamically with adOpenDynamic specified in the ADO connection.open statement, then the recordset should capture new data as specified in item 3 of your description.
When the user leaves the page or her session times out, you can close the recordset and database connection so you don't kill your server.
Hope this is useful for you.
--Charlie

Collapse -

Web - Multiple Pages for a Result Set

by stefanw In reply to Web - Multiple Pages for ...

I understand that. However, that is not what I am looking for. This is a server side request, running in the following environment. A 4 computer web farm, 2 app servers load balanced, and 2 database servers load balanced with a shared disk. The issue is not getting from page to page. The issue is limiting the amount of records the database server needs to retrieve and pass back to the app server when a recordset consists of 1 million records. With an outlook of 1000 concurrent users, we need tobe sure this app will scale appropriately as database requests are generated. This is also a stateless environment, so from one page to the next, there is nothing cached or stored on any of the computers.

Just to answer your question, I am using VC++ and OLEDB for the data layer. and VB for the biz layer. With a tiny amount of asp, implementing xml and xsl on the web layer. Sql Server 2000 on the back end.

Collapse -

Web - Multiple Pages for a Result Set

by jignesh In reply to Web - Multiple Pages for ...

Hi,

If you can stick to sql then you can try top 'n' with order by clause.

Hope this helps.

Regards

Jignesh

Collapse -

Web - Multiple Pages for a Result Set

by stefanw In reply to Web - Multiple Pages for ...

Poster rated this answer

Collapse -

Web - Multiple Pages for a Result Set

by denis77 In reply to Web - Multiple Pages for ...

For each unique request you'll need to run a full query anyway. So the only area where you could optimize is not running a full query while flipping between pages with results and not running identical queries twice.

To achieve this, you could cache IDs of returned records in a separate table for each unique query, so when the query is repeated by another user or by the same user to show next bunch of results, you're not running original query again, but rather picking records by record IDs from the cache, which is in most cases far more efficient than running a full query. This approach will also allow to show updated records, as you need (but not newly added.)

Another optimization you should definitely consider given the number of records in your table is running full queries on a separate table, extensively indexed and constructed from a VIEW where all look-up fields are expanded to a maximum extent possible. This technique is explained in great detail in a chapter of any database developer's guide devoted to optimization.

Collapse -

Web - Multiple Pages for a Result Set

by stefanw In reply to Web - Multiple Pages for ...

Poster rated this answer

Collapse -

Web - Multiple Pages for a Result Set

by stefanw In reply to Web - Multiple Pages for ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums