Paul,

> > Could you define more closely what "slow" means. How many records per
> > second?
>
> Between 15 and 100 records per second depending on record size and how
> many concurrent tables are being processed. Sweep is set to 0, and all
> update/insert triggers are marked inactive.

Don't forget to disable all possible indexes.

Or better yet, only add indexes and PK/FK constraints, as many as possible, the very end. (PK/FK indexes can't be disabled)

Since you are moving from an existing database, all PK/FK are not required, since the source data already has appropriate integrity.

That should increase the speed to by a factor of 50 (if not more)!!!

> Each table is done in its own connection and the transaction is committed at the end of the processing.

I would suggest that you commit after 1,000 to 10,000 rows.

> Some tables have 1,000,000+ records and 12 hours seems far too long to wait
> for a 1GB database to be processed.

If the database is small (1GB) then consider using a RAM disk and eliminate all disk IO. That should improve performance by at least a factor of 5!

> About 100 tables are insert only and do not get checked. (I can already
> guarantee these records do not exist in the new database. About 120 tables
> check for a record to exist first. 2/3 of the records in the database are in the
> first 100 tables, so they are not being selected from the new database.

Are you using prepared statements with parameters?


Sean