Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: DBI: How to update 150K records efficiently

by Juerd (Abbot)
on Mar 31, 2008 at 14:15 UTC ( #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' }


Comment on Re: DBI: How to update 150K records efficiently
Download Code
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
Node Status?
node history
Node Type: note [id://677525]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (11)
As of 2015-07-02 02:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (25 votes), past polls