in reply to Re: How to optimized mysql insert into table
in thread How to optimized mysql insert into table

Where in your program do you loop over your input file?

You are talking about 100,000 inserts but your program is only ever doing one insert.

Maybe you can show us a real program that does more than one insert.

My suggestion for doing more than one insert is to use a loop and to prepare the statement handles outside of the loop and to do the ->commit calls every 1000 inserts or something like that.

Also, consider using the native data loading functionality of MySQL instead of hand-feeding the data to your database.

Replies are listed 'Best First'.
Re^3: How to optimized mysql insert into table
by Anonymous Monk on Jan 17, 2017 at 18:00 UTC
    OK I put that part back in: 
    use strict;
use warnings;
use DBI;

my $start = time();
my $data;
my ($version,$jobnumber);
my $file = "C:/temp/dump.txt";

open (FH, "$file") or die "$!";
while(<FH>){
   $data .= $_;
}

$version = substr($data,0,3);
$jobnumber = substr($data,4,5);

my $databaseName        =   "basedb";
my $connectHost         =   "127.0.0.1";
my $DSN                 =   "DBI:mysql:dbname=$databaseName;host=$conn
+ectHost";
my $db_username         =   'root';
my $db_password         =   'root';
my $dbh                 =   DBI->connect($DSN,$db_username,$db_passwor
+d,{RaiseError=>1,PrintWarn=>0,AutoCommit=>0}) || die "error connectin
+g: $!";

my $dump_sql = "INSERT INTO temp (version,jobnumber) VALUES(?,?);";
my $dump_sth = $dbh->prepare($dump_sql) or die "$DBI::errstr";
eval{
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    $dump_sth->execute($version,$jobnumber) or die "$DBI::errstr";
    ...
}

    [download]
[reply]
[d/l]

      Have you heard that Perl has loops?

      Also, you never change $version and $jobnumber.

      Maybe you want to reconsider using one of the approaches I've already outlined.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom