Monks,
I'm looking for ways to speed up a subroutine that involves the insertion of many (~300,000) records into a PostgreSQL database. At the moment I'm doing a prepare_cached() with placeholders, then running insert() for each record. Is there a more efficient way to do this, possibly based around bind_param_array and execute_array, or should I look elsewhere for my speed gains?
It looks to me like the databasing is the rate limiting step because when I run the program the cpu usage is about 60% while disk IO is going crazy.
I crave the benefit of your experience.
Code follows:
sub insert_nodes_one {
my $sth = $dbh->prepare_cached("INSERT INTO node (taxid, name, tre
+e_id, parent, children, rank, common_name)
VALUES (?, ?, '4', '-1', 'replace me', ?, ?); ")
or die "Cannot prepare: " . $dbh->errstr();
foreach (keys %tax_lookup) {
first_pass($_, $sth, \$n, $count);
}
}
sub first_pass{
my $key = $_[0];
my $sth = $_[1];
my $n= $_[2];
my $count= $_[3];
my $name = $tax_lookup{$key}{name};
$name =~ s/'/_/g;
my $common_name = $tax_lookup{$key}{common_name};
$common_name = 'none' unless ($common_name);
$common_name =~ s/'/_/g;
my $parent_taxid = $tax_lookup{$key}{parent_taxid};
my $rank = $tax_lookup{$key}{rank};
if ($tax_lookup{$key}{rank} eq 'no') {$rank = 'none'}
my $children_taxid = join ' ', @{$tax_lookup{$key}{children_ta
+xid}};
unless ($$n % 100) {print "\r($$n / $count)"}
$sth->execute($key, $name, $rank, $common_name);
$$n++;
}
