DB2 UDB for OS/390 and z/OS Application and SQL Performance Trade-Offs

by Daniel L. Luksetich
April 1, 2005

The major advantage to stored procedures occurs when they’re implemented in a client/server application that must issue several remote SQL statements. The network overhead involved in sending multiple SQL commands and receiving result sets is significant, so proper use of stored procedures to accept a request, process that request with encapsulated SQL statements and business logic, and return a result will lessen the traffic across the network and reduce the application overhead (see Figure 6).

If a stored procedure is coded in this manner, then it can be a significant performance improvement. Conversely, if the stored procedures contain only a few or one SQL statement, the advantages of security, availability, and reusability can be realized, but performance will be worse than the equivalent single statement executions from the client due to task switch overhead (see Figure 7).

Triggers and Constraints

Triggers and constraints can be used to move application logic into the database. The greatest advantage to triggers and constraints is that they’re generally data-intensive operations, and these types of operations are better performers when placed close to the data. These features consist of:

- Triggers

- Database-enforced RI

- Table check constraints.

A trigger is a database object that contains some application logic in the form of SQL statements that are invoked when data in a DB2 table is changed. These triggers are installed into the database, and are then dependent upon the table on which they’re defined. SQL DELETE, UPDATE, and INSERT statements can activate triggers. They can be used to replicate data, enforce certain rules, and fabricate data.

Database-enforced RI can be used to ensure that relationships from tables are maintained automatically. Child table data cannot be created unless a parent row exists and rules can be implemented to tell DB2 to restrict or cascade deletes to a parent when child data exists.

Table check constraints are used to ensure values of specific table columns and are invoked during LOAD, insert, and update operations.

Triggers and constraints ease the programming burden because the logic, in the form of SQL, is much easier to code than the equivalent application programming logic. This helps make the application programs smaller and easier to manage. In addition, since the triggers and constraints are connected to DB2 tables, they’re centrally located rules and universally enforced. This helps to ensure data integrity across many application processes. Triggers can also be used to automatically invoke UDFs and stored procedures, which can introduce some automatic and centrally controlled application logic.

There are advantages to using triggers and constraints. They certainly provide for better data integrity, faster application delivery time, and centrally located reusable code. Since the logic in triggers and constraints is usually data-intensive, they typically outperform the equivalent application logic simply because no data has to be returned to the application when these automated processes fire. There’s one trade-off for performance, however. When triggers, RI or check constraints are used in place of application edits, they can be a serious performance disadvantage. This is especially true if several edits on a data-entry screen are verified at the server. It could be as bad as one trip to the server and back per edit. This would seriously increase message traffic between the client and the server. For this reason, data edits are best performed at the client when possible (see Figure 8).