Performance with dynamic SQL

Comparing static SQL statements and dynamc SQL statements used in a loop.

Although SQL statements can be directly specified in the program source as a part of the language as static SQL, it is sometimes more efficient to use dynamic SQL instead, when you are executing SQL statements repeatedly.

Dynamic SQL allows you to PREPARE the SQL statements once and EXECUTE N times, improving performance.

Note however that implementing prepared statements with dynamic SQL has a cost in terms of database resources and code readability: When a simple static SQL statement is executed, database client and server resources are allocated for the statement and are reused for the next Static SQL statement. With dynamic SQL, you define a statement handle and allocate database resources that last until you FREE the handle. Regarding code readability, static SQL statements can be written directly in the source code (as another language statement), while Dynamic SQL uses several instructions and takes the SQL text as a string expression. Additionally, static SQL statements are parsed at compile time so you can detect syntax errors in the SQL text before executing the programs.

Therefore, dynamic SQL should only be used if the SQL statement is created at runtime (with a where part generated by a CONSTRUCT for example) or if the execution time is too long with static SQL (assuming that it's only a statement preparation issue).

To perform static SQL statement execution, the database interface must use the basic API functions provided by the database client. These are usually equivalent to the PREPARE and EXECUTE instructions. So when you write a static SQL statement in your program, it is actually converted to a PREPARE + EXECUTE behing the scene.

For example, the following code:

FOR n=1 TO 100
   INSERT INTO tab VALUES ( n, c )
END FOR

is actually equivalent to:

FOR n=1 TO 100
   PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
   EXECUTE s USING n, c
END FOR

To improve the performance of the preceding code, use a PREPARE instruction before the loop and put an EXECUTE instruction inside the loop:

PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
FOR n=1 TO 100
   EXECUTE s USING n, c
END FOR