Hello Myles,

> --- In firebird-support@yahoogroups.com, "unordained" <unordained_00@...>
> wrote:
>> You could try to cache the resultset database-side; keep a table of
>> recent
>> searches and the results that went with them (delete header and details
>> after
>> some expiration period.) When you first call the stored procedure, it
>> runs the
>> query and dumps all the results into the detail table, then returns from
>> there
>> only the rows requested (first N skip M) as you're doing now, plus can
>> relatively quickly get the row count. On subsequent calls, it could match
>> the
>> query parameters to current entries in the recent-searches table; that'll
>> match
>> that user's previous search, and possibly from other users (if desired --
>> depends on your security model), saving time. It may be less expensive to
>> push
>> the data from a query directly into a local table than to download the
>> whole
>> resultset to PHP for parsing. It may be possible to store less than the
>> full
>> resultset in the database, depending on the joins at query time vs. disk
>> speed
>> when caching the data.
>
> I thought of storing the previous results set in a cache, but the problem
> is that the number of search options available to the user and constant
> changing state of the database would make this impractical. There would
> be too many caches required to make this a reasonable approach. But not a
> bad idea though.
>

Does this mean that a "View Page 2" link -reruns- the search query? If so,
you
should really rethink that strategy.

Storing the search result into a table (create a "header table" with a
timestamp for
the query, use a "detail table" for the actual result records, run a job
every so often
to delete old searches) really is the way to go.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com