October 31, 2007

Temporary Tables in SQR

Set-based processing, as we know, can be a very efficient way to update your data. It is nearly always more efficient than processing one row at a time. Application Engine programs tend to have a reputation for being fast and efficient due to their reliance on set-based processing. Most App Engine programs use multiple temporary tables—often to excess. SQR, of course, can do set-based processing just as well. It also does not force you into this model, but simply supports it as an option. However, some people may run into trouble using temporary tables in SQR and then give up, reverting back to...App Engine! (Today being Halloween, I thought it appropriate to mention something scary. But enough of that...)

If you just create a temporary table in your SQR program and then try to use it, you may have syntax errors, depending on how it is used. This can lead to the confusion. Why won't SQR recognize the table I'm creating? I've written about this issue before but today wanted to expand on one of the options.

SQR sends SQL in BEGIN-SELECT sections to the database before the program runs. If you are selecting from a temporary table and that table does not yet exist, the database will report an error and the program won't run. (This does not seem to affect BEGIN-SQL sections, which are deferred until run time.) There are two ways around the difficulty.

The first and probably best method is to create your table in a SETUP section. BEGIN-SETUP is executed before the program starts to run. BEGIN-SETUP is intended to give SQR and the database instructions needed before SQR can even interpret your program code. If you create your table in a SETUP section, the table will exist when SQR begins interpreting the rest of your program. Here is the pattern I generally use:

begin-setup

#define TEMP_EMPLOYEES  ##EMPS   ! Or a "permanent" table name if testing

  begin-sql on-error=skip
    DROP TABLE {TEMP_EMPLOYEES}
  end-sql

  begin-sql
    CREATE TABLE {TEMP_EMPLOYEES}
    (EMPLID VARCHAR2(11)
    ,NAME   VARCHAR2(30)
    )
  end-sql

end-setup

The name ##EMPS in this example designates a truly temporary table that will be dropped when your program finishes. (The double ## distinguishes this from a numeric variable name.) So why is there a DROP TABLE step? Often during development and testing of a program, I like to keep the results around for review after the program runs. In the SQL Server and Sybase family of databases there is a "tempdb" that can be used for this. So the #DEFINE would appear like this:

#define TEMP_EMPLOYEES  tempdb..EMPS

You could also use a regular name such as TEMP_EMPS. Avoid using PS_ names for these tables.

The other solution to the problem is to drop and create your table within the normal flow of the program (in a procedure), but trick SQR into deferring the preparation of your SELECT until run time. This is done by using dynamic SQL and hiding the name of the table in a variable:

  let $TableName = '{TEMP_EMPLOYEES}'
  begin-select
EMPLID
NAME
      do ...
    FROM [$TableName]
    WHERE ...
  end-select

This method can have a performance penalty if you are executing the SELECT many different times, just as with any other dynamic SQL, since it must be prepared again each time it's encountered. But it would be fine if you are executing the SELECT only once or a few times. You might also use this method if, for example, you need to create the temporary table only under certain conditions that are not known until run time.

Until next time...