The fastest option for your bulk loading is probably using the LOAD DATA INFILE statement from MySQL.That statement expect a file with the data and does a very fast insert into the database. You can use Perl to transform your data into a format expected by the LOAD DATA INFILE statement.

Dear friend,

When i use 'DBIx::BulkLoader::MySql' i got the error that shows @INC...., .pm file not find. I noted that it may due to http://code.activestate.com/ppm/DBIx-BulkLoader-Mysql/. if you don't mine explain the LOAD DATA INFILE concept in a small program(Now i insert 10,00,000rows/3min)I would like to reduce that time to seconds

Thanks

I doubt that even the LOAD DATA INFILE statement will insert 10 lakh records in a few seconds.

As far as DBIx::BulkLoader::Mysql is concerned, did you try installing it through cpan?

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

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

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]

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

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?

i need to modify this code to enhance it speed by using bulk insertion concept

See Re^3: How can i modify this code by using Bulk Loader?

When the use of a command-line or other tool is (for whatever reason) infeasible, bulk-insertion operations are categorically improved through the judicious use of three techniques:

1. Table locking, or the use of transactions with a very strict “isolation level,” to preclude any conflicting operations by other users... or even access by other users.
2. Transactions, in general, to permit the database system to do “lazy writes” while carrying out the operation.   The transaction is periodically committed and reopened so that the rollback-file does not become huge.
3. Disabling index updates and referential integrity checks until the entire operation is finished.

There is a very-considerable amount of material on this subject on the Internet, and you need not be concerned if the article you read is not specifically talking about Perl.   The core concepts are universal.

Transactions make insert operations in most database, including MySQL slower. There's no such thing as a "lazy write" - in order to remain consistent, data needs to be written to disk as soon as possible.

Now, I'd still recommend putting everything in a transaction, but not for performance reasons.

You already have a bulk loader. What you are missing are transactions. Please see my previous post here: Re: How can i modify this code by using Bulk Loader?

Hi friend,

Thanks for your comment, i need to say one thing that i don't have any experience in Perl i just have 10days experience. I understand the logic what you have post on my previous thread, but i don't understand where i introduce your code instead of my previous code

Thanks