When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using the chunk method.
To make things worse: if you let such an operation run for a few hours and KILL the job (or crash) then it can take days or weeks until ROLLBACK is finished.
Rollback is significantly slower than insert, no optimizations here.
The same counts for doing mass UPDATEs.
My question:
How can I completely disable transaction log for such a transactions ?
I tested this: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But it showed no difference, when killing the job transactions start rolling back.
I really did my homework and tried to find anything about it, no luck.
P.S.
Of course I am aware that importing without transactions does not give a unique snapshot to rely on if anything is changed in meantime.
Best Answer
You cannot turn off transactions.
It is assumed that
ROLLBACKis the exception, not the rule. Are you trying to make it the "normal" path?5 days vs 2 hours -- this is far more extreme than I would expect. Perhaps you are running out of RAM? Or swapping?
Please provide more details on the task; we may be able to help you 'solve' the problem in a way other than "turning off transactions".