Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: DBI: How to update 150K records efficiently

by herveus (Parson)
on Mar 31, 2008 at 14:22 UTC ( #677529=note: print w/ replies, xml ) Need Help??


in reply to DBI: How to update 150K records efficiently

Howdy!

Doing transactions that contain multiple updates will buy you some, in my experience. You should do some testing to find out how much benefit you get from various batch sizes.

I'm not immediately able to sort out if that call to update uses a prepared and cached statement. A prepared statement with placeholders will probably be faster than preparing the statement each time.

Are you always updating the one table, or is the code simply an example?

Assuming it's not just illustrative, I'd do:

my $batchsize = 20; # tune this value my $count = 0; my $sth = $dbh->prepare('update myTable set va = ?, vb = ? where vc = +?'); $dbh=>begin_work; # or however you start a transaction; don't take my +word on this line while (my $record = <$input>) { chomp $record; my ($va, $vb, $vc) = (split(/\|/, $record))[1, 3, 5}; $sth->execute($va, $vb, $vc); $count += 1; if ($count % $batchsize == 0) { $dbh->commit; # doublecheck the commit statement too $dbh->begin_work; } }
Now you can experiment with different batch sizes to see how it speeds up.

yours,
Michael


Comment on Re: DBI: How to update 150K records efficiently
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://677529]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (11)
As of 2014-12-22 22:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (132 votes), past polls