Profiling and Performance

Herkum has asked for the wisdom of the Perl Monks concerning the following question:

I was working on some code and decided to give Memoize a shot since I had run out of ideas of how to make my application faster and was using Devel::Prof to help identify problem areas. The results were great, was I able to get it to run about 6 times faster than before!I was very happy however the last part I am having problems with is having to do with database queries.

Devel::Prof tells me what methods are taking my time and how many calls there are but it does not tell me where those calls are coming from. In this case, it is telling me that the database queries are taking most of the time now but not which ones. See below...

%Time    Sec.    #calls    sec/call  F  name
13.88    9.5690   522901   0.000018     EEBL::get_field
12.40    8.5473      483   0.017696     DBI::st::execute
 6.49    4.4764   186739   0.000024     EEBL::_get_object
 4.15    2.8622  1760404   0.000002     Scalar::Util::refaddr
 3.88    2.6735     2851   0.000938     DBI::db::do
 2.16    1.4885     1726   0.000862     DBI::db::selectrow_array
[download]

The two things I would like to tackle are the number of calls to are being made to refaddr(which is being called from Class::Std ident function). Is there some what I can override the exported ident?

The second is tracing where the SQL calls that are being made for DBI::db::do and DBI::db::execute.

Help? Suggestions?

Comment on Profiling and Performance Download Code

Replies are listed 'Best First'.
Re: Profiling and Performance by perrin (Chancellor) on Mar 26, 2009 at 23:03 UTC
Devel::NYTProf UPDATE: Also, DBI::Profile for the SQL	[reply]
Re^2: Profiling and Performance by Herkum (Parson) on Mar 27, 2009 at 16:16 UTC
This appears to be significantly better than Devel::DProfLB which produced (for me) a 247 MB dump. I saw DBI::Profile but some reason using it did not reach my brain. I certainly slow down my thinking by mid-afternoon I tell ya...	[reply]
Re: Profiling and Performance by BrowserUk (Patriarch) on Mar 27, 2009 at 07:46 UTC
Have you tried replacing Class::Std with Class::Std::Fast or even Class::Std::Fast_XS? Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. "Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."	[reply]
Re^2: Profiling and Performance by Herkum (Parson) on Mar 27, 2009 at 16:11 UTC
I had not tried that. One thing I had been thinking about was why is the object reference not overridden to display the ID for the object instead of it just being a blessed object. What do you know, Class::Std::Fast does that! :) My biggest concern that is that it has problems with Activestate Perl on a Windows box. I hope it does not turn out to be an issue.	[reply]
Re^3: Profiling and Performance by BrowserUk (Patriarch) on Mar 27, 2009 at 18:32 UTC
Personally, I dismissed Class::Std a while ago as too slow to even see the cold as it flashes by. The most interesting OO infra-structure module that I've seen in recent times is Alter::Ego. Unfortunately, it's announcement timing meant that it has got lost in the Moose discussion. I didn't do much more than play with it at the time, but it did seem interesting and quite fast. My biggest concern that is that it has problems with Activestate Perl on a Windows box. I just looked again at Class::Std::Fast and I don't see any mention of problems with AS. Could you elaborate a little, or provide a pointer? Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. "Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."	[reply]
Re^4: Profiling and Performance by Herkum (Parson) on Mar 27, 2009 at 19:27 UTC
Re: Profiling and Performance by andreas1234567 (Vicar) on Mar 27, 2009 at 06:48 UTC
Which database are you using? Most databases has the ability to monitor performance, and single out queries and statements where most of the time is spent. MySQL, for instance, has The Slow Query Log and the EXPLAIN command. -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply]
Re^2: Profiling and Performance by Herkum (Parson) on Mar 27, 2009 at 16:09 UTC
I don't think that any individual query is slow so much as the number of queries. After sleeping on it last night, I figured out I was always using the DBI handle instead of storing a statement handle for a prepared statement. That being said I think I will try to cache the statement handle's and see if that offers any improvement.	[reply]
Re^3: Profiling and Performance by andreas1234567 (Vicar) on Mar 27, 2009 at 20:35 UTC
I don't think that any individual query is slow so much as the number of queries. Then shaving off a little time on each one can add up to a lot. Are you using placeholders? Have you considered stored procedures, possibly combining multiple operations into single? Are your data properly indexed? Are your tables unnecessarily large (can old data be deleted)? -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply]