Re: What's the problem with stored procedures

Jacob Salomon wrote: > > 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! ;-) Yeah! > |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). Oh poohy! You have always been able to change the default value of 50. It's just that you have to add the configuration parameters to your configuration file manually. PC_POOLSIZE and PC_HASHSIZE I think. PC_HASHSIZE must be a prime number. I don't know what the limits of these are except the size of SHM. > More recently I was informed of an environment variable set before > running oninit (the name escapes me, as usual) that can raise this > limit. Never used one, but probably the same name as the configuration parameters if others are anything to go by. > 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 agree, you shouldn't put ALL SQL statements into SPs. But SPs are intended to optimise code usage and reduce network traffic. If they are used that way, then they are great. SPs should be used when you need to execute a large number of SQL statements together, frequently. > I would never claim that using SPL is more efficient [on a local > server] than straight SQL. Why not?? ;-) > 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. Exactly! > 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!" ? How about: "I believe that WAS the conclusion we arrived at before." :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+