Subject Re: [firebird-support] Re: The age old 'count' issue...
Author unordained
---------- Original Message -----------
From: "vladman992000" <myles@...>
> 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.
> Myles
------- End of Original Message -------

I assumed you already have to deal with all the various search options in your
stored procedure, didn't figure that'd be a problem. In fact, you could keep
all of this "under the hood", modifying your SP without changing anything else.
I do understand what you mean about the ever-changing state of the database; in
an OLTP environment, we always want to give our customers the most up-to-date
information available. I completely agree.

A low cache timeout period is one solution, a fancy trigger-based cache
invalidation would be another (you could store the search parameters in the
header record in such a way that database changes would fire a trigger that
would look for any searches "affected" by the change, and invalidate them

One thing about "paging" on active datasets though that really annoys me: I've
been playing with the "" service, and hitting "next" on "recent
images" very often shows me the same stuff I just looked at, because it goes to
the next page by offset from the newest item, not by last-id-on-rendered-page;
so if I'm slowly looking through the page for anything interesting, I'll find
myself hitting "next" twice just to get to more new content. I actually
wouldn't mind a more stable search, sometimes.

In your case, you're worried about result count, yet the result count should be
changing every time you change pages, as it's requerying the active database
and data may have changed in the last 5 seconds. So your problem isn't just to
get it once, but to get it every time, and possibly fix the "number of pages"
information on your GUI, every time the user pages. Eeek! If you could have
efficiently returned all 100k results by AJAX as you originally attempted, you
wouldn't have been worried that the user was getting slightly old information
as they paged client-side, right? Is this really any different?

(I know, this is getting off-topic for FB proper. Sorry Helen!)