Re: Triggers & Stored Procedures

schung@edd.ca.gov wrote: > Hi, Is anyone out there using triggers and stored procedures in > Informix > for performance reason? Can someone identify the pros and cons of > using > them? Our system is a web application (on NT) accessing Informix > database (on Unix). We are thinking about using the triggers may save > us > time on passing the SQLs over the network to the Informix engine. The > > Informix manual describes that we use triggers for audit trail or to > minimize redundant code in applications but does not mention anything > about performance. > > We welcome and appreciate any input. Thanks. > > -------------------==== Posted via Deja News > ====----------------------- > http://www.dejanews.com/ Search, Read, Post to Usenet 1. The performance advantage from using triggers or stored procedures is minimizing the overhead of contacting the database server. For stored procedures that perform several SQL statements at once, this advantage is noticable across a shared memory database connection and potentially very significant across network connections. Another consideration for on-line transaction systems is that by reducing the elapsed time of the transaction, you can greatly reduce the lock contention on heavily updated tables. 2. One thing to remember is that while Informix lets you alias tables, there is no way to alias procedure names. This means if multiple users connect to the database through your application, either the procedures need to be recreated for each user or the owner of the procedure needs to be hardcoded where one procedure calls/executes another. 3. Another downside of using stored procedures is that they are difficult to debug. Debugging is limited to trace statements or "system" calls, so the stored procedures I use tend to be transaction oriented (i.e. given a set of input, apply updates to a group of tables, perform the commit/rollback and report back with status codes or re-raising the database exception codes back to the application. Testing this type of procedure is manageable without adding excessive "debugging" code that needs to be removed when the procedure has been thoroughly tested. 4. There are other advantages of using stored procedures as well. Maintaining transaction control, data integrity and security can also be implemented using trigger's and stored procedures. For performance the more SQL statements you put into the procedures the bigger gain you'll see. Remember that while your client can do most calculations and complex decision logic much faster than the database server's SPL code, network transfer times can totally offset that advantage. So balance the non-SQL code in your procedures against the expected network transfer times and database server loads. John Kroll