Subject Re: [firebird-support] The age old 'count' issue...
Author Milan Babuskov
Myles Wakeham wrote:
> There is one last part of this optimization issue though and it has to
> do with getting the total number of rows found in the query. I
> understand that using COUNT(*) is a bad idea. But in order to know how
> many pages of data the user can browse through, I'm completely at a loss
> as to how to get this number dynamically.
> Can it be done? Are there any tricks to do this sort of thing?

I removed the total number of pages from my applications for all the
tables that have a lot of records. Instead I use the following approach:

If you show, say, 50 records per page. Build a query that selects FIRST
51. You will display only 50, of course and show user an option "More
records" if there is 51st, or just stop if there are no more.

That was a first step. Soon after that I added option to navigate up to
5 pages back or forth. So, for this, you would select FIRST 251.
Selecting 251 records still performs very well and you get ability to
build nice navigation controls like:

First | Prev | 2 3 [current] 5 6 | Next | Last

To get the last page, I just toggle the ORDER BY clause and before
displaying records I fetch them into PHP array and then print on screen
in reverse. So it really shows the last 50 records in dataset.


BTW, I'm also currently looking into this:

and wonder if it could be made usable with Firebird.

Milan Babuskov

The easiest way to import XML, CSV
and textual files into Firebird: