Slow Postgres Update

[Rob]

The below updates a database from a very large file but it's rather slow;
How can I speed it up some?  I'm still new to database programming with
Perl so if you see anything else that I'm doing wrong or could do better
don't hesitate to point it out.

#!/usr/bin/perl -w

use strict;
use DBI;

my($sql, @row, $message, $pin, $pageCnt) = "";

my $database = "dbi:Pg:dbname=paging";
my $db_user = "rob";

my $dbh = DBI->connect($database, $db_user, "",
     {AutoCommit => 1},
     ) or die "Can't connect to database\n" . DBI->errstr;

$sql = $dbh->prepare_cached('UPDATE data SET TotalPages = (TotalPages + ?)
            WHERE PIN = ?');


#process file and update db
open(IN, "CNTR2000.ASC") || die "Can't open input file: $!\n";
open(ERR, ">zetron.err") || die "Can't create err file: $!\n";
while(<IN>) {
    chomp;
    $pin = substr($_, 1, 7);
    $pageCnt = substr($_, 139, 5);
    if($pageCnt > 0) {
        $sql->execute($pageCnt, $pin)
                or print ERR "$pin\t$pageCnt\t$sql->errstr";
        $message = $sql->errstr;
        unless ($message) {
            $message = "Record Updated!";
        }
        print "$pin\t$pageCnt\t$message\n";
    }
}
$sql->finish;
$dbh->disconnect;
close(IN);
close(ERR);

print "\n\nAll Done!\n\n";

Rob

Good judgement comes from experience, and experience -
well, that comes from poor judgement.




-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]