----- Original Message -----
From: "Rob" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 27, 2002 4:00 PM
Subject: Slow Postgres Update
> 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";
1. Set AutoCommit to 0 and commit in batches (say 100 rows).
2. Make sure there's an index on PIN.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
