Re: What's the problem with stored procedures

Kirk Waterhouse wrote: |I had an Informix employee (a fairly high level consultant type) tell |me that I should not use stored procedures with Informix. He said that |Informix did a very poor job of implementing stored procedures. He said |that they were parsed at run-time, showed poor performance and in |general caused problems within the engine. He said triggers were at |least as bad. An Informix employee? Nah, sounds like it must have been an Oracle employee! ;-) |I was wondering if the user community has experienced problems with SPL |and triggers in Informix. I have warned classes not to implement their whole blessed application in store procedures, partially because of a limited cache of procedures that would thrash in the cache if you used more than the limit (50). More recently I was informed of an environment variable set before running oninit (the name escapes me, as usual) that can raise this limit. Another reason not to overuse SPL is strictly my opinion: that the engine is a database processor, not a specialized language processor. Furthermore (and this is just plain common sense), there is a certain overhead in interpreting SPL and executing its statements, as opposed to running the same statements in straight [prepared] SQL. I would never claim that using SPL is more efficient [on a local server] than straight SQL. However, this is no reason to avoid them in general. SPL simplifies reams of common operations, implement business rules, and provides security for common operations. And on a remote server, really will save time by bypassing communication overhead. When I was doing tech support, the only complaint I ever had about SPL was from a user who *did* implement every blessed database operation as a stored procedure. Anybody know a diplomatic way to say "Nya Nya, I told you so!" ? -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+