Perl插入sqlserver数据库方法比较

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, $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.930s

insertdata2: 157.624s

bulkinsert:  44.018s

3. 分析