Perl插入sqlserver数据库方法比较
分类： Python/Ruby
1. 本文对三种插入数据库方法比较。
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,$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 or die "cannot open file";
    my @a = <IN>;
    close IN;
    
    my $sth=$dbh->prepare("INSERT INTO $table VALUES (?,$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 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.930s
insertdata2: 157.624s
bulkinsert:  44.018s