I think one of the reasons that people disagree on this subject is that Firebird users use the database for very different purposes. For a small database with a few thousand records within a table, and possibly connecting through the internet and wanting to transfer large records across a slow internet connection, I'd say an exact count of records for displaying a progress bar might be a good idea. In such circumstances, counting 1000 records on the server may be quick even when compared to transferring one single record.

However, many of us work with large tables containing many millions of rows, have complex (and time consuming) queries or have a database that is in heavy use. In such situations, counting the rows before a transfer may at worst almost double the time it takes to get the records.

I would agree with Woody that select count isn't always problematic, but would object to it as never being a problem. Doing things as Martijn indicates (showing how many have been transferred so far) generally doesn't have any problems except that the user doesn't know how much is left - they may see that progress happens, and that is normally the most important thing for users.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody
Sent: 10. desember 2008 16:12
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: If Not SELECT COUNT(*), Then What?

From: "Martijn Tonies" <m.tonies@...>

>
> If there is a case when a user needs lots of rows (eg: an
> export of data), I never display "progress", only that the
> application is busy and perhaps display the current number
> of rows exported.
>
> In all other situations, the number of rows requests should
> be small that no notion of "progress" is required.

I have had clients specifically request such things as current count versus
total count so it's not always a choice you can make as a developer. For
instance, in one of my apps, the client wanted to always see a total count
of records that matched the filtering set for the form. These are not grid
type forms, they only display one record (set) of information at a time so
there isn't any other way of visually informing the user of the number of
matching records.

While we can certainly tell clients that doing a count prior to fetching the
information does increase the time, I've never had any count take enough
time to make any difference anyway. And I use SELECT COUNT for most of my
report generation code to display a progress bar so that longer reports,
such as monthly and fiscal data, give the user some feedback since they
generally take a little longer to run.

With proper usage and a good database design, I see no problem using things
like SELECT COUNT for visual feedback of some type to the user.

Just my $0.02

Woody (TMW)