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.

PS: added bonus: you can store in the header how many result rows there
are, as you know that from your INSERT INTO ;-)

Next, on a "next page", you can simply use the search session ID and
display the results very quickly, much quicker than your current system.

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