Re: Informix 7.12: Stored Procs vs Roles

philcya@aol.com wrote: > > We are using PowerBuilder 5 for our development tool. However, the > Director of our Data Architecture group is toying with the idea that to > ensure database integrity ALL access to the database must be done via > stored procedures. I am trying to convince him that action will cause > loss of productivity on the development side of the house (since PB's data > window has a good Update function) and that we could do the same thing > using Informix's ROLE ability. Every use would be granted minimal access > rights from their login and each application would then set their role for > the duration of the application session to whatever role gives them the > access rights needed. This way we could get the best of both worlds: data > integrity and faster development. Whoa there, Phil! Your instincts are on the right track but overuse of SP has some inherent problems. These stem from the fact that the database engine - which interprets the Pseudo Code of the SPL - was never designed to be an efficient languae processor. This is not a put down, simplyhte reality of what it is and is not. The bigger problem, however, is caused by a built-in limit in the stored procedure cache. As of this writing, the limit is 50 procedures. (I don't recall seeing this capacity raised in 7.2.) If you have many tables and correspondingly many SP's - all frequently used - you will have the engine thrashing them in & out of memory. I have been preaching that stored procedures are NOT a replacement for writing application code. They were intended as a unified way of consistently complying with business rules across many applications on one database. What is required to make Informix raise this limit? More than anything, a substance known as "green gease". ;-) However, before you apply the pressure, bear in mind that the SP caching algorithm is very simple. To start managing more cached SP's, the developers might be forced to implement a complex scheme (like multiple LRUs) for the stored procedure cache. This gets into the category of "be careful what you ask for; you might get it". I hope I have convinced you to be wary of this overuse of SPL. (And to avoid cliches like the plague! ;-) -- Jake Salomon