Daniel, Svein

Thanks for the replies. I'll try to be more clear.

Since the delphi+ibx situation has a bunch of other contributing
factors, i'll stick to the perl script i'm using for now. It DOES use
prepared inserts. I'll include the bulk of it below.

The reason i feel it is slow, is that I converted it directly from using
MySQL. I ran the exact code below today, on the same machine, with both
MySQL 3.23.56 and FireBird 1.5RC2 using local connections to files on
the local filesystem. MySQL inserted 657534 records in 161 seconds (4084
records / sec). InterBase inserted 657534 records in 294 seconds (2236
records / sec).

I'd like to bring these numbers closer to each other, if possible.

--begin perl code
#!/usr/bin/perl
my $dbh;
if ( 0 ) {
$dbh = DBI->connect("DBI:mysql:postal_db", "root", "")
or die "Couldn't connect to postal database: $DBI::errstr\n";
}
if ( 1 ) {
$dbh = DBI->connect(
"DBI:InterBase:/opt/postal.gdb",
"SYSDBA",
"masterkey"
);
}
$dbh->{AutoCommit} = 0;

# Rebuild the tables
#my $tabledel = "drop table if exists delstat";
my $tabledel = "drop table delstat";
my $sth = $dbh->prepare($tabledel)
or warn "Can't prepare the SQL statement: $DBI::errstr\n";
$sth && $sth->execute()
or warn "Can't execute the SQL statement: $DBI::errstr\n";

my $tabledef = "CREATE TABLE delstat ("
. " zip integer not null, "
. " rt_type char(8) not null, "
. " car_rt char(4) not null, "
. " bca integer not null, "
. " bcp integer not null, "
. " rca integer not null, "
. " rcp integer not null, "
. " gca integer not null, "
. " gcp integer not null, "
. " state char(2) not null, "
. " county char(3) not null, "
. " municipality char(6) not null "
. ")";
$sth = $dbh->prepare($tabledef)
or die "Can't prepare the SQL statement: $DBI::errstr\n";
$sth->execute()
or die "Can't execute the SQL statement: $DBI::errstr\n";
$dbh->commit();

my $insert_stmt = "INSERT INTO delstat (zip, rt_type, car_rt, bca, bcp,
rca, rcp, "
. " gca, gcp, "
. " state, county, municipality) VALUES "
. " (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$sth = $dbh->prepare($insert_stmt);

##### SNIP
##### Define record format and open input file

my $stime = time();
my $count = 0;
while((my $length = read IN, my $line, 219) != 0){
###### SNIP
###### calculate some values from $row
######
$sth->execute($row[1] + 0, $rt_type, $row[4],
$active_business, $possible_business,
$active_residential, $possible_residential,
$active_general, $possible_general,
$row[-4], $row[-3], $row[-2])
or warn "\n${row[1]} ${row[4]} $active_business $active_residential
$possible_business $possible_residential $active_general
$possible_general\n";
if ($count % 1000 == 999) {
$dbh->commit();
}
}
$dbh->commit();
my $seconds = time() - $stime;
my $recpersec = $count / $seconds;
print "$count records inserted in $seconds seconds ($recpersec records /
sec)\n";

# Add an index on zip
$sth = $dbh->prepare("create index DELSTAT_IDX1 on delstat (ZIP)");
$sth->execute();
$dbh->commit();

$dbh->disconnect()

--end perl code

Daniel Rail wrote:

> Hi,
>
> At May 22, 2003, 16:14, Erik S. LaBianca wrote:
>
>
>>I wondering if any of you have had any luck speeding up inserts to
>>interbase? I've found that using both DBD::InterBase and IBX under
>>delphi insert operations are quite slow. I've tried playing with my
>>transaction component settings in delphi to no avail.
>
>
>>Is using an external table the only way to get data in faster?
>
>
> Use an insert statement with parameters, so you only have to prepare
> once. If you prepare the insert statement everytime, that usually is
> a performance penalty. IBX might prepare the statement, even if you
> don't explicitly do.
>

--
Erik S. LaBianca <erik@...>
Developer / System Administrator, Interlink, Inc.
Voice: (269) 473-3103 Fax: (269) 473-1190
http://www.totalcirculation.com