Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

How can i modify this code by using Bulk Loader?

by sowraaj (Novice)
on Sep 01, 2011 at 06:20 UTC ( #923569=perlquestion: print w/ replies, xml ) Need Help??
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} ); }; }

Comment on How can i modify this code by using Bulk Loader?
Download Code
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
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; }
Re: How can i modify this code by using Bulk Loader?
by Marshall (Prior) 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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://923569]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (17)
As of 2014-08-27 16:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (245 votes), past polls