Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
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!!!
Actually it IS the overhead from the UML! Time to step over to XEN.
After testing we found out that using apache ont the uml in a simplefied script still executed in 12 seconds, and on our life server in the same time as the commandline options (3 seconds).
ergo:
uml: Command line: *** Benchmark *** : Execute: 4.636518 seconds From Apache : *** Benchmark *** : Execute: 12.584603 seconds life: Command line: *** Benchmark *** : Execute: 3.079879 seconds From Apache : *** Benchmark *** : Execute: 3.066677 seconds
Sorry to have bothered you all, it seems that apache uses the disk even more, cousing even more io overhead.
"We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.

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 rifling through the Monastery: (4)
As of 2020-07-08 01:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?