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.

  • Use a different temp table for each batch? – Alain Collins Jun 2 '14 at 16:06
  • Alain Collins, thank you for your input. I've thought of that too. Problem is it creates and drops hundreds of tables. It just doesn't seem very practical. I hope in the end it won't come to that but we'll see. Anyways mysql should support this in a transaction seeing that internally it already does that when we do selects that create temporary tables. – helderjsm Jun 3 '14 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.