Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

$dbh->prepare() and execute() take too long?

by jbrugger (Parson)
on Mar 07, 2006 at 06:41 UTC ( #534840=perlquestion: print w/ replies, xml ) Need Help??
jbrugger has asked for the wisdom of the Perl Monks concerning the following question:

Oh wise Perl magicians

I have a query that looks something like this :
select ra.value, ra.ra_id, q.id as q_id, q.qid , q.parent, q.q, q.type +, p.qid as pqid, if($ifstatement,'1','0') as selection , r.rpid from +r, ra, q left join q p on q.parent = p.id where r.id = ra.ra_id and r +.q in (3,4,5) and ra.q = q.id order by ra.ra_id ASC,selection desc;
The 'explain' shows a good and nice run (no full table scans), and in mySQL it takes about 3 seconds to run (about 130.000 records) using  mysql -u user -p database < q.sql > output.txt
When doing this from whithin Perl however,
my $bm= DEBUG_Benchmark->new(); $bm->bmStart(); $sth = $dbh->prepare("select ra.value, ra.ra_id, q.id as q_id, q.qid , + q.parent, q.q, q.type, p.qid as pqid, if($ifstatement,'1','0') as se +lection , r.rpid from r, ra, q left join q p on q.parent = p.id where + r.id = ra.ra_id and r.q in (3,4,5) and ra.q = q.id order by ra.ra_id + ASC,selection desc"); $sth->execute(); $bm->bmStop(); print $bm->getBmResult();
It takes about 12 seconds to run (Only the prepare and the execute, i did not even loop over the resultset).
Any idea on how to speed this up? Whit a smaller resultset, it takes about .003 seconds to prepare / execute.
I know DBI should be able to do mutch better, any ideas on how i can get a better result?
Thanks.

ps.
I run this on an UML (user mode linux), so there is a lot of overhead in the disk io, however, the same problem occurs on a 'normal' debian distribution, the difference is big. (2 sec - 8 sec)

ps.ps.
The resultset of the query looks like this:
value ra_id q_id qid parent q(4,5,6)type pqid sel +ection rpid 1 5501 1918917 34 0 4 6 NULL 1 ed94d62745f5 +2f83cc6225c46cd1f012 1 5501 1918918 33 0 4 6 NULL 1 ed94d62745f5 +2f83cc6225c46cd1f012 4 5501 1918915 32 0 4 6 NULL 1 ed94d62745f5 +2f83cc6225c46cd1f012 2 5501 1918940 31 0 4 6 NULL 1 ed94d62745f5 +2f83cc6225c46cd1f012 6 5501 1918916 30 0 4 6 NULL 1 ed94d62745f5 +2f83cc6225c46cd1f012 ...
*** UPDATE ***
Ah FOUND IT!!! Here is the answer!!!
"We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.

Comment on $dbh->prepare() and execute() take too long?
Select or Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (9)
As of 2015-07-05 21:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (68 votes), past polls