Subject Re: [firebird-support] Bulk inserts in Interbase
Author David Johnson
He must be looking for one of:

1. datapump tool

2. Mechanism to transfer data from an External table to a regular table
(already described by another contributor to this thread)

3. Mechanism like Oracle's SQLLoader's raw mode - where supplied data is
laid directly into an Oracle table file format without going through the
DB Engine.

4. Mechanism like DB2's that allows an insert statement to accept
multiple rows

insert into myTable (c1, c2, c3, c4)
values (v1a, v2a, v3a, v4a)
(v1b, v2b, v3b, v4b)
(v1c, v2c, v3c, v4c)
ad nauseum

5. Mechanism like Oracle jdbc driver's batch mode, which collects insert
and update statement requests on the client side, then submits them as a
batch at commit or when a predetermined count of pending statements is

6. "hints" (kludgey ways to tell the optimizer your deepest desires)
like Oracle supports.

Recent experience says that (3) raw mode I/O bypassing the DBMS is a bad
idea, and the performance of (2) is likely to be just about as fast and
more reliable. For most purposes, (1) is adequate.

(4) is rarely used in my experience. I would expect it to more than
double the net throughput of a datapump, at the expense of making the
code proprietary to the target database engine. Since I have had no
need to use it I have not measured this DB2 feature.

(5) is valuable in my recent experience for improving throughput for
bulk inserts across a network. It more than doubles the net throughput
of a datapump, at the expense of making the code proprietary to the
target database engine.

(6) Use of the /*+APPEND*/ hint in Oracle improves throughput in this
case by eliminating some of the checks on insert statements. This
violates some of the inherent architecture goals of Firebird, such as
"the software is supposed to be smart enough to apply the correct amount
of checking and auditing". The manual fixing of "plans" in Firebird is
as near to this as is desirable, and even then I have seen enough on
this list to suggest that this approach is a Bad Idea.

On Sat, 2005-01-22 at 10:42, Ann W. Harrison wrote:
> spgilmore wrote:
> >
> >
> > I want to know how to do a bulk-insert in Interbase 6.0 or Firebird
> > 1.x.
> What do you mean by a bulk insert? There are ways of improving
> performance for inserts, but the actual records are stored one at a time.
> Regards,
> Ann
> Yahoo! Groups Links