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.
Of course I am aware that importing without transactions does not give a unique snapshot to rely on if anything is changed in meantime.