I need to run below UPDATE query one by one for ~500 times on MySql table. I am wondering which of these two will have better performance?
UPDATE 'latency_table' SET 'is_active' = null WHERE rule_id = 1;
UPDATE 'latency_table' SET 'is_active' = null where rule_id in (1...500);
In #1, I am accessing table ~500 times but in #2, I am accessing table once, but still accessing rows 500 times. Is #2 a more performant solution?
Every statement you send to the DBMS must be parsed. So you send the DBMS your SQL and the DBMS says: "Ah an update. Which table? The latency_table. Which rows? Row ID 1. Let's think about how to get there quickly... Do I have an index? Yes, so for one row it makes sense to use it. What columns to update and with which values? Column is_active and null. Okay. Let's do this."
For the single statements you do this about 500 times. For the second just once.
Then you fire the statement from somewhere. So that somewhere must talk to the DBMS: "Hey, I have a statement for you". - "Thank you, I've just checked it. It looks okay." - "Alright, then please execute it." - "Done. Your update affected 500 rows." This happens for whichever app, even if this is some DB tool like phpmyadmin. This is called round trips. This smalltalk happens for every statement you send.
At last, with single statements you don't give the DBMS any chance to optimize. Let's say you are updating 500 rows ot of 600. That's almost all rows. If you send a single update, the DBMS will search this one record. If there is an index, it will quickly find it by binary search (Look at row #300. Smaller? Look at row 150. Greater? Look at row 225...) Otherwise it must read the whole table. For every single statement. 500 times. If you send only one statement for 500 rows, the DBMS will certainly notice that it would be a hassle to run through the index for every rule ID and would rather just read the table sequentially once and be done.
Maybe the DBMS can even execute your statement parallelized. Four CPU cores? Then every CPU core may care about 150 of the records at the same time.
So: Many reasons to do this in one single update statement. And this is what SQL is all about after all. Don't worry about how the DBMS might approach the problem. Tell it what to do (update those 500 rows) and not how to (one by one). It may even decide to execute the 500 rows as if you had send 500 single statements and still be a lot faster, because of parsing only once and doing far less round trips.
In the first solution, you go back and forth from the client to the database over the network - an update statement is issued, the result returned to the client, another statement is issued, and so on.
In the second solution you send everything to the database once, let it do its thing, and only then get a response. That would definitely be a better approach.
all of things depends of your hardware,you must to run 500 times your command then if you want or not it cause high load in your machine but if you want run command as quickly as possiable second command may help you,but i have my own advice for you if you wanna manage machine resources you must to monitor when you run the commands and you can write a shell script to manage resources and "sleep" time on your script due too
