Thanks Ann,

Very helpful info. I just saw a post by Daniel Rail saying hyperthreading
slows superserver down. If so, that may be the problem. All of us in
development are hyperthreaded. I am going to check performance with ht
disabled to see if that helps. If not I will post more info.

Thanks,

Aaron


-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Thursday, April 14, 2005 4:35 PM
To: firebird-support@yahoogroups.com
Cc: chris@...
Subject: Re: [firebird-support] Query Performance - correlated subqueries
and group by statements


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




Yahoo! Groups Links