I've been searching a lot for this kind of comparison or some sort of performance balance.
What I do know so far is this:
MERGE is T-SQL command that is already implemented on SQL Server. As far as I know, it does perform very well, since it uses "kind of like" INNER JOIN hash mapping for inserting and updating, but I've been having some issues when deleting on the same MERGE statement as well when using CLUSTERED INDEX. Besides that, it's very fast and make let me do some comparison clauses before updating or inserting something, so it's very flexible for me.
In SQL Server some specific scenarios would rather use UPDATE and INSERT as separate statements and I wouldn't argue that. In my short experience, I would use MERGE by default as a standard for my code.
Now as a DBA, I'm facing a new challenge, I have to manage MySQL servers as well, so I ended up looking for similar MERGE behavior in MySQL to improve performance of the queries. So far, I found nothing alike but INSERT … ON DUPLICATE KEY … UPDATE. Still, I have some performance questions, since I'm not sure how it behaves with the server, how does it works and if optional pair of statements would work better or faster than that.
Looking around in MySQL, I found UPDATE + INSERT IGNORE, UPDATE + INSERT, INSERT … REPLACE, and so on..
MySQL documentation is a bit confusing when trying to determinate if I could use some other clauses ex. in MERGE statement I could use AND (TARGET.COLUMN_X > 'VALUE'):
MERGE _TABLE A_ AS TARGET WITH _TABLE B_ AS SOURCE ON (TARGET.KEY = SOURCE.KEY) WHEN MATCHED AND (TARGET.COLUMN_X > 'VALUE') UPDATE TARGET.COLUMN_A = SOURCE.COLUMN_A ...
I don't find how to do this on MySQL.
I have to manage this to achieve better time result and performance friendly.
What I have as setting:
- txt file that need to be uploaded to a table every moth with new data and some changes (this is why I'm looking for a MERGE like statement)
- InnoDB MySQL Engine
- Relational Database tables so I can't delete or truncate the target table because all of them are related.