Determining when to use SQL routines or dynamically prepared compound SQL statements

Performance

If a dynamically prepared compound SQL statement can functionally meet your needs, using one is preferable, because the SQL statements that appear in dynamically prepared compound SQL statements are compiled and executed as a single block. Also these statements generally perform better than CALL statements to logically equivalent SQL procedures.

At SQL procedure creation time, the procedure is compiled and a package is created. The package contains the best execution path for accessing data as of the SQL procedure compile time. Dynamically prepared compound SQL statements are compiled when they are executed. The best execution path for accessing data for these statements is determined using the most up to date database information which can mean that their access plan can be better than that of a logically equivalent SQL procedure that was created at an earlier time which means that they might perform better.

Complexity of the required logic

If the logic is quite simple and the number of SQL statements is relatively small, consider using inline SQL PL in a dynamically prepared compound SQL statement (specifying ATOMIC) or in an SQL function. SQL procedures can also handle simple logic, but use of SQL procedures incurs some overhead, such as creating the procedure and calling it, that, if not required, is best avoided.

Number of SQL statements to be executed

In cases where only one or two SQL statements are to be executed, there might be no benefit in using an SQL procedure. This might actually negatively impact the total performance required to execute these statements. In such a case, it is better to use inline SQL PL in a dynamically prepared compound SQL statement.

Atomicity and transaction control

Atomicity is another consideration. A compound SQL (inlined) statement must be atomic. Commits and rollbacks are not supported in compound SQL (inlined) statements. If transaction control is required or if support for rollback to a savepoint is required, SQL procedures must be used.

Security

Security can also be a consideration. SQL procedures can only be executed by users with EXECUTE privilege on the procedure. This can be useful if you need to limit who can execute a particular piece of logic. The ability to execute a dynamically prepared compound SQL statement can also be managed. However SQL procedure execution authorization provides an extra layer of security control.

Feature support

If you need to return one or more result sets, you must use SQL procedures.

Modularity, longevity, and re-use

SQL procedures are database objects that are persistently stored in the database and can be consistently referenced by multiple applications or scripts. Dynamically prepared compound SQL statements are not stored in the database and therefore the logic they contain cannot be readily re-used.