Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

I'd summarize my thoughts this way (most are redundant):

  1. Sometimes the abstractions provided by a class are good and handy; sometimes “desperate times call for desperate measures” and this is a good example of that. What you have done looks fine.
  2. Using a prepared query is definitely helpful.
  3. It is wise to code any such update-routine defensively, even at the expense of speed. You are, after all, about to update 3/4 of the entire table!
  4. If your database-manager supports it, transactions can be very helpful:   start a transaction, do a few hundred updates, then commit, then start a new transaction. (Don't forget to commit the last one.) Study “transaction isolation levels.” Consider how much concurrent table-activity you can... or should... tolerate.
  5. Since you are, indeed, updating such a large percentage of the table, and such a large table, what if you did things very differently?
    • Upload the information in-quantity to a different table by whatever means.
    • Then, do a transaction on the server which orders it to empty the first table, insert into it from the new table, then commit.
    Bang! Now all the updates happen, when they happen, entirely on the server. Or maybe you can actually DROP the old table and rename the temporary so that it becomes permanent. “Desperate times...”

No matter how you choose to do it, this is going to be a “10,000 pound elephant” operation. It's going to be a whole lot slower than a lot of the things you're doing, and when it sits down or goes anywhere you're going to feel the floorboards shake.

In reply to Re: DBI: How to update 150K records efficiently by sundialsvc4
in thread DBI: How to update 150K records efficiently by lihao

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, 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, 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.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others lurking in the Monastery: (6)
    As of 2018-06-21 13:28 GMT
    Find Nodes?
      Voting Booth?
      Should cpanminus be part of the standard Perl release?

      Results (118 votes). Check out past polls.