I have a program which takes a few hours to run and does lots of different queries along the way. Individual queries take 3-4 minutes, but I need to run a large number of these sequentially in order to obtain the final result.
I have realised that there is a serious bug because the database is changing in real time, and so the results were inconsident, so I need all the queries to run against the same “snapshot” of the database otherwise, if it updates during the run, the results can be inconsistent.
So to solve this, I created some temporary tables at the start of the script, which are basically just
SELECT * FROM table, and the queries then run against those instead of the live tables themselves, and I have verified that the results are correct/consistent.
Howeverr, the problem is that with temporary tables, the whole program takes 40-45 hours to run, as opposed to 2-3 hours without temporary tables (which I am hoping to reduce). When I look at the performance of the instance that I run the queries on (using MySQL Workbench) the CPU and memory usage are very low; yet when I don’t use temporary tables, the CPU usage on my local instance flatlines at 100%.
So my theory is that, since temporary tables are stored in RAM on the server, the server does not have sufficient RAM, and the bottleneck is there. Unfortunately I don’t have any access to the server so I can’t verify this. So what I am hoping for from this question is to get some feedback as to whether this problem is likely caused by insufficient resources on the server, or perhaps something else.
Edit: I have also noticed in MySQL Workbench, that the individual queries are taking 15 times longer to run on the server against the temporary tables, than when it was running against the raw tables