Perl ABC

th->execute($i, $title);

} }

});

cmpthese ($result);

Benchmark results are slightly lower than those using raw DBI. Compared to using a normal insert, there is a difference of nearly 10 times, so I think it is better to use it when performing a large number of inserts at the same time.

           Rate normal multiple raw
normal 22.3/s - - 90% -92%
multiple 213/s 855% - -23%
raw 278/s 1147% 31% -

Speed up with bulk insert

If your database supports bulk inserts, you can do bulk inserts. Currently available for MySQL and PostgreSQL. You can use the database function to insert multiple data at once, so you can expect even higher speeds.

$dbi->insert($params, table =>'book', bulk_insert => 1);

Benchmark with bulk_insert in MySQL

Below is the benchmark script.

use strict;
use warnings;
use Benchmark qw/timethese cmpthese/;

use DBIx::Custom;

my $dbi = DBIx::Custom->connect(dsn =>'dbi: mysql: database = usertest',
user =>'root', connector => 1);

# Performance comparison
my $result = timethese (100, {
  normal => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table =>'book');
      for my $i (1 .. 100) {
        my $param = {id => $i, title =>'Perl'};
        $dbi->insert($param, table =>'book');
      }
    });
  },
  multiple => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table =>'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title =>'Perl'};
      }
      $dbi->insert(\@params, table =>'book');
    });
  },
  bulk_insert => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table =>'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title =>'Perl'};
      }
      $dbi->insert(\@params, table =>'book', bulk_insert => 1);
    });
  },
  raw => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table =>'book');
      my $sth;
      for my $i (1 .. 100) {
        $sth ||= $dbi->dbh->prepare('insert into book (id, title) values (?,?)');
        my $id = $i;
        my $title = 'Perl';
        $sth->execute($i, $title);
      }
    });
  }
});

cmpthese ($result);

Benchmark results show that using bulk_insert gives about 5 times the normal performance of a normal insert.

              Rate normal multiple raw bulk_insert
normal 24.0/s - - 72% -76% -83%
multiple 86.2/s 259% - -16% -40%
raw 102/s 326% 18% - - 29%
bulk_insert 143/s 496% 66% 40% -

If bulk inserts are not performing well, check once to see if there is enough memory space for database inserts. If this memory area is insufficient, swap-in/swap-out will occur, which will be very slow.