That's a message for dbi-users. I've directed followups there.
Tim.
On Thu, Nov 21, 2002 at 09:25:13AM -0700, Jason E. Stewart wrote:
> 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 (?,?,?,?,?,?)
>
>
>
>
