--- In firebird-support@yahoogroups.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