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

by Daniel L. Luksetich
April 1, 2005

SQL that runs in SPUFI can run in a batch job, on a Web server, or even on a PC using software such as Microsoft Excel. So the more logic that can be placed into a complex SQL statement, the more portable that logic becomes. Complex SQL statements that are a part of COBOL programs today can easily be ported to a Web application server on an Intel-based machine. The more logic that’s incorporated in the SQL statement, the less logic that needs to be translated from COBOL into another language such as Java.

The SQL language is also easier to code than most other application programming languages. For example, C programs that are thousands of lines in length can be rewritten as SQL statements that are hundreds of lines in length. Simplifying and shortening the code can mean faster time to implement and test fixes or changes to the code. Since SQL is a standardized language, there’s a better chance that more people are going to be qualified to work with it than with a language such as COBOL or C. Most Java programmers know SQL, but fewer know C, and even fewer know COBOL. Programmers who have written SQL to access Oracle databases can write SQL that accesses DB2.

The reusability, flexibility, and portability of complex SQL doesn’t come without a performance price. Complex SQL can be an extreme performance advantage if the program processes written into the SQL statement aggregate or filter data. If the complex SQL statement logic is data-intensive, then it will be a performance gain over the equivalent COBOL or Java logic. If the application program can issue one large statement that returns a result rather than many smaller statements that return data, it will gain all the advantages that complex SQL has to offer and a performance advantage. If a complex SQL statement processes data, rather than filters it, then it can be a performance detriment. SQL statements that use lots of UDFs, CASE expressions, and data conversions in the SELECT clause may impact performance. Nested table expressions that contain expressions in a SELECT clause and then have references to those nested expressions in outer expressions may also significantly impact performance.

So, complex SQL statements can be a performance advantage or disadvantage. Data-intensive logic is almost always a performance advantage; data-processing logic is almost always a performance disadvantage. The trade-off is being able to balance the performance with the reusability, flexibility, and portability of complex SQL (see Figure 2).

User-Defined Functions

Functions are a useful way of extending the programming power of the database engine. Functions let us push additional logic into our SQL statements. User-defined scalar functions work on individual values of a parameter list, and return a single value result. A table function can return an actual table to an SQL statement for further processing (just like any other table).