On Mon, Jun 11, 2012 at 9:36 PM, Ken Slater <kenslate...@gmail.com> wrote:
> On Mon, Jun 11, 2012 at 2:06 PM, GlenM <glenmill...@gmail.com> wrote:
>> Hello Folks;
>>
>> I see an earlier post about sluggish code - I am not really sure what I am
>> doing, so I let me post my entire script here.
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> #!/usr/bin/perl
>>
>> #use strict;
>> use DBI;
>> use XML::XPath;
>> use XML::XPath::XMLParser;
>>
>> # Set the input dir where all of the XML files live
>> my $input_dir = ".";
>>
>> # Begin reading the directory
>> opendir(DIR, $input_dir) || die "sorry shit the bed $input_dir: $!";
>>
>> # Read them into an array
>> my @files_in_dir = grep { /xml/ } readdir(DIR);
>> closedir DIR;
>>
>> # connect to database and create parser object
>> my $dbh = DBI->connect ("DBI:mysql:can_us_ratecenters",
>> "xxxx", "xxxxxx",
>> { RaiseError => 1, PrintError => 0});
>>
>> # clear the database - new DIDs coming in
>> $dbh->do ('TRUNCATE TABLE rc_city_town');
>>
>> #Now the fun begins - read each file and put it in the database
>> foreach my $f (@files_in_dir) {
>> open IN, "<$f";
>>
>> my $xp = XML::XPath->new (filename => "./$f");
>> my $nodelist = $xp->find ("//row");
>> foreach my $row ($nodelist->get_nodelist ())
>> {
>> $dbh->do (
>> "INSERT IGNORE INTO rc_city_town (state_prov, city_town,
>> did_number) VALUES (?,?,?)",
>> undef,
>> $row->find ("state")->string_value (),
>> $row->find ("ratecenter")->string_value (),
>> $row->find ("number")->string_value (),
>> );
>> }
>> close IN;
>> }
>> $dbh->disconnect ();
>>
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> As far as I can tell, the script works without any compilation errors.
>>
>> I ran it with the Perl debugger ( perl -d scriptname.pl) and stepped through
>> it.
>>
>> It seems to bottleneck at the first foreach{} loop - probably not the best
>> use case.
>>
>> To get this script to stop, I have to kill the process - it has been running
>> for over an hour - it populates the database, but I want to do this right.
>>
>> However, I am sure there is someone smarter than I and can look at this and
>> say "..oh, there's your problem right there..."
>>
>> However, I am not a Perl guru. So, I am relying on some assistance from the
>> crowd.
>>
>> If you have any questions, please feel free. I will take any assistance I
>> can get.
>>
>> Thanks - Glen
>>
>>
>
> This is a guess (and not really a Perl issue), but one thing you might
> want to look at is turning autocommit off on in the connect statement.
Of course that should just say "off" not "off on" - need to proofread.
Ken
> This means you will have to explicitly commit the transactions before
> disconnecting (do it only once).
> HTH, Ken
>
> If it does, try turning it off
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
