Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Re: DBI: How to update 150K records efficiently

by sundialsvc4 (Abbot)
on Mar 31, 2008 at 20:46 UTC ( #677636=note: print w/replies, xml ) Need Help??

in reply to DBI: How to update 150K records efficiently

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.

  • Comment on Re: DBI: How to update 150K records efficiently

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://677636]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2018-05-26 12:55 GMT
Find Nodes?
    Voting Booth?