> Sorry :-( I there is a typo in the:
>
> select count(*) from abc where J > 500; ....
> FIREBIRD = 30.26 sec
>
> I haven't got the actual results at home, will check on Monday but am sure
> it was 0.3 sec not 30. I also remember there not being any differences in
> the times on indexed or non-indexed columns. It was purely to do with the
> number of rows counted.

Which would actually make sense.

> Hypersonic is caching most of the table in memory. The hypersonic guys have
> told me that in default configuration which I was testing it caches 50,000
> rows. Hence its large memory footprint compared to firebird. Is there a way
> of getting firebird to do more caching? My usage profile is mostly selects
> and few updates other than initial batch insert. The total dataset is only
> about 30-50Mb at the moment and mill probably not grow over 100Mb, so should
> be feasible to have the whole thing in memory on must people machines these
> days.

You can adjust the number of pages in a Firebird Cache using the config file
(firebird.conf) and adjusting the setting

# ----------------------------
# Number of cached database pages
#
# This sets the number of pages from any one database that can be held
# in cache at once. If you increase this value, the engine will
# allocate more pages to the cache for every database. By default, the
# SuperServer allocates 2048 pages for each database and the classic
# allocates 75 pages per client connection per database.
#
# Type: integer
#
#DefaultDbCachePages = 2048

> Also I am running in a single threaded, single user environment(A desktop
> application) so is there is any options that I can turn off with firebird to
> speed things up?

You could look at using the embedded engine rather than the default....

Regards
Paul