Saving SQL resources

SQL cursors and prepared statement consume resources that should be freed when useless.

To write efficient SQL in your programs, you can use dynamic SQL. However, when using dynamic SQL, you allocate an SQL statement handle on the client and server side, consuming resources. According to the database type, this can be a few bytes or a significant amount of memory. When executing several static SQL statements, the same statement handle is reused and thus less memory is needed.

The language allows you to use either static SQL or dynamic SQL, so it's in your hands to choose memory or performance. However, in some cases the same code will be used by different kinds of programs, needing either low resource usage or good performance. In many OLTP applications you can actually distinguish two type of programs:

• Programs where memory usage is not a problem but good performance is needed (typically, batch programs executed as a unique instance during the night).
• Programs where performance is less important but memory usage must be limited (typically, interactive programs executed as multiple instances for each application user).

To reuse the same code for interactive programs and batch programs, you can do this:

1. Define a local module variable as an indicator for the prepared statement.
2. Write a function returning the type of program (for example, 'interactive' or 'batch' mode).
3. Then, in a reusable function using SQL statements, prepare and free the statement according to the indicators, as shown in the next example.

PRIVATE DEFINE up_prepared BOOLEAN

FUNCTION getUserPermissions( username )
  DEFINE username VARCHAR(20)
  DEFINE cre, upd, del CHAR(1)

  IF NOT up_prepared THEN
     PREPARE up_stmt FROM "SELECT can_create, can_update, cab_delete"
                             || " FROM user_perms WHERE name = ?"
     LET up_prepared = TRUE
  END IF

  EXECUTE up_stmt USING username INTO cre, upd, del

  IF isInteractive() THEN
     FREE up_stmt
     LET up_prepared = FALSE
  END IF

  RETURN cre, upd, del

END FUNCTION

The first time this function is called, the up_prepared value will be FALSE, so the statement will be prepared. The next time the function is called, the statement will be re-prepared only if up_prepared is TRUE. The statement is executed and values are fetched into the variables returned. If the program is interactive, the statement is freed and set the up_prepared module variable back to FALSE, forcing statement preparation in the next call of this function.