Re: Stored procedure overhead

Irwin Goldstein wrote: > > Paul Mailman <pamailman@qed.com> wrote in article > <c1f7cc$10310.7c@www.qed.com>... > > <snip> ... However, some recent experimentation appears to show that > there can be a > > surprisingly large overhead involved in stored procedure execution. I > wonder > > if anyone can provide any insight. > > > > I'd written a stored procedure that took what I thought was surprisingly > long > > to execute. To get a handle on where the performance bottlneck was, I > created > > a table that would log a timestamp and comment, and I wrote a "log" > procedure: > > > > create procedure log(comment char(30)) > > insert into logtable > > (sl_stamp,sl_text) > > values(CURRENT,comment);> > end procedure > > > > I sprinkled a number of calls to "log" throughout my procedure and then > > executed the following three commands together: > > > > execute procedure log("begin"); > > execute procedure myproc(); > > execute procedure log("end");> > > > Then I looked at the contents of logtable. I was quite surprised to see: > > > > sl_serial sl_stamp sl_text > > 2 15:15:52.6 begin > > 3 15:15:52.8 first log within myproc() > > ... > > 15:15:52.8 all other logs withing myroc() > > ... > > 15 15:15:52.8 last log within myproc() (just before end of > procedure) > > 16 15:16:06.4 end > > > > That is, within the resolution of the clock, the entire stored procedure > > happened essentially instantaneously, yet it took nearly 6 seconds to > return > > from myproc() and execute the "end" log. > > <snip> > > I seem to recall someone in this newsgroup mentioning tha the CURRENT > function call in SPL is resolved only ONCE for an entire call to a > procedure. Thus all of your logs within the same procedure will show the > same time, even if the procedure took hours to execute. Can anyone else > verify this? If this IS the case, the bottleneck may be in the procedure > itself rather than the overhead of calling it. > > -- Even if that is the case, it would not apply to Paul's test. He made SEPARATE calls to his log procedure from outside and from within the main procedure. The call to CURRENT was made from within the log procedure, so it probably returned the real time. Gabriel ---