Re: SPL & Triggers - do's and don'ts

Yo! Get a real name for the newsgroups. This ain't a chat room, y'know! ;-)) Hot Desk10 wrote: > > Has anyone got a useful do's and don'ts guide to developing > SPL / triggers? > > I am involved in a project that is making extensive use of SPL (I > am pretty sure that this is a don't all by itself!) and am concerned > about possible performance considerations due to parameter passing, > cache, nested calls etc. We are running Online 7.2 on HP UX 10. Dear Hot, ;) As far as I recall, the SP cache is still limited to 50 procs. This is not [yet] a tunable to keep those feature requests coming, folks! Because of this limit, if you have a huge library of commonly called procs, you will thrash the cache to death and those poor techies in Lenexa will go home with heartburn. So have a heart; don't run your whole fershluggener application in SPL, despite the temptation to build elegant black boxes. (IMO, this whole SPL thing grew it's own pseudopods once folks started to see how great they are. Maybe Informix will raise or even remove the ceiling on cached procs.) Even without the cache issue (it's corporately incorrect to say the word "problem"), bear in mind that the database engine was designed to be just that. The SPL is an afterthought to the design. It is NOT an efficient language processor (like perl?), only a very elegant one. Another consideration: SPL cannot process subscripts as variables. i.e the expression string_var[x, 10] generates a syntax error while the expression string_var[4, 10] is perfectly OK. This makes real programming awkward. Just remember that SPL is meant to "encapsulate" compound database statements. Excessive use for programming is hazardous to your benchmarks. A couple of items on triggers: WHile one ma complain that they slow down processing (and they do), bear in mind that these checks would have to be done by the applications if not done by the engine. And that would be even slower. The fast-running apps are skipping the business-rule integrity checks and always have overworked programmers working 120-hour weeks. That said, I would caution you against overdoing the triggers bit as well. Why? Because the side effects of cascading triggers can confuse the admin folks. I only meant to delete 1 row but my SQLCA tells me I impacted 5623 rows (inserts, other deletes, updates). Also, a deliberate feature in triggers: If you create a trigger based on a column, you cannot reference that column in the trigger body. And don't try hiding that reference in a called procedure - I've tried that! And don't try hiding it in a cascaded trigger - it will catch you @ run time. Implication: If you have a self-referential master-detail relation, your triggers are unusable. Note that this is not considered a bug. There are legitimate concerns in consideration. But many folks have requested that this restriction be removed, so who knows? > We have invested in Informix training courses, read the manuals and > bought the only book on the market place about this subject - any > suggestions based on development experience would be much appreciated. Excellent choice. Even mediocre training saves hundreds of man.. er, person-hours in development costs. Too bad so many users see it as giving their employees a week of. -- Jake