Re: Stored procedure overhead

Paul, You may want to check to see if any of the talbes you are updating have a trigger on them. The SPL you are running will not complete untill all triggers have been executed. Also, if you are doing an update to a large table, or doing a lot of inserts, the SPL could be slowed down while committing the work. (SPL's automatically execute within a transaction I think.) Other than that I do not have any other suggestions, -Toby Denbow Paul Mailman wrote: > > Informix documentation advises that using stored procedures can improve > performance because the procedure is stored in the database in an executable > form. (Which makes sense, since you avoid syntax-checking, creation of query > plans, etc.) > > 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. > > The stored procedure only has four local SPL variables, does not create any > cursors (i.e. no "foreach"es), does not create any temporary tables, does not > return any values. What on earth is taking 6 seconds to clean up before > returning? Any suggestions of other things I could look to clean up to reduce > this time? > > It seems to me. BTW, that whatever is causing this can't _just_ be "stored > procedure overhead" because the logging itself is being done by stored > procedure and there is only a .2 second delay between the first log and the > second... > > Preceding was executed using dbaccess under Informix 7.1 (SE).