--- In firebird-support@yahoogroups.com, "unordained" <unordained_00@...> wrote:

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

I was thinking that I couldn't be the only one affected by this, but I'm really encouraged by the number of smart ideas being posed by everyone here, so I'm sure I'll find one that is a 'best case' scenario for this.

> 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
> specifically.)

That's a pretty slick idea too. I do store the user's last query persistently in a session table. So I would know, on an update trigger, what queries would be invalidated by row count if a record updates. Interesting thought....

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

Right. I have to re-query the database each time the user interacts with the grid. This could be a next/previous page, change sort order, change search criteria, etc. Even just return to the page from navigating to another - that refreshes their last query, hence re-running the search.

I can live with it running the search all the time. That actually is pretty efficient since I have constructed it to only return the rows needed by limiting the SELECT statement dynamically. That works really well. Its the need to run it twice that's where its inneficient. I need to run it once to get the 'cut' of the data from the query. But in order to tell the user/user interface where this cut is positioned in the greater scheme, requires a COUNT(*) to get back the number of rows that it would have returned if the user wanted to get everything. If I can remove that step, I'm good.

This is the problem with feature-rich user interfaces like this. In my case, my users are demanding that their cloud based, web solutions mimic the performance and behavior of their PC desktop apps that they have been using for decades. Its a hard task, but I'm almost there.

I would be ok if there was a way to quickly get a result count from a SELECT statement after it has run, without having to embed the COUNT function into the query itself. In other words, if I could just get a count of the number of rows returned from a SELECT after it has run, in a stored procedure. Like counting the number of results that are in a cursor without having to fetch all the results sequentially to get it.

Myles