use DBI; 
use edw; 
use Benchmark qw(:all) ;
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=0;
my $max_commit=30000;
my $dbh1 = DBI->connect( $edw_connect, $edw_db_user, $edw_pwd , { Prin
+tError => 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,{Au
+toCommit => 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);

[download]

20101011 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

In reply to DBI Performance help by cool79s

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.