If it is going to take longer than a few minutes, you're going to have a really hard time making it work. Even if the server doesn't time out (which it probably will), browsers have their own timeouts as well. The other thing you have to consider is that even if the browser times out, it's possible that the command will continue to execute on the server even after the browser errors out.

The most reliable way to do this would be to write a regular script and execute that.

As for performance tips - add an index on all the date columns, that will severely speed up the lookups (but also slowdown the inserts -- but I bet it will be faster regardless). I remember there being an option in MySQL to temporarily turn off rebuilding indexes (so updates can happen faster), but I can't find it now.

If you really, really, really need to do this over a cgi script, you can use apache::session or something to keep track of exactly which rows need to be updated, and then do them a few hundred at a time, returning to the browser with a meta-refresh in the <HEAD> section. The old UBB bulletin board we used years ago used to do that. It's not perfect but maybe it will work for you.

In reply to Re: Perl and MySQL update question by saberworks
in thread Perl and MySQL update question by cosmicperl

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.