Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: DBI: How to update 150K records efficiently

by Juerd (Abbot)
on Mar 31, 2008 at 14:15 UTC ( [id://677525]=note: print w/replies, xml ) Need Help??


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

Likely to be a significant slowdown, is the SQL abstraction done by SQL::Abstract. By including this in the loop, you make SQL::Abstract work once for each record.

The solution would be to get back to basics by writing the SQL yourself.

$db->query("UPDATE myTable SET va = ?, vb = ? WHERE vc = ?", $val1, $v +al2, $val3);

By the way, you should not call your DBIx::Simple $dbh. This is the common name for DBI objects, and objects of classes that inherit from DBI. Using the name $dbh for DBIx::Simple objects is kind of confusing. I suggest calling it $db instead.

Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }

Replies are listed 'Best First'.
Re^2: DBI: How to update 150K records efficiently
by lihao (Monk) on Mar 31, 2008 at 15:13 UTC

    Thanks all for the helpful replies. :-)I've adjusted my code to:

    my $sql = qq[ UPDATE `myTable` SET `va` = ?, `vb` = ? WHERE `vc` = ? ]; while (my $record = <$input>) { chomp $record; my ($val1, $val2, $val3) = (split(/\|/,$record))[1,3,5]; $db->query($sql, $val1, $val2, $val3); }

    My question is: can we get the same 'prepare' effects as moritz mentioned in his post, since 'prepare' is done before the while loop..:)

    BTW. thank you for your module, I've used it in most of my applications.

    Best,
    lihao

      My question is: can we get the same 'prepare' effects as moritz mentioned in his post, since 'prepare' is done before the while loop..:)

      That is done automatically. DBIx::Simple recognises that the query is the same each time, and re-uses the old statement handle that represents the already compiled query.

      Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2024-03-29 00:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found