What strategies are there for handling large result sets for web programming?

apprentice has asked for the wisdom of the Perl Monks concerning the following question:

I am using DBI & MySQL to develop my own database driven website and have the following question on strategies:

Suppose I have a query that returns more lines of data than I want to send in a single web page, the data may be from a single or multiple tables, and it may contain only some of the entries from a given table or all of them.

What are some ways of:

A - Keeping track of where I am in displaying the results and either pass that info through a parameter in a "next" link on the web page or store the info in a state file?

B - Storing the data either in the database (is it possible to just ask the db for a range of results without incurring the cost of getting the entire result set?) or in a temporary file of some sort (and what is the best way of cleaning that up)?

Any ideas on what is the most efficient in terms of memory, processing time, disk usage, etc. would be greatly appreciated!

Apprentice

Comment on What strategies are there for handling large result sets for web programming?

Replies are listed 'Best First'.
Re: What strategies are there for handling large result sets for web programming? by perrin (Chancellor) on Jan 23, 2002 at 20:25 UTC
Go straight to the source: Tim Bunce's talk from the last Perl Conference covers this. http://theoryx5.uwinnipeg.ca/scripts/CPAN/authors/id/T/TI/TIMB/DBI_Talk5_2001.tar.gz	[reply]
Re: What strategies are there for handling large result sets for web programming? by gav^ (Curate) on Jan 23, 2002 at 20:20 UTC
The easiest thing to do is to use `LIMIT` then you can restrict the amount of rows you get. From the MySQL docs: The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15 If one argument is given, it indicates the maximum number of rows to return: mysql> select * from table LIMIT 5; # Retrieve first 5 rows gav^	[reply]
Re: What strategies are there for handling large result sets for web programming? by trs80 (Priest) on Jan 23, 2002 at 22:14 UTC
DBIx::Recordset offers a method for doing this, but not sure if you need something this robust/complex. Documentation	[reply]