In a similar situation I have done INSERT DELAYED + running things in batches of 100. Just remember to catch the last batch if your data isn't divisible by 100. The DELAYED part of insert will tell MySQL to do that insert when it has a moment. The upside is your program gets control back immediatly, the downside is you loose error reporting on your inserts.
Another option might be to create an intermidiate table that holds which tables the data should go in, so you import to this table in large batches. Then go through that table moving them to the correct locations. I'm not sure that will get you much in the way of gain since you will then have to do 2 inserts, but it would allow you to do both operations in large batches.
A final posibility that can be added to either of the above is to prepare a single SQL statment for each table at the beginning, then use then use those prepares to insert the data.
$prepares->{$table}->execute(@data);In reply to Re: DBI: speed up insertions in generating database
by eric256
in thread DBI: speed up insertions in generating database
by punch_card_don
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |