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

In reply to Re: speedy update routine by Zaxo
in thread speedy update routine by js1

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.