At 01:40 PM 23/04/2003 +1000, you wrote:

>Hi,
>
>I'm asking on behalf of a colleague. A customer has our factory management
>product running Firebird. It has started running much slower. The file
>size has
>reached 400 MB after about a year of use.
>
>The questions we have are:
>Could the presence of indexes significantly slow down our import process? It
>consists mainly of INSERTS to the large tables, with say 1 million records.

How often do they do a gbak and restore?

>Could the size of the table (~1M recs) significantly affect the speed of
>returning records read by index?

It could. How often do they run SET STATS to rebalance the indexes?

>Would it be sensible to split the big table into smaller tables, and
>either have
>duplication or do Union queries as needed?

No, it would be daft. But it might be worth reviewing the *structure* of
bigger tables, to see whether you are carrying a lot of extra stuff in
denormalized structures, that could be abstracted into "occasional use"
relations.

>Would it be sensible to have separate databases?

Ditto above.

>I expect people to say Need more info, what are the record structures,
>show me
>the plan, etc. But I'm asking in general. What things do people do to
>speed up
>otherwise well designed databases that start running slow?

When a DB starts running slow after performing OK before, it's a cert that
there's a housekeeping problem. See Bill's response about long-running
transactions, too. And, as you observe, we don't know whether your table
structures and index collections are help or hindrance. But good
housekeeping can help keep even inefficient structures comfy.

heLen