Re: set autocommit off / commit / set autocommit on terribly slow

[John Crowley]

Aftab, thanks for your reply.

This was a modest batch size -- when we hit a max of 50 INSERT statements then it was committed (and most were much smaller). I would not expect that size to tax the UNDO mechanism. (Also, the START TRANSACTION .... COMMIT form should have the same buffering/undo issues.)

Re the 19 inserts/sec -- I probably misled you by simplyfying the discussion above.

The 14,000 records represents our raw input data, which is then split up to load several different tables in the DB. For the table in question, we actually loaded 626,166 database rows. Another table had 107,646 rows, and there were some smaller tables also. All this was parsed out of the 14,000 original input records.

The thing that bothers me the most is that the START TRANSACTION .... COMMIT approach worked like a charm. What is different about the setAutocommit(false) .... commit ... setAutoCommit(true) version that shows such a huge performance difference?

Also note that this sequence applied only to loading the largest table (the 626,166 rows). All the other tables just did a simple INSERT in both scenarios.

They are both loading the same data into the same tables with the same index definitions, log buffer sizes, etc. But the performance differs by orders of magnitude.