How can i modify this code by using Bulk Loader?

sowraaj has asked for the wisdom of the Perl Monks concerning the following question:

i need to make faster execution of this code by using bulk loader

i can't able to understand the bulkloader package source code properly.

I need to enhance this code by using bulk loader; i thought it will rapidly reduce the time of process.

I request you to modify this code by using bulk loader concept

At present i use ubuntu and mysql platform

======== SCHOOL (B): MALE =========
SCHOOL_NAME :ABCDEFGH
NAME_OF_STUDENT :RAJ
ID_NUMBER :9632587410
ADDRESS :23,7TH STREET
PINCODE :03214
MOBILE_NUMBER :4563210789
LAND_LINE :0123859674
FATHER'S_NAME :SHIVA
MOTHER'S_NAME :JAYA
ANNUAL_INCOME :520000
CHARACTER :GOOD


======== SCHOOL (G): GIRLS =========
SCHOOL_NAME :QWERTY
NAME_OF_STUDENT :FATEMAH
ID_NUMBER :10234567890
ADDRESS :635,9TH STREET
PINCODE :63520
MOBILE_NUMBER :9632105478
FATHER'S_NAME :NHOORE
MOTHER'S_NAME :JAVATHI
ANNUAL_INCOME :420000
CHARACTER :GOOD

#PLS NOTE THAT THE LAND_LINE NUMBER OF GIRLS SCHOOL IS MISSING


#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $table  = 'SOWND1';
my $dbh =
  DBI->connect( 'dbi:Mysql', 'new', undef, { PrintError => 0,
+ RaiseError => 1 } );
my $sth = $dbh->prepare("DROP TABLE IF EXISTS '$table'");
$sth->execute;
$sth = $dbh->prepare(
    "CREATE TABLE $table(
        TYPE varchar(300),
        SCHOOL_NAME varchar(300),
        NAME_OF_STUDENT varchar(300),
        ID_NUMBER varchar(300),
        ADDRESS varchar(300),
        PINCODE varchar(300),
        MOBILE_NUMBER varchar(300),
        LAND_LINE varchar(300),
        FATHERS_NAME varchar(300),
        MOTHERS_NAME varchar(300),
        ANNUAL_INCOME varchar(300),
        CHARACTER varchar(300)
    )"
);

$sth->execute;

$sth = $dbh->prepare("INSERT INTO $table VALUES(?,?,?,?,?,?,?,?,?,?,?,
+?)");

my @files = <C:/strawberry/perl/bin/SCHOOL/STUDENTS/*>;
die "No files found\n" unless scalar @files;

for my $infile (@files) {
    local $/ = '';
    open my $fh, '<', $infile or die "$!\n";
    while (my $record = <$fh>) {
        chomp $record;
        my %info;
        $record =~ s/^=+.+ MALE =+/TYPE :BOYS/;
        $record =~ s/^=+.+ GIRLS =+/TYPE :GIRLS/;
        map {
            my ( $key, $value ) = split / :/, $_;
            $key =~ s/\'//g;
            $info{$key} = $value;
        } split /\n/, $record;
        $info{LAND_LINE} ||= 'NE';

        $sth->execute(
            $info{TYPE},            $info{SCHOOL_NAME},
            $info{NAME_OF_STUDENT}, $info{ID_NUMBER},
            $info{ADDRESS},         $info{PINCODE},
            $info{MOBILE_NUMBER},   $info{LAND_LINE},
            $info{FATHERS_NAME},    $info{MOTHERS_NAME},
            $info{ANNUAL_INCOME},   $info{CHARACTER}
        );
    };
}
[download]

Comment on How can i modify this code by using Bulk Loader? Download Code

Replies are listed 'Best First'.
Re: How can i modify this code by using Bulk Loader? by Anonymous Monk on Sep 01, 2011 at 07:04 UTC
...bulk loader ... What bulk loader? I request you to modify this code by using bulk loader concept Sure, here you go `/path/to/or/name/of/bulk/loader/program data.sql`	[reply]
Re^2: How can i modify this code by using Bulk Loader? by cdarke (Prior) on Sep 01, 2011 at 07:19 UTC
bulk-loader is an Actionscript library: http://code.google.com/p/bulk-loader, and a mysql .Net class: http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-bulk-loader.html, so I assume it is the latter the OP is concerned with.	[reply]
Re^3: How can i modify this code by using Bulk Loader? by Anonymous Monk on Sep 01, 2011 at 07:44 UTC
It is also a generic term for programs like mysqldump/mysqlimport, which can be used for this purpose :) sowraaj needs to explain what he is talking about, even if we guess right sometimes site:perlmonks.org LOAD DATA INFILE trying to execute "LOAD DATA INFILE" using DBI::Mysql	[reply]
Re^4: How can i modify this code by using Bulk Loader? by sowraaj (Novice) on Sep 02, 2011 at 13:51 UTC
Re^5: How can i modify this code by using Bulk Loader? by Corion (Patriarch) on Sep 02, 2011 at 14:31 UTC
Some notes below your chosen depth have not been shown here
Re: How can i modify this code by using Bulk Loader? by Anonymous Monk on Sep 01, 2011 at 10:31 UTC
Why not just use transactions? If you wrap the inserts in a single transaction, they should not take ~10 milliseconds per insert anymore, but likely be ten times as fast. `# assumes RaiseError eval { $dbh->begin_work; ... inserts here $dbh->commit; } or do { $dbh->rollback; }` [download]	[reply] [d/l]
Re: How can i modify this code by using Bulk Loader? by Marshall (Canon) on Sep 03, 2011 at 04:19 UTC
I think that I have already replied to another of your threads about this. If you say "i need to make faster execution of this code by using bulk loader", you are making 2 claims: 1) The need to make the creation of the entries in the data tables faster. So why isn't it fast enough now? What performance do you want to achieve? 2) You further suppose that the bulk-loader will solve your problem. That may or may not be true. look at my post to your more recent thread and go for it.	[reply]


Keep It Simple, Stupid
	PerlMonks