Hey all,
I'd be grateful if someone could give me a reality check. I have 250k
rows I want to insert into Postgres using a simple Perl script and
it's taking *forever*. According to my simple timings, It seems to be
only capable of handling about 5,000 rows/hr!!! This seems
ridiculous. This is running on a pretty speedy dual processor P4, and
it doesn't seem to have any trouble at all with big selects.
There are two prepared statements running concurrently, one that
selects the next value of the master sequence, and the other that
inserts a row into the DB.
Is this some DBD::Pg problem? Is this some Postgres problem (is it
recalculating an index of every insert???)?
Any help appreciated.
jas.
PS. Here's the loop:
foreach my $row (@{$matrix}) {
$count++;
$seq_sth->execute()
or $dbh->error(@error_args,
message=>"Couldn't execute nextval from sequence $seq",
sth=>$seq_sth,
sql=>$seq_sql);
my $pkey = $seq_sth->fetchrow_arrayref();
$dbh->error(@error_args,
message=>"Couldn't fetch nextval from sequence $seq",
sth=>$seq_sth,
sql=>$seq_sql)
unless defined $pkey && $pkey->[0];
$sth->execute(@{$row},$pkey->[0])
or $dbh->error(@error_args,
message=>"Couldn't execute insert sql with args: "
. join(',',@{$row},$pkey->[0]),
sth=>$sth,
sql=>$sql);
}
$seq_sth is running a select on a master sequence:
SELECT nextval('"GENEX_ID_SEQ"'::text)
and $sth is running an insert:
INSERT INTO Reporter (name,con_fk,ro_grp,rw_grp,type,rep_pk) VALUES (?,?,?,?,?,?)
