Application Design and Tuning for Performance › Avoiding Redundant Statements › Avoiding Programmatic Joins

Avoiding Programmatic Joins

If you are using a modern generic application design with an access module for each DB2 table or if you are using an object-oriented or service-oriented design, you are using a form of programmatic joins. In almost every situation in which an SQL join can be coded instead of a programmatic join, the SQL join outperforms the programmatic join.

Consider the following trade-offs when you use SQL join versus a programmatic join:

• Flexible program and table access design versus improved performance for SQL joins
• Extra programming time for multiple SQL statements to perform single table access when required or joins when required versus the diminished performance of programmatic joins
• Extra programming that is required to add control breaks in the fetch loop versus the diminished performance of programmatic joins

DB2 provides the INSTEAD OF trigger (DB2 9) feature that allows mapping between the object world and multiple tables in a database. You can create a view to join two tables that are commonly accessed together. The object-based application then uses the view as a table. Because the view is on a join, it is a read-only view. However, you can define an INSTEAD OF trigger on that view to allow INSERTs, UPDATEs, and DELETEs against the view. You can code the INSTEAD OF trigger to perform the necessary changes to the tables of the join using the transition variables from the view.

In our tests of a simple two-table SQL join versus the equivalent programmatic join (FETCH loop within a FETCH loop in a COBOL program), the two-table SQL join exhibited 30 percent less CPU than the programmatic join.