3

I have a program which is time based. The users have all day to enter data that will affect their score, however if you haven't entered anything by midnight then your score is negatively affected. I had thought about setting up a chron job to handle this at midnight, but am then faced with a batch process that may be updating hundreds of thousands of records all at once.

I've decided one way to reduce this would be to divide the batch into 1000 records and then release the event loop, stack on another 1000. Is this a good way to go about something so massive? Would creating a second server instance and offloading the work to that server be a better idea?

closed as unclear what you're asking by mustaccio, Vérace, RDFozz, Colin 't Hart, Max Vernon May 30 '18 at 20:01

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

Please provide proof that 500k rows is a lot for your database.

Hint In most cases, it isn't.

Although this answer could befit a Comment, this is a valid Answer.

Databases deal with MILLIONS of rows at a time without a hiccup. 500k is nothing.

Things you need to be aware of when testing the single DML approach

  • Undo tablespace (or MySQL's equivalent)

  • possibly temp tablespace (for indexes)

  • locks on a heavily modified table.

If you have proof that one (or more) of those is causing issues, then you should consider Chunking your UPDATE statement.

You haven't given any indication that any of those is a problem. So, the answer is: Neither. Use a single DML.

As far as "process on separate server"...I don't see the need. I foresee too many headaches with synchronizing the processed rows with any modified data that occured during the Processing.

  • The complexity of the db updates is not what worries me, they are async. This is essentially a giant for loop and each of those calls takes time to begin and callback, which is why I asked if batching them was appropriate. – KAT May 30 '18 at 17:03
  • Processing 500k rows with a single UPDATE statement is faster than processing 1 row at a time in a giant for loop. Even if the UPDATE is complex, store "what you want to do" in a scratch table ( via INSERT..SELECT) then do the UPDATE (other RDBMS would call it MERGE). Basically, you want to do as much work as possible in every DML call. Don't treat the database like a bit bucket by accessing every row individually. Databases suck at doing that. – Michael Kutz May 30 '18 at 17:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.