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

use a table count table. It requires occasional housekeeping.
Tablename, record count fields in a table.
Update them with select count(*) for each table.
Use triggers to insert -1 on delete with table name and +1 on insert with
table name.
select sum(tcount) where tablename='name' for a fast count.
Then fire off occasional housekeeping at low use time schedule to delete all
records where tablename= and replace with select count(*)'s again.
Alan