in reply to Bulk data insertion into MySql

Your code looks like straight DBI. The easiest way to make this a LOT faster is to make the whole operation one single transaction.

$dbh->do("SET autocommit = 0");
....
$dbh->do("COMMIT");

[download]
I took the above "SET autocommit = 0" from some MySQL code that worked in my app. If you commit every 1/4-1/2 million records or so, the performance will be fine. There can be other ways to start the transaction, perhaps BEGIN or other keywords.

I do find this map pretty ugly: 

map
{
   my($keys,$value)=split /\s*:\s*/,$_;
   $keys=~ s/\s//g;
   $info{$keys} = $value;
}split /\n/, $record;

#Don't use map when you mean foreach (or for)
foreach (split /\n/, $record)
{
  ....
}

[download]

Replies are listed 'Best First'.
Re^2: Bulk data insertion into MySql
by sowraaj (Novice) on Sep 03, 2011 at 12:31 UTC

    Dear friend,

    I tried the method Auto_commit& Commit in my coding but i noted that there is not large difference compare to my previous code. If you know any method which is helped to enhance the insertion time by bulk loading . (Now i insert 10,00,000rows/2min)I would like to reduce that time to seconds

    Thanks

[reply]

      If you can already insert one million rows in two minutes, that seems like a very good speed to me.

      Your task looks like a one-time script to import ("bulk load") a number of files into the database. Why do you need it faster? How often do you need to run it?

[reply]

In Section Seekers of Perl Wisdom