--- In firebird-support@yahoogroups.com, "Aaron Abend" <aabend@v...>
wrote:

> I did not mean that one can replace CPU usage with RAM. It is just

that in

> any database system, you are going to be bound by memory, CPU, or disk.

I like to think of how it as limited rather than how it is bound.
Where a system is bound tends to be a trend on what limits are being
frequently hit. The truth is that CPU, Disk, and RAM will all limit
the performance of the system, but in your case the CPU seems to be
the cause of the problems.

I am

> beginning to conclude that in fact our app does not need any more

memory and

> Firebird is simply a very CPU intensive program (unlike SQL Server,

which

> performs the same operations on the same computer using much more memory
> (about 4x) and much less CPU).
>

That has not been my experience with Firebird. I have found it to be
relatively efficient with respect to resources, but see below.

>
>
> The problem with optimization is that our application assembles

complex SQL

> (correlated subqueries, joins, set operations, the works) on the fly. So
> it's not as though we can take a query and test it in advance. We

can trap

> the queries that we suspect and test them, but the shape of the data can
> vary in each user's system.
>

Fair enough, what you need to determine is whether the queries that
are being run are doable in an efficient manner with the index
structures you have given it.

>
>
> We do all our testing on large real-life datasets with about 50,000

records

> after we do development on demo datasets. Having done database

application

> development for 25 years, I have thought of most of the obvious

errors, but

> I do appreciate your ideas. It is the Firebird-specific stuff that I

really

> need help on.

> My thinking right now is that our indexing, which drew from
> experience with other databases, is probably not optimal for Firebird.
>

Your thinking here may be right. Firebirds indices are directional,
whereas a lot of other databases are not. In other words, an ascending
index can not help you in a select max() query, or a descending order
by statement.

If you have ported concepts from one system to another, you may need
to add a descending index on some of your fields.

>
>
> Another thought - is there any way to reduce the priority of the

Firebird

> process in the config file? Is there any harm to reducing its priority?
>

I would look for the source of the problem, in either case, even if
the priority was lowered, if your application was waiting for the
database to run queries, it is not going to speed it up

>
>
> We'll keep exploring the problem machine to see what we come up with.
>

Make sure you check out Anne's suggestion, if you are building up a
lot of garbage, then you may want to turn off auto sweep and run it
"overnight" or whenever your system isn't used..

Hope that helps
Adam