|No such thing as a small change|
$dbh->prepare() and execute() take too long?by jbrugger (Parson)
|on Mar 07, 2006 at 06:41 UTC||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 :
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,
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?
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)
The resultset of the query looks like this:
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).
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.