If the data have a different source, like a text file, consider using the bulk import feature that your database engine most likely provides.

Also it's a good idea to set RaiseError to 1 or to do some error checking yourself, otherwise you might miss important error messages (that can also tell you about possible performance problems).

Please use <code></code> tags around your code.

Using fetchrow_arrayref instead of fetchrow_array might be slightly faster, or using bind_columns, but probably not by much. E.g.:

my $sth = $dbh->prepare("SELECT ...");
my @columns = @{$sth->{NAME}};
my @row; $sth->bind_columns(\@row{0..$#columns});

# Then
while ($sth->fetch()) {
  $ins_h->execute(@columns);
}
[download]

Not slightly, it matters a lot. Bench if you want to know.

See here for a postgresql example


Enjoy, Have FUN! H.Merijn

I see that you are using transactions.   Good.   Bulk inserts are often speeded up by disabling indexes on the table for the duration of the update.   When the indexes are re-enabled, they are calculated once for the whole data set.

I cannot easily see (because of the lack of <code> tags) whether you are preparing the query each time (you don’t need to), and whether you are using placeholders and bound parameters (you should be).

I have updated the format.Can you please look into it?

From my perspective, you have worsened the format and removed your question and information about what you are doing.

See Markup in the Monastery and pay careful attention to how the code tags are used.

Although your program seems quite simple, it is not obvious where the performance bottleneck is and there are many possibilities within your program, the modules you use and the databases you are accessing. A good place to start might be to profile the execution of your program and then focus your attention on whatever takes the most time.

I have found Devel::NYTProf to be easy to use and very informative.

I would first try to determine whether most of the time is spent executing your program or waiting for the databases to respond. As your program is quite simple, I suspect the latter.

You may find that only the selects or only the inserts are slow. Without knowing anything about your databases or systems, it is hard to say anything other than: there are many possibilities. The underlying network or servers may be overloaded. You may have triggers on your database that are consuming time and resources. You may be querying a poorly optimized view.

Rather than trying to identify all the possibilities in advance, simply start to track down what is taking the time and follow the leads.

Eventually, you will identify something that is consuming a lot of time. You should be able to identify what is taking the most time quite quickly.

The next operation step is to try some alternative approach in hope that it will be faster. think about why that operation is slow and what changes you could make that might be make it faster. Then try some of your ideas.

You are already using Benchmark. Keep good records as you progress so you know what changes you made and what impact they had on performance.

Be prepared to back out changes. Not everything you try will be an improvement.

It is not obvious what your underlying databases are. They may have tools that will help you understand why the queries you are running take so long to execute. If they do, you could use them or get help from someone who is familiar with them.

If you find you are not making good progress, get help from others, providing the information you have gathered and the alternatives you have tried.

$dbh->{Profile} = 6;
[download]

while ( @columns = $sth1->fetchrow_array ) {
    $inserted += $sth2->execute(@columns);
    ...
    }
}
[download]

while ($row=shift(@$cache) || shift@{$cache=$sth_o->fetchall_arrayref(
+undef,$max_rows) || []}) {
    $inserted += $sth2->execute(@$row);
    ...
};
[download]