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. You
can only become bound on a second resource when one is used up. If memory is
being bound by a config file (I still do not know why it is not using more
ram on this system) then the CPU will be next unless there's a table scan
that is eating disk io. This is true in every database I have used in the
past 20 years, and basically they all work more or less the same way. 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).



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.



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.



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?



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



Thanks,



Aaron

>

Why do you think that it is possible to replace CPU usage with RAM?
CPU is what processes data, more RAM just gives it more options in the
manner it processes. While it is true that some operations can be done
faster at the expense of extra memory, not all operations can be.

The normal reason for this sort of behaviour is a rogue query. Often
queries perform well in test environments because in order to speed up
development, you tend to use smaller datasets to test against. A full
table scan of a 100 record table is not going to be noticably slow, if
it has 10000 records, let me assure you it will be noticed.

> Inactive indexes?
>
> I would say "no" because all machines use the same app. But

the app

> drops and recreates indexes and it is remotely possible that the

indexes did

> not get rebuilt.

Unless you are pumping data, and you have specific performance targets
for the insert operation into the table, I would avoid stuffing around
with tne indices at all.

There are a couple of things you may not have considered.

1. Triggers!

What queries are they running, and when are they being run. There is
often a lot of room for optimisation at this point. Be aware of
triggers recursively impacting the same table.

2. Language.

It concerns me when you say that you are going through the code to see
exactly what the queries are. It may be a throw-away line meaning you
will recheck everything, but it may also mean that they were never
checked for plans in the first place.

I would suggest using a tool such as IBPlanalyzer. For every query you
run, check the plan. Make sure that table scans do not happen, or if
they do you can guarantee that only a few (<100) records will ever be
in that table. Firebird 1.5 often performs better when some where
conditions are moved into the join condition, but this is related to
the plan it chooses, so it will become evident if you check what
exactly is being run.

I think you are probably barking up the wrong tree with respect to the
RAM theory. I bet it is a query related performance issue.

But finally, some encouragement.

We had a problem where a report would take 10 minutes to open on site.
After bringing the database back to analyse, I noticed that the
selected plan involved a tablescan because it was written poorly and
it had left joins (that could have really been inner joins). Spending
10 minutes with the query in the optimiser, the report now opens on
that site in 2 seconds.

Adam





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
<http://docs.yahoo.com/info/terms/> Service.



[Non-text portions of this message have been removed]