M Patterson, Multinail 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.
>

Important Question: What exactly has gotten slower? In there any particular query which is slower?
Any particular operation? How fast is it running and how fast did it run before?

> 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.

The presense of indexes IMHO tend to slightly slow down an import process.
Unless the import process is in every other way already highly optimized it almost makes no difference.

Usually people getting import performance problems are importing records in an inefficient manner,
and in those cases the presence of an index is very unlikely to make a noticible difference.

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

Not really significantly. But it depends on what you call significantly.

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

That would serve no purpose. 1 million rows is NOT a large table.

> Would it be sensible to have separate databases?
>

Usually not. Not to hold the same closely related data.

> 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? And I'm the Firebird
> sponsor at our company, working on a different project now.
>

It depends on what exactly is the "running slow" symptoms you are seeing. Well designed databases start running slower due to something unforseen.
But without knowing what exactly you mean by "running slow" it would be difficult to imagine what it is. It is not NORMAL in any event.

Just for your personal interest. At this company we import about 1 million rows PER DAY, into a single table
with about 2 hundred million rows. The table has 31 columns, and 2 multi-column indexes as well as a BEFORE INSERT and AFTER INSERT
trigger, both triggers perform a SELECT against 1 or 2 other tables.

The entire process takes about an hour, and to tell you the truth, It is performing the inserts in a NON-EFFICIENT manner.

The actual part of the import which engages the large table takes less than 10 minutes.

First we dump all data into a non-indexed table prior to moving them to their permanent location in the indexed table. This first import step
is done over the network and a non-efficient manner and it takes about 50 minutes.

The total size of the database in question is somewhere between 30 and 40 gb. (we keep it less than 40 gb).

David Z