At 12:28 p.m. 6/09/2013, firebird_jimmy wrote:

>At the very start, I insert into real table, but it gradual become slowly. I think maybe records too much, so insert into temporary table.
>I think this is a wrong.

It is the wrong solution to your slowdown problem. Mass inserts can get slower and slower if you are trying to commit too many inserts in a single transaction. Try performing a hard COMMIT ** after each ~8000 inserts and see whether this helps to keep things fast and even. (You can experiment with slightly larger or smaller batch sizes to establish what works best for your case.)

In fact, your "double insert" is likely to be doubling the performance hit that the operation is already suffering due to overloading *two* Undo logs, which the engine is going to abandon anyway, eventually, when the logs simply grow too big!

Also make sure your transaction for the mass inserts is in Snapshot isolation, not ReadCommitted. ReadCommitted has heavier overhead but has no purpose for an operation that only performs inserts.

** A "hard" COMMIT means calling COMMIT expressly, not using CommitWithRetain, which is often a default setting in application interfaces.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________