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

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

Database questions? Check the forum: