Re: What's the problem with stored procedures

Posted in 1997

Felix Koshy Mathews — 17 Nov 1997 21:18:24 -0500 — source: Informix-list mailing list archive (1991-1998)

--------------AC2830B8761D14BB071347BB Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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! ;-) > > |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. > <snip> Informix-SPL is not that bad, ofcourse the systax and operators are little primitive ( C-like ), yet they are really powerful if you use them properly. General business logic/validations can be done in SPL which will get loaded/stored once and used many times, by many executables. There was one problem we had with a typical combination of triggers and SPL which was reported to Informix and they fixed it in On-Line 7.23. We use SPL and triggers and we really appreciate the way it works so if you are using engine 7.23 or heigher, and want to better overall performance of your application then chalk out most commmonly used validation-rules/business-logic and implement them in SPL. Keep in mind that SPL needs to be loaded from database, if it is not already loaded into memmory, so convert only most commonly used validations/rules into procedures, in which case, the ist reference will load it and the rest will directly execute it from the memmory. BTW ask that that "Informix employee" how long he have been working with Informix products, I think he might have been a rocket scientist who was laied-off from that field and then joined Informix. -- Have a nice day Felix K. Mathews mailto:fmathews@systems.dhl.com --------------AC2830B8761D14BB071347BB Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <HTML> Jacob Salomon wrote: <BLOCKQUOTE TYPE=CITE>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.  </BLOCKQUOTE> <snip> Informix-SPL is not that bad, ofcourse the systax and operators are little primitive ( C-like ), yet they are really powerful if you use them properly. General business logic/validations can be done in SPL which will get loaded/stored once and used many times, by many executables. There was one problem we had with a typical combination of triggers and SPL which was reported to Informix and they fixed it in On-Line 7.23. We use SPL and triggers and we really appreciate the way it works so if you are using engine 7.23 or heigher, and want to better overall performance of your application then chalk out most commmonly used validation-rules/business-logic and implement them in SPL. Keep in mind that SPL needs to be loaded from database, if it is not already loaded into memmory, so convert only most commonly used validations/rules into procedures, in which case, the ist reference will load it and the rest will directly execute it from the memmory. BTW ask that that "Informix employee" how long he have been working with Informix products, I think he might have been a rocket scientist who was laied-off from that field and then joined Informix. -- Have a nice day Felix K. Mathews <A HREF="mailto:fmathews@systems.dhl.com">mailto:fmathews@systems.dhl.com</A>  </HTML> --------------AC2830B8761D14BB071347BB--