Re: Anybody Knows The Black Magic Behin SPL Performance?

Bashar Chalabi writes: <snip> > I have to admit that the update triggers around 10 procedures, which in turn make updates, > and so on... The total number of procedure calls triggered by the "simple" update is around 40. > > One innocent "execute procedure" with no argument and no return > values was taking 0.35 seconds out of the 0.5. The funny thing is the procedure was really > not doing much. An indexed select followed by a small insert. In any case, we changed the > procedure so that it really does not do anything, but it was still taking 0.35 seconds to do > nothing. <snip> > A call to a procedure with an empty body (ie., create procedure XYZ () end procedure) was taking > 0.35 seconds. In the process of removing and reinserting the offending execute procedure call, we > once inserted it in the wrong place, but miracle, we were down to 0.15 seconds for the whole thing. > > The short conclusion of this long story is by swaping 2 execute procedure calls, we are able to > move from 0.5 second to 0.15 second. This is the exact same SPL code being executed, with the > same data. > > Can anybody contribute a morale, or at least a happy ending? The engine tries to cache stored procedures to improve performance, however it has a limited amount of memory available for this. With the large number of stored procedures you are using, you may have hit the wall at or around the point the one offending procedure is being called. I believe that some later versions (perhaps 7.11 or later?) have some enviornment variables which can be used for controlling the size of the stored procedure cache and/or for setting up a "private" cache for a particular session. Check the release notes in $INFORMIXDIR/release. I think this is where I saw reference to the enviornment variables. This may or may not have something to do with what you are experiencing. In any case it's probably worth checking out considering your heavy use of stored procedures. HTH Irwin Goldstein Objective Software Systems, Inc.