Subject Re: [firebird-support] Re: The age old 'count' issue...
Author Martijn Tonies
Hello Myles,

> --- In, "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,
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

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

Database questions? Check the forum: