Home: Perl Programming Help: DBI:
How to speed up module


Aug 30, 2012, 5:07 AM

Views: 22721
How to speed up module

Hi Experts,

I am implementing Perl module which uses DBI and XML::XParent. It works really slow and I would like to increase the speed of my code. I have changed everything following the other posts, tutorials etc but it was not a solution - I mean, I prepare statements, I use arrayref instead of array etc.

My question is associated with logic. My code parses the XML and during this adds a lot of records into the database (but only one with one query).

I want to ask, is it bad idea to create a SQL script during the parsing and invoke it once after that? Should it increase the speed of my module?

Thank you in advance

Veteran / Moderator

Aug 30, 2012, 6:28 AM

Views: 22714
Re: [appetitto] How to speed up module

You should post your code so that we can see if we can spot some obvious points that might be causing the slow running.

Beyond that, you need to profile your script to see where and why it's running slow, then work on optimizing those sections.

Devel::NYTProf - Powerful fast feature-rich perl source code profiler

Chris Charley

Aug 30, 2012, 8:09 AM

Views: 22710
Re: [appetitto] How to speed up module

Have you set AutoCommit to '0' when you create your database handle? If not, you will be committing for every insert and this will slow it down quite a bit.

You can do your inserts and commit every so many records or insert them all, then commit.

my $dbh = DBI->connect("dbi:SQLite:dbname=pedro.lite","","", 
{PrintError => 1, AutoCommit => 0}) or die "Can't connect";

my $sth = $dbh->prepare(q{INSERT INTO purchases VALUES(?,?,?,?)})
or die $dbh->errstr;

while (<DATA>) {
$sth->execute( split /\|/ );

$dbh->commit() or die $dbh->errstr;