Perl Guru Forums: Perl Programming Help: DBI: How to speed up module

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

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

appetitto
Novice

Aug 30, 2012, 5:07 AM

Post #1 of 3 (4503 views)

How to speed up module	Can't Post

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
Alex

FishMonger
Veteran / Moderator

Aug 30, 2012, 6:28 AM

Post #2 of 3 (4496 views)

Re: [appetitto] How to speed up module [In reply to]	Can't Post

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
http://search.cpan.org/~timb/Devel-NYTProf-4.08/lib/Devel/NYTProf.pm

Chris Charley
User

Aug 30, 2012, 8:09 AM

Post #3 of 3 (4492 views)

Re: [appetitto] How to speed up module [In reply to]	Can't Post

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.

Code

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>) { 
	chomp; 
	$sth->execute( split /\|/ ); 
} 
 
$dbh->commit() or die $dbh->errstr;

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives