How to performance test nested Sybase stored procedures?

Question

I am looking for any tool which will allow the performance testing/tuning of Sybase nested stored procedures. There are many tools around and of course Sybase's own for performance tuning and testing SQL but none of these can handle nested stored procedures (i.e. a stored proc calling another stored proc). Does anyone have/know of such a tool?

Answer 1

I don't know anything that does this but I'd love to see a tool that does. What I tend to do in this situation is to try to establish which of the nested stored procedures is consuming the most resources or taking the longest and then performance tuning that procedure in isolation.

Answer 2

Hi Andy,

I am not sure which Sybase DB you are using at the moment, but have you tried the Profiler in the Sybase Central tool? Right Click on DB Connection and then select PROFILE (or PROFILER???)

I have used it in the past for single stored procedures but I do not recall if it works all the way down the calling chain from one SP to another. At the least it should tell you how long each sub-SP which was called from your initial SP took and then you can home in on the procedures needing the most time.

I hope that helps.

Cheers,

Kevin

Answer 3

Late to the game, but in Sybase you have the option of using "SET FMTONLY" to get around "SET NOEXEC" turning off the evaluation of the nested procedure.

For example:

suppose: sp_B is defined sp_A is defined and calls sp_B

Then, the following will show the execution plans for both sp_A and sp_B

SET SHOWPLAN ON GO SET FMTONLY ON GO sp_A GO

See the sybase writeup here...this worked in ASE 12.5 as well as ASE 15. Using set showplan with noexec