| Temporary Tables in SQR | KEVIN RESCHENBERG
10-31-2007 |
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.)
[UPDATE: This may work for SQL Server
and Sybase only. For other databases, look into the syntax for creating
local temporary tables.]
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.
|
