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

If you can send the queries and plans, we may be able to suggest
alternate ways of expressing them that perform better.

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

Firebird index strategies are slightly different because, unlike most
other databases, Firebird can use more than one index per table for a
query. Thus if you want to use fields A, B, and C to restrict input,
you can index each separately, rather than creating a compound index on
A, B, C. There is also less of a performance penalty in Firebird for
the use of secondary indexes.

However, Firebird indexes do suffer when there are large (> 10,000)
numbers of instances of a single value in an index. Check the list
archives for discussions (many many discussions) of index selectivity.

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

You can add more page buffers (called cache pages in some places) in a
variety of ways - through the configuration file, using a server
setting, or using a tool to set the cache size for a particular
database. The size should not be larger than 10,000 until Firebird 2.

>

Regards,


Ann