Re: Insert Into ... Select, Delte Data

[Hans Henrich]

Hi Rick,

thank you for your great support!

First of all, yes I´m using MyIsam, I tought about changing to InnoDb but this will cost some productivity time of the application. (Copying DB and changing engine).

The table has only 2 columns and one index, thats not a problem.

I didn´t know, that big Insert statements are better than much "atomic" statements. I tested to batch statements in queue´s in the application and execute the big Insert statements but without much success.

Losing data is a big theme. The applications creating task. The tasks where saved in the table and any other application (or it´s self) take undone tasks from the table and generating new tasks and so on. The applications do not only generating tasks but tasks are importand by-products. Generally it happens that task x generates task task h,i,J and task i for example generates task o,J,p. But it could happen that an task only be found once. At the moment the applications store every new task immediately in the table and only if all querys has been successfully executed the task will be set to done. If an error occurs the task will be processed a secound time. With the temp tables that behavoir could be continued. If the application crashs the tables will not be deleted and when the application starts it searchs for old temp tables and process them first. If the sql server chrashes thats not a big deal because the tasks are undone. If the application batchs in queues I store the data in the file system.

Maybe I should live with much "table lock waited" until the Engine could be changed to InnoDB