Stored Procedures Limitation
OptionJ, on Informix 7.1, asked whether a reported limit of 16 running stored procedures meant the 17th must wait. Superboer had never heard of such a limit, suggested testing it and warned against overusing SPL. scottishpoet said thousands of users can run procedures concurrently. bozon suggested STACK_SIZE governs recursion depth. No confirmation of any limit.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I found a reference that tells me that Informix can only have 16 stored procedures running at one time. Currently, we have a variety of things that are set up as stored procedures that take a long time to process. If there are 16 running at one time, does that mean that the 17th must wait until one finishes in order to be loaded into cache so that it can start processing? I believe that we are running an older version, 7.1, of Informix. If this is enhanced in later versions, I'd be interested in knowing that as well. Many of these processes have been, in my opinion, incorrectly set up as SP's. If this is also causing a performance hit, then I would be better able to request time and resources to make these changes. Thank you! Option J Upton
--I found a reference that tells me that Informix --can only have 16 stored --procedures running at one time. Never heard of that limitation. Test it yourself should not be that hard to do.. spl's are nice and easy however use them with care; DO NOT write a whole application in spl, do not pass in or return tonnsss of variables do not missue them if you do, it'll bite you. problems to be expected when missused: a lot of shared memory in the virual part is given to a session which executes a lot of spl unexpected locks on the catalog when permissions are added.... etc.. Superboer.
Where dd you find this reference What does this "Informix can only have 16 stored procedures running at one time" mean. 1/ 16 users running different procedures and the 17th has to wait? 2/ One procedure that calls another then another then another until you are 16 deep? if you think its teh first then this is not correct, informix can have thousands of users all running procedures at the same time. If its the second then there may be some procedure management limitation that I am currently unaware of. This could potentially be argued to be a design issue. OptionJ wrote: > I found a reference that tells me that Informix can only have 16 stored > procedures running at one time. > > Currently, we have a variety of things that are set up as stored > procedures that take a long time to process. If there are 16 running > at one time, does that mean that the 17th must wait until one finishes > in order to be loaded into cache so that it can start processing? > > I believe that we are running an older version, 7.1, of Informix. If > this is enhanced in later versions, I'd be interested in knowing that > as well. > > Many of these processes have been, in my opinion, incorrectly set up as > SP's. If this is also causing a performance hit, then I would be > better able to request time and resources to make these changes. > > Thank you! > > Option J Upton
>scottishpoet said >If its the second then there may be some procedure management >limitation that I am currently unaware of. This could potentially be >argued to be a design issue. STACK_SIZE? - the default may only allow a certain depth of recursion but this can be changed and I would think also it would be a factor of just how complicated the procedure is the move variables and parameters the more that would go on the stack. I have never heard of the 16 limitation before but it is a power of 2 so it must be true. ;-)