Perl插入sqlserver数据库方法比较
发布时间:2020-12-16 00:09:44 所属栏目:大数据 来源:网络整理
导读:Perl插入sqlserver数据库方法比较分类: Python/Ruby1. 本文对三种插入数据库方法比较。a) 循环执行$sth-execute();并且自行控制$dbh-commit();b) $sth-bind_param_array()与$sth-execute_array()c) 利用sqlserver提供的bulkinsert与直接insert比较 2. 代码
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$timespant"; $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$timespant"; $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$timespant"; $timespan; } 2. 结果: 100,000条数据用时比较: insertdata1: 156.930s insertdata2: 157.624s bulkinsert: 44.018s (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |