RE: faster update

[PARLEY,JON (HP-MountainView,ex1)]

Greg,

Try turning off autocommit and just call commit once the loop exits.

    _/          Thunder Jon Parley
   _/           Software Engineer
  _/_/_/ _/_/_/ ISSO - Software Mastering
 _/  _/ _/_/_/  
_/  _/ _/       
i n v e n t     [EMAIL PROTECTED]



-----Original Message-----
From: Greg D. [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 03, 2002 12:00 PM
To: [EMAIL PROTECTED]
Subject: faster update


Hello, 

I was wondering if there was anyway i could speed up the amount
of time it takes for my script to run. I know my DB table is quite big, but 
is there anyway to speed up the update in my script or is there something i 
can do in the database to speed it up.

Thanks for any help

here a copy of my script:

#!/usr/bin/perl
 
use DBI;
 
close STDERR;
open (STDERR,">>/tmp/ktjunsub.log");
 
$ktjpath = "/var/www/mail";
$ktjlist = "ktj.lst_rem";
 
$database = "DBI:mysql:maillist:localhost:3306";
$username = "********";
$passwd = '*******';
$dbh = DBI->connect($database, $username, $passwd) || die "Error connecting"

... $dbh->errstr;
 
print "Updating KTJ table\n";
 
open(A, "$ktjpath/$ktjlist") || &death("Can't open $ktjlist: um121");
 
my $sql_statement = "UPDATE ktj SET ktjnews = 'N' WHERE email = ?";
my $sth = $dbh->prepare($sql_statement) || die "Error1 preparing update 
statement on track:\n" . $dbh->errstr;
 
while (<A>){
 
        print "$_";
        $sth->execute($_) || die "Error2 executing update statement on 
track:\n" . $sth->errstr;
}
close A;
 
print "\nfinished\n";
 
close STDERR;