Batch Insert by Perl

gistfile1.pl

#!/usr/bin/perl

use DBI;
use Time::HiRes qw(time);
use List::Util qw(shuffle);

my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost;rewriteBatchedStatements=true", "root", "", {'RaiseError' => 1});

#insert_seq(5000000, 'nulltest');
#insert_seq(300000, 'seqtest');
#insert_rand(300000);

$dbh->disconnect();

sub insert_seq {
  println("### SEQ INSERT");
  $dbh->prepare("truncate table ".$_[1])->execute();
  $dbh->{AutoCommit} = 0;
  my $sql = "INSERT INTO ".$_[1]." VALUES (?,?,?)";
  my $stmt = $dbh->prepare($sql);

  my $before = time;
  for ($i = 0; $i < $_[0]; $i++) {
    my $rnum = int(rand(1000000));
    if ($i % 10000 == 0) {
      $dbh->commit;
      println($i." ".(time - $before)."s");
    }
    $stmt->execute($i,$rnum,"Hello ".$rnum);
  }
  $dbh->commit;
  $stmt->finish;
  println("Seq Spent Time:".(time - $before)."s");
}

sub insert_rand {
  println("### RAND INSERT");
  $dbh->prepare("truncate table randtest")->execute();
  $dbh->{AutoCommit} = 0;
  my $sql = "INSERT INTO randtest VALUES (?,?,?)";
  my $stmt = $dbh->prepare($sql);

  my @id = (0..$_[0]-1);
  @id = shuffle @id;

  my $before = time;
  my $i = 0;
  foreach (@id) {
    $i++;
    my $rnum = int(rand(1000000));
    if ($i % 10000 == 0) {
      $dbh->commit;
      println($i." ".(time - $before)."s");
    }
    $stmt->execute($_,$rnum,"Hello ".$rnum);
  }
  $dbh->commit;
  $stmt->finish;
  println("Rand Spent Time:".(time - $before)."s");
}

sub selects {
  my $sql = 'SELECT * FROM cha LIMIT ?';
  my $stmt = $dbh->prepare($sql);
  $stmt->execute($_[0]);

  while(my $row = $stmt->fetchrow_hashref()){
    println($row->{id});
  }
  $stmt->finish;
}

sub rnum {
  return int(rand(10000000));
}

sub println {
  print $_[0]."\n";
}