-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
The major thing you can do, besides using a prepare, is to not bother
splitting 502 times, but only enough times so that the last field you
are going to use is included. In this case, the highest element you are
using is [25], which is actually the 26th element to be split. Add one
more and 0-25 will be split, while 26 will be one giant field:
my @line = split(/\|/, $_, 27);
Since you already know exactly which fields you want from the split
result, we can specify those directly like this:
my @line = (split(/\|/, $_, 27))[0,4,5,6,10,11,14,18,22,25];
The parenthesis around the split call force it to be evaluated first,
and then we slice out the values we need. Now @line holds exactly the
elements you want to add in. Just pass that in to a prepared statement.
You can also remove the chomp and the chop, as the ends of the line are
being discarded anyways. Moving the commit outside of the loop should
help too. Here's the final result:
my $SQL = "INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),?,?,?,?,?,?,?,?,?,?";
my $sth = $dbh->prepare($SQL);
while(<FHD>) {
my @line = (split(/\|/, $_, 27))[0,4,5,6,10,11,14,18,22,25];
$sth->execute(@line);
}
$dbh->commit();
On a final note, you may want to create tables in the future that
automagically populate columns with sequences like this:
CREATE TABLE cdl_16master (
myid INTEGER NOT NULL DEFAULT nextval('cd_16_seq'),
...
);
The drawback is that you will need to specify the exact columns to be
filled in the INSERT command, but this is really a good practice to
get into anyway.
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200210291455
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE9vunyvJuQZxSWSsgRAnFKAKD5Fkyl9TzEaRwrNTuX8dqvRa6SCACg3Bzd
pgaJNkoGC2hXvpE23Ko9CaE=
=wtsO
-----END PGP SIGNATURE-----