RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I see.. but I agree with you From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 14 October 2015 11:20 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time? 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am 14.10.2015 10:09: > I think a tool like FBTraceManager from Thomas (Upscene) can do that for you Unfortunately not. The Trace API does not provide that level of granularity for PSQL code modules. This has been a long discussion in firebird-devel in the past, people basically argued that the Trace API isn't the right place for that sort of granularity, but more appropriate for a debugging/profiling feature. I still believe in the Trace API being a good profiling tool though. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: 13 October 2015 05:31 PM > To: firebird-support@yahoogroups.com > Subject: [firebird-support] How can I see which query within a stored > procedure execution takes the longest time? > > > > > > Greetings All, > > > > Firebird 2.5.4 > > I would like to know if there is any way that I can retrieve the execution > time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? > > Any tool to accomplish this? > > Thanks, > > Mike > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > Posted by: "Louis van Alphen" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu there. > > Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > > [Non-text portions of this message have been removed]
Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am 14.10.2015 10:09: > I think a tool like FBTraceManager from Thomas (Upscene) can do that for you Unfortunately not. The Trace API does not provide that level of granularity for PSQL code modules. This has been a long discussion in firebird-devel in the past, people basically argued that the Trace API isn't the right place for that sort of granularity, but more appropriate for a debugging/profiling feature. I still believe in the Trace API being a good profiling tool though. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: 13 October 2015 05:31 PM > To: firebird-support@yahoogroups.com > Subject: [firebird-support] How can I see which query within a stored > procedure execution takes the longest time? > > > > > > Greetings All, > > > > Firebird 2.5.4 > > I would like to know if there is any way that I can retrieve the execution > time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? > > Any tool to accomplish this? > > Thanks, > > Mike > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > Posted by: "Louis van Alphen" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I lost the logging code some time ago so can't quote it I'm afraid. I defined an external table, mapped to a file, with columns for timestamp, a couple of numeric values, and a text message. Then a procedure which wrote a record to this table. Then called the procedure at points in my code where I suspected there were bottlenecks. I haven't used this approach for a while because I found the profiling output easier to use (no need to instrument the code) and just as useful. This involves running the query select * from mon$call_stack order by mon$call_id desc from a PHP script once a second and (if you can be bothered) doing various post-processing on the result. The query appears to only work sometimes, and I don't know why, but it works often enough to be useful. This is Firebird 2.1. On 13/10/2015 19:30, 'stwizard' stwiz...@att.net [firebird-support] wrote: Hi Tim, Thanks for the reply. You mentioned that you did it with a logging procedure. Can you share what you exactly what you did to accomplish this? Mike -- Tim Ward
RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I think a tool like FBTraceManager from Thomas (Upscene) can do that for you From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 13 October 2015 05:31 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How can I see which query within a stored procedure execution takes the longest time? Greetings All, Firebird 2.5.4 I would like to know if there is any way that I can retrieve the execution time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? Any tool to accomplish this? Thanks, Mike [Non-text portions of this message have been removed]
RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
Hi Tim, Thanks for the reply. You mentioned that you did it with a logging procedure. Can you share what you exactly what you did to accomplish this? Mike
Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?
I did it by writing a logging procedure which wrote timestamps to an external file mapped to a text file, then calling it where I was interested. An alternative is Monte Carlo profiling - I've also got a script which polls the call stack from the RDB$ tables. The procedure or statement it hits most often is the one taking the most time! (This turns out not to be terribly reliable in terms of actually getting call stack data every time it asks, but it does seem to be pretty reliable in pinpointing the performance bottlenecks.) On 13/10/2015 16:30, 'stwizard' stwiz...@att.net [firebird-support] wrote: Greetings All, Firebird 2.5.4 I would like to know if there is any way that I can retrieve the execution time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? Any tool to accomplish this? Thanks, Mike -- Tim Ward