Perl插入sqlserver資料庫方法比較

 Perl插入sqlserver資料庫方法比較分類： Python/Ruby1. 本文對三種插入資料庫方法比較。a) 迴圈執行$sth->execute();並且自行控制$dbh->commit();b) $sth->bind_param_array()與$sth->execute_array()c) 利用sqlserver提供的bulkinsert與直接insert比較 2. 代碼如下:點擊(此處)摺疊或開啟use strict;use warnings;use DBI;use Time::HiRes qw(gettimeofday);main();sub main{        my $data_source = "DBI:ADO:driver={SQL Server};Server=localhost; database=NepMDB;";    my $dbh=DBI->connect($data_source, 'NepUser', '1q2w3e4r%T');    $dbh->{AutoCommit} = 0;    insertdata1("record","recordData.txt", $dbh);        $dbh->disconnect();}sub insertdata1{    local $| = 1;    my ($table ,$file, $dbh) = @_;     open IN, $file or die "cannot open file";     my @a = <IN>;    close IN;        my $sth=$dbh->prepare("INSERT INTO $table VALUES (?, ?, ?, ?, ?, ?, ?, ?)") or die $dbh->errstr;    my ($start_sec, $start_microsec) = gettimeofday();    my $i;     foreach (@a)    {        my ($sid, $wid, $eta, $isc, $voc, $ff, $rs, $rsh) = split /,/;        $sth->execute($sid, $wid, $eta, $isc, $voc, $ff, $rs, $rsh);        $i++;        $dbh->commit() if $i % 500==0;    }    $dbh->commit();    $sth->finish();        my ($end_sec, $end_microsec) = gettimeofday() ;    my $timespan= ($end_microsec - $start_microsec) / 1000 + ($end_sec - $start_sec) * 1000;    print "\t$timespan\t";        $timespan;}sub insertdata2{    local $| = 1;    my ($table ,$file, $dbh) = @_;     open IN, $file or die "cannot open file";    my @a = <IN>;    close IN;        my $sth=$dbh->prepare("INSERT INTO $table VALUES (?, ?, ?, ?, ?, ?, ?, ?)") or die $dbh->errstr;    my ($start_sec, $start_microsec) = gettimeofday();    my $i = 0;    my (@sid, @wid, @eta, @isc, @voc, @ff, @rs, @rsh);        foreach (@a)    {        my ($_sid, $_wid, $_eta, $_isc, $_voc, $_ff, $_rs, $_rsh) = split /,/;        push @sid, $_sid;        push @wid, $_wid;        push @eta, $_eta;        push @isc, $_isc;        push @voc, $_voc;        push @ff, $_ff;        push @rs, $_rs;        push @rsh, $_rsh;    }    $sth->bind_param_array(1,\@sid);    $sth->bind_param_array(2,\@wid);    $sth->bind_param_array(3,\@eta);    $sth->bind_param_array(4,\@isc);    $sth->bind_param_array(5,\@voc);    $sth->bind_param_array(6,\@ff);    $sth->bind_param_array(7,\@rs);    $sth->bind_param_array(8,\@rsh);        $sth->execute_array({ ArrayTupleStatus => \my @tuple_status } );    $dbh->commit();    $sth->finish();        my ($end_sec, $end_microsec) = gettimeofday();    my $timespan= ($end_microsec - $start_microsec) / 1000 + ($end_sec - $start_sec) * 1000;    print "\t$timespan\t";    $timespan;}sub bulkinsert{    local $| = 1;    my ($table ,$file, $dbh) = @_;     open IN, $file or die "cannot open file";        my $sql = 'truncate table '.$table;    my $trunca=$dbh->prepare($sql);    $trunca->execute();    $trunca->finish();        my @a = <IN>;    close IN;        my ($start_sec, $start_microsec) = gettimeofday();        my $sth=$dbh->prepare("BULK INSERT $table FROM 'c:\\recordData.csv' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\\n')") or die $dbh->errstr;    $sth->execute();    $dbh->commit();    $sth->finish();        my ($end_sec, $end_microsec) = gettimeofday() ;    my $timespan = ($end_microsec - $start_microsec) / 1000 + ($end_sec - $start_sec) * 1000;    print "\t$timespan\t";        $timespan;}2. 結果:100,000條資料用時比較:insertdata1: 156.930sinsertdata2: 157.624sbulkinsert:  44.018s