Re: Set Explain with SP's

Jim Gordon (jgordon@us.DHL.COM) wrote: : On Oct 10, 1:29pm, Jon Vemo wrote: : > Subject: Set Explain with SP's : > In attempting to determine how the optimizer is processing queries in : > stored procedures, we've tried turning 'set explain on' within a procedure. : > However, when executing the procedure, I get an empty file. : > : > Is there anything special which needs to be done, to get set explain output : > from stored procedures?? : > : > Thanks : > Jon Vemo : > : >-- End of excerpt from Jon Vemo : Jon, : You only get Set explain output from an SP when you create it as this is when : the optimisation is done. : Cheers - Jim You can also get the explain output by: SET EXPLAIN ON; UPDATE STATISTICS FOR PROCEDURE [procname]; It is generally a good idea to update statistics on your procedures regularly to re-optimize them as the profile of the tables changes. ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111