I've got this stored Procedure that is called by an event that runs every 1 minute.
The stored procedure selects some data from one device and puts it in a temporary table. Than it picks up other comparable devices and puts that data into another temporary table.
Unfortunately I have to use cursors as I have to go through that data back and forth at times.
I Solved this issue by creating this solution which works well. But I have a problem with transactions which is due to truncating the temporary tables. As we all know mysql forces a commit WHEN using truncate on a table (even on temporary tables). I've been looking all morning for a solution for this as I just had a deadlock and some data wasn't rolled back.
So and after making some tests I can see the solution would be to do create and drop tables instead of doing truncates. So I made a stored procedure that compared creating inserting dropping a temporary table against truncating inserting one hundred times each. The result was 9seconds against 125ms.
This is obviously not acceptable in a stored procedure already taking between 1 seconds and 10 seconds at times.
Please note the main table where the data is fetched in the first place is very big and grows very fast (several millions rows). Comparing data on the main table is not an option.
Did anyone came across something like this? If so how did you solve it? Thank you.