Subject Re: [firebird-support] Query Performance - correlated subqueries and group by statements
Author Aage Johansen
Aaron Abend wrote:
> I am using Firebird and have found that correlated subqueries and queries
> involving group by statements are unusually slow (compared to MS SQL
> Server).
> I have indexed the tables in what I believe is an appropriate way, based on
> experience in SQL Server and Oracle. Are Firebird index strategies
> significanty different?

Post table metadata, sql and plan. Also Firebird version. This may help
us in helping you.

> I also notice that Firebird is not using much memory, which is good, I
> guess, but at a certain point I'd rather let it eat more memory and run
> faster. It's using only 20 mb of memory right now and going fairly slow on
> these subquery and group by statements.
> In Oracle, for example, I can allocate more database buffers to improve
> performance. Is there a similar concept in Firebird?

If you use a pagesize of 8KB and set the number of page buffers to 8K, Fb
will use slightly in excess of 64MB for this database. As you open other
databases these will use additional <pagesize>*<buffers>.
8KB may not be optimal for all your databases, and don't use more than
about 8-10000 pages (for one database).

Aage J.