Need help in performance

[cool79]

I have written the below code to to straight inserts from another database.Currently it takes 30 seconds to insert 10000 rows for 20 columns.Can anyone suggest me to improve the performance of the code?

use DBI;
use edw;
use Benchmark qwall) ;
my $maximo_connect=$maximoconf{'maximo_connect'};
my $maximo_db_user=$maximoconf{'maximo_db_user'};
my $maximo_pwd=$maximoconf{'maximo_pwd'};

my $edw_connect=$edwconf{'edw_connect'};
my $edw_db_user=$edwconf{'edw_db_user'};
my $edw_pwd=$edwconf{'edw_pwd'};
my $table=$ARGV[0];
my $edw_table="STG_EAM_$ARGV[0]_TB";
my $inserted;
my $max_commit=30000;
my $dbh1 = DBI->connect( $edw_connect, $edw_db_user, $edw_pwd , {
PrintError => 0,
RaiseError => 1,AutoCommit=>0 });
my $dbh = DBI->connect( $maximo_connect, $maximo_db_user, $maximo_pwd , {
PrintError => 0,
RaiseError => 1,AutoCommit=>0 });

#$sth = DBI->connect($maximo_connect, $maximo_db_user, $maximo_pwd,{AutoCommit => 0})
# ->column_info(undef, undef, $table, "%");
$sth=$dbh->column_info(undef, undef, $table, "%");

$sth->execute();
push @fields, $_->{COLUMN_NAME} while $_ = $sth->fetchrow_hashref;
my $field_placeholders = join ", ", map {'?'} @fields;
my $fieldlist = join ", ", @fields;

my $sth1=$dbh->prepare("SELECT $fieldlist from maximo.$table " );
$sth1->execute();
$t0 = Benchmark->new;
my $sql_ins="INSERT INTO $edw_table ( $fieldlist )
VALUES ( $field_placeholders )";
my $sth2=$dbh1->prepare("$sql_ins");

while(@columns=$sth1->fetchrow_array)
{
$inserted+=$sth2->execute(@columns);
unless ($inserted % $max_commit) {
$dbh1->do('commit');
$inserted=0;
}

}
$dbh1->do('commit');
$t1 = Benchmark->new;
$td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";
$dbh->disconnect();
$dbh1->disconnect();
exit(0);

 

[PinkeyNBrain]

Don't know if this is a viable option, but if the two databases are running under the same server (SQL server?), what are the chances of writing a routine/macro at the server level that would do the copying? That way you wouldn't have to route all of your data through an intermediate program (perl in this case) You can use perl to call the macro with any needed parameters and/or call different macros as the case warranted. If you are into self abuse, use perl to write the macro and save it under the database, then execute it.

 

[travs69]

One big insert is way more efficient than a lot of small ones.

Try doing a query, building your insert query data off of that, and then doing the insert.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[cool79]

Travis,
Are you saying me to concat all the insert statements and insert at once?If that is the case,can you please explain me the proces?

 

[travs69]

In a table with 3 cols call col1, col2, col3
This
insert into table (col1,col2,col3) values (a,b,c);
insert into table (col1,col2,col3) values (a1,b1,c1);
insert into table (col1,col2,col3) values (a2,b2,c2);

is a lot slower than this
insert into table (col1,col2,col3) values (a,b,c),(a1,b1,c1),(a2,b2,c2);

So you need to change your insert query to the new table to have all the old information in it.
Something like this (just roughly thoughts.. no real code)


my $query = 'insert into table (col1,col2,col3) values ';
while (query->old_db) {
my ($val1,$val2,$val3) = select vals from db;
$query .= "(val1,val2,val3),";
}
execute $query.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;