Large Recordset - web - multiple pages per recordset

Large Recordset - web - multiple pages per recordset

Post by Stefan Willmer » Fri, 06 Apr 2001 23:39:51



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.

 
 
 

Large Recordset - web - multiple pages per recordset

Post by Tibor Karasz » Fri, 06 Apr 2001 23:57:57


Quote:> Ok, first...does anyone have the source code for altavista? ;-)

See attachment <g>.

Seriously. Search the archives (deja.com) for posts by Bob Pfeiff. Also see Itzik
Ben.Gan's SQL Mag article about the subject (February, perhaps).

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

 
 
 

Large Recordset - web - multiple pages per recordset

Post by Don Arsenaul » Sat, 07 Apr 2001 23:39:07


/*
Range of Rows from resultset.
Paging a resultset.

If you're doing forward and backward paging of dynamic data, I recommend
using a unique row key value instead of a page number or row number.  Make
the client remember the unique key value of the first and last rows of the
current page.  The unique key value of the first row is used to get the
previous page and the unique key value of the last row is used to get the
next page.

New and lost rows will be handled much better.  Performance should be much
better.  The client just has to handle the situation of getting fewer rows
than expected when it reaches the first row in the table.

Stored procedures handle the situation nicely.  Pass NULL for the parameter
to get the first or last page.
*/


AS
    --return first page if parameters are null.

        SELECT TOP 10 *
        FROM my_big_table
        ORDER BY unique_key
    ELSE
        SELECT TOP 10 *
        FROM my_big_table

        ORDER BY unique_key


AS
    --return last page if parameters are null.

        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            ORDER BY unique_key DESC
            ) AS Reorder
        ORDER BY unique_key
    ELSE
        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table

            ORDER BY unique_key DESC
            ) AS Reorder
        ORDER BY unique_key

/*
The above routines assume that the resultset is ordered by the unique key.
If that's not true, a combination of a sort column and the unique key
can be used. Pass the sort column value as well as the unique key to the
next_page and previous_page procedures.  Make sure the table has an index
on the combination of the sort column and the unqiue key.
*/

CREATE PROCEDURE next_page


AS
    --return first page if parameters are null.

        SELECT TOP 10 *
        FROM my_big_table
        ORDER BY sort_column, unique_key
    ELSE
        SELECT TOP 10 *
        FROM my_big_table
        WHERE

            and (


                )
        ORDER BY sort_column, unique_key

CREATE PROCEDURE previous_page


AS
    --return last page if parameters are null.

        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            ORDER BY sort_column DESC, unique_key DESC
            ) AS Reorder
        ORDER BY unique_key
    ELSE
        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            WHERE

                and (


                    )
            ORDER BY sort_column DESC, unique_key DESC
            ) AS Reorder
        ORDER BY sort_column, unique_key


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.

 
 
 

1. Opening multiple recordset per page

I some asp pages that may perform multiple queries per
page depending on what the user has done.  I'm wondering
what is the best way to accomplish this?  When I nest the
objRS.Open method and Close with an IF statement for some
reason it open and closes it even if the 'IF' statement
wasnt satisfied.  So than later on the page I get error
messages letting telling me I may have 'redefined' and
variable or im trying to open a recordset on a connection
that I have already closed.
Is it best practice to open a connection at the top of the
page regardless and use that connection throught the
processing of that page? Should I not Dim a recordset
object under each if statement? Although I would think the
Dim'img would not take place unless the If statement was
satisfied.
Thanks for any help!
-mike

2. testing

3. Display large recordsets on web page

4. Newbie: LOGICAL Logs -Reply

5. ADO Recordset - displaying n records per page

6. US-NC Oracle Application Developer

7. Using multiple recordsets per connection

8. How to read mSysXxxxx tables from VB?

9. First recordset-action very slow on large recordset

10. Suggestion on how to page through a large recordset

11. copy a recordset's page to another recordset

12. Paging through a large recordset with ADO

13. Module-level multiple-record recordset vs temp single-record recordset