Re: Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE
Thanks again for the feedback. !!
My current problem is to optimise data processing which is updating several rows from a single incoming message.
So batching it is something I cannot really manage, I couldn't see how to do the "update many" in the JDBC Java Driver... Then I gave it a deeply thought I believe it cannot be done.
We use transaction, but for the whole message. one message has one or more layer (around 30) which is each of those layer can do multiple updates (up to 100 rows).
And if any unexpected happens the whole message, those 30 by 100 updates needs to be rolled back. This scenario happens almost never.
Right now the development team is doing INSERT ON DUPLICATE UPDATE, and when I did the analysis I saw the inefficient of that statement when 95% of our message is to update those records, not to insert new ones. Thus the change to UPDATE and if fails, do insert.
I need to do some test to identify how from the BackEnd to the MYSQL the update are processed. I mean, I believe it is managed by one one.... so cannot see how to do batching at that level.... what instructions to send to MYSQL to tell: look the next 100 updates are from the same table, just different records. Optimised please!.
However, I cannot forget one of those update might fail (not found), so we need to do an insert.... Again, not batching allowed here (or haven't founded yet).
:)