Improving performance of procedures and functions

Some changes are in the design of a routine and some are in the implementation. For example, differences between how the C language compiler handles host variables and the way the SQL procedural processor requires the host variables to be handled can cause many calls to the database engine. These calls are very expensive and, when done many times, can significantly degrade performance.

The IBM® i 6.1 release contained significant improvements to the performance of the code generated within SQL routines. If you have SQL procedures or functions that have not been rebuilt since before IBM i 6.1, it is recommended that you drop and recreate them to guarantee that your procedure or function is running with the improved code generation.

Another simple action which will improve the performance of SQL procedures is to use the PROGRAM TYPE SUB clause. When omitted or PROGRAM TYPE MAIN is used on the CREATE PROCEDURE (SQL) statement, an ILE C program (*PGM) is built for the procedure. PROGRAM TYPE SUB results in an ILE C service program (*SRVPGM) being built for the procedure. The use of PROGRAM TYPE SUB is most relevant for procedures that are frequently called within a performance critical application. PROGRAM TYPE SUB procedures perform better due to the fact that ILE service programs are activated a single time per activation group, while ILE programs are activated on every call. The cost of an ILE activation is related to the procedure size, complexity, number of parameters, number of variables, and the size of the parameters and variables.

The only functional difference to be noted when using PROGRAM TYPE SUB is that the QSYS2.SYSROUTINES catalog entry for the EXTERNAL_NAME column is formatted to show an export name along with the service program name.