--- In firebird-support@yahoogroups.com, Elmar Haneke <elmar@...> wrote:

>
> > I have a program done with Delphi. This inserts row by row and this
> > takes to long.
> >
> > What's the fastest way to make a Batchmove from Oracle to Firebird?
>
>
> In theory using external files sould be most performant.
>
> In practice you should first make shure that
>
> * Inserts are done by using prepared statements
>
> * Transaction is not committed to often. Especially an "auto commit"
> does break performance since it has to create an new transaction on each
> insert.

It is worse than that, you have an additional round trip to start a
transaction and a round trip to commit the transaction.

But the OP hasn't really provided enough information. Performance
problems are relative things. It takes a length of time to complete an
operation. In this case, that length of time is unacceptable. We need
to know what is trying to be achieved (in terms of numbers of records
and in terms of acceptable timeframes) to determine where to look or
even if it is possible.

The following features require additional time when inserting

* Forced Writes
* Indices
* Insert Triggers
* Foreign Key / Unique constraints / etc
* Query preparation
* Round trips between application and server
* Source and destination database on same physical drive (thrashing)

If you are the exclusive user during a data pump, and you are 100%
sure of the consistency of the source data, you may be able to
temporarily disable or drop these features during the import.

To minimise query preparation and round trips, your query should be
prepared once and using parameters have the values substituted each
time. Some people like to commit every couple of thousand records
during a pump, there is probably some reason. Certainly committing
every record or using autocommit in your Delphi component (which
internally does the same thing) is a sure way to make it crawl.

You should be able to get on modest hardware 10000 inserts per second,
although that depends also on the width of the table and the indices
and triggers.

Firebird does support the idea of external tables, where you can
fashion most data into a pretty crude format (no nulls etc) that
Firebird can understand. And you can use an insert into ... select ...
from ... clause to load it into a real table.

Using embedded Firebird is even quicker still.

But before you go down any road, be sure you are not within your
pumping application introducing inefficiencies.

Adam