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

Hmmm. In my experience, the inefficiency isn't in the query itself, it's
usually in the size of the result set returned over the wire to the client.
Firebird can run through a query internally quickly, and even cache it fairly
quickly, as long as you don't want to return it. So, with that in mind, how
about the following:

Modify the stored procedure so it runs the *full* query, not limiting the
number of rows. But it doesn't always call SUSPEND to return the result;
instead, you keep a running row count, and only call SUSPEND when in the right
row range (as requested, by page# or row range, by the client). When it
finishes, it sets a variable (temporary table or rdb$set_context) that has the
number of rows returned by the full query (row_count). After running a select
to get a partial dataset, you then also go fetch that value for your total row
count. (Same transaction, would be able to see the value via rdb$get_context,
or in the temporary table.)

I'm curious what the performance would look like on that. It's just idle-
looping through results, inside the SP ...