|Subject||RE: [firebird-support] Query Performance - correlated subqueries and group by statements|
> Firebird can use more than one index per table for a queryIn what cases could FB use more than one index on a table? My guess
would be only for OR logic in the JOIN or WHERE clause. Are there any
documents I could read on this, besides the FB Book?
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Thursday, April 14, 2005 4:35 PM
Subject: Re: [firebird-support] Query Performance - correlated
subqueries and group by statements
Aaron Abend wrote:
>If you can send the queries and plans, we may be able to suggest
> I am using Firebird and have found that correlated subqueries and
> queries involving group by statements are unusually slow (compared to
> MS SQL Server).
alternate ways of expressing them that perform better.
>Firebird index strategies are slightly different because, unlike most
> 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?
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.
>You can add more page buffers (called cache pages in some places) in a
> 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.
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.
Yahoo! Groups Links