in reply to speedy update routine

Both routines would be improved by reducing or eliminating global variables, and passing subroutines all the parameters they need. It would help to go through the exercise of converting your script to run under strict.

The first version would be improved by preparing a statement handle once, using placeholders, 

my $sth = $dbh->prepare(
    q(UPDATE url_table SET hits=? WHERE url=?));
while (my ($url, $hits) = each %urlhits_update) {
    $sth->execute($hits, $url);
    # ...
}

[download]
The improvement here is not only in performance, but also in the safety of the DBI quoting mechanism invoked by the placeholders.

You won't be able to LOAD FILE from an in-memory file you've constructed. That would require a) A db server which can do so, and b) shared memory between the server and your perl process. Neither is likely.

Is 0.26 seconds really too slow?

After Compline,
Zaxo

Replies are listed 'Best First'.
Re^2: speedy update routine
by js1 (Monk) on Jun 12, 2004 at 21:48 UTC

    Thanks for your reply. I'd forgotten about the placeholders.

    Will getting rid of the global variables and passing parameters actually improve performance or is that just good programming practice?

    Lastly, the update routine takes over a second. The insert routine takes 0.26 secs which I'm happy with.

    js.

[reply]
      Will getting rid of the global variables and passing parameters actually improve performance or is that just good programming practice?

      Good practice, mainly. It may improve performance, but only indirectly. By reworking your code with attention to variable scope, you may see simplifications which speed things up.

      Don't get too caught up in timing performance. Correctness is far more important.

      After Compline,
      Zaxo

[reply]

In Section Seekers of Perl Wisdom