Just my 2 cents - you have to know the total count only when you're
asking for last page. If there is next page you can obtain through query
like SELECT * from table rows ((page_no - 1) * recs_per_page) + 1 to
(page_no * recs_per_page) + 1 and test number of records returned (if
number of records returned is higher than recs_per_page there IS a next
page). That's a trick we're using in our apps. And for last page we're
using this: If there is a prediction of many records returned by query
we're not allowing this feature. Only way to allow it is implementation
of filters required to fill by user.

And one opinion at the end - can't you use something like this:

CREATE TABLE test (
ROW_NO bigint
)
CREATE DESCENDING INDEX IX_TEST_ROWNO ON TEST (ROW_NO);

and maintain ROW_NO as a continuous (it means without spaces) and use
max() instead of count()? Max() can use that descending index so it will
be really fast, but there can be a problem to maintain that ROW_NO values.

M. Tomes

Dne 23.02.2010 14:47, vladman992000 napsal(a):

>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, "emb_blaster"
> <EMB_Blaster@...> wrote:
> > There's no way you run twice the query and it doesn't slow down your
> query. One suggerstion is that you limit the max number or rows that
> you procedure will count, so you can paginate till it... example of
> returning to user 20 rows for page:
> > row1
> > row2
> > row3
> > ...
> > page 1, page 2, page 3,page 4,page 5,.... page 50, More pages...
> >
> > So your procedure will count first 1001 results to see if theres
> more than 100 pages but in true will show just first 100 pages...
> > I think that searches engines works almost like this (despite the
> aproximated search results it can show)...
> > I don't know if it will be a good idea for your case, but it's a idea :)
>
> Thanks for the feedback, but I'm somewhat constrained by the fact that
> I'm using a 3rd party Javascript/AJAX style grid tool for row results,
> so the user can sort by column, etc. The tool provides 'Go to First
> Page', 'Go to Last Page', 'Next Page', 'Previous Page', type
> navigation, and any user click event on the grid sends back an AJAX
> query to the server to re-issue the query.
>
> I've tested this with stored procedures returning 30,000 rows back and
> performance is not too bad. The problem I'm having is when I get
> 100,000 rows back from the query. I do filter it down to just
> returning the result set for the page - that's working great. But in
> order to know if there is a next page, or provide them an ability to
> go to the last page, I need to know the total count of results. I can
> probably handling doing two queries in the stored procedure, but what
> I don't know is how to get back the total number of rows found from a
> query, within the stored procedure other than looping through the
> result set.
>
> Is it possible to get the total number of rows returned from a query,
> without having to tell the query itself to count(*), or without having
> to transfer the entire result set to a client application to count
> them outside of Firebird?
>
> Myles
>
>

[Non-text portions of this message have been removed]