June 6, 2005
Brenda wrote to ask if SQR programs are compiled.
She was wondering if they are compiled when we specify the -RS
flag and/or at runtime.
The short answer is that, no, SQR programs are never
really compiled. They are, however, scanned and
interpreted completely before execution starts.
When we submit an SQR program to SQRW.EXE (whether directly
or by running an SQR from a PeopleSoft menu), SQRW performs
several steps. First, it scans the program and interprets
the various directives such as #DEFINE, #INCLUDE and
#IFDEF. (These are handled at this stage, before
execution starts. Putting a #DEFINE inside of an IF/END-IF
block is logically meaningless.) SQL statements are
checked by the database--unless we have included
dynamic SQL using variables inside of brackets ([$MY_WHERE_CLAUSE]).
The SETUP section is also run.
All of this happens quickly, as long as there isn't a lot
of processing being done in the SETUP section. It happens
each time we submit a program to SQRW.
But there is an option to do this once and save the
interpreted program to be run later. Include the -RS
command line flag to invoke this "compile" option.
The program will then be scanned and validated, and
a version of the program will be written as an .SQT file.
This is not really a compiled version. It's not machine
code, but rather an intermediate format that SQR can
use efficiently. This file can then be run using the -RT command
line flag, and the initial scan and validation of the
program will be skipped because all of the results are
already available to SQR in the .SQT file.
There are a couple of reasons that you might want to
"compile" a program. First, there may be a small performance
improvement since the program does not need to be scanned
every time it is run. Second, using the -RS flag allows
you to "freeze" the values of ASK variables, the contents
of #INCLUDE files, and the results of SETUP sections.
Let me explain that. An ASK variable is resolved at the
time an SQR is initially interpreted. It's a prompt that
the user answers. The value of the variable then becomes
a constant in the program, just as a #DEFINE creates
a constant. When you "compile" a program using -RS, the
same thing happens, but the value of this constant is
written into the .SQT file and it will never change.
When you run the .SQT file later, the ASK value is already
known and the user is not prompted for it.
Similarly, #INCLUDEs are resolved once and SETUP sections
are run once. You can then run the .SQT file repeatedly
and these steps are not done again.
So using this option lets you "freeze" the program.
You don't need to worry about what would happen if
someone changed an #INCLUDE (.SQC) file, and you get the
benefit of the improved performance.
So, should we all start using this feature? I don't
think so. First, the performance gain would seem to be
negligible. Start a large SQR using SQRW and you can
see how quickly the initial scan is performed. Why
bother with a separate step every time you change the
program just to save a couple of seconds?
Also, "freezing" the program means that improvements
in your .SQC files won't be available to the program.
Also, SETUP sections won't run, and if you are doing
something that is needed each run--creating a temporary
table, for example--that won't work.
And then we also have the problem of not knowing for
sure whether the .SQR "source" file matches the
.SQT "object" file. If there is an error in the program,
and you look at the .SQR file to find it, you can never
be completely sure that they are the same program.
So although the pre-scan feature is available to us, it doesn't
seem to offer many advantages in everyday use. There may
be situations in which it would be handy--for example, in
creating many versions of the program using different
ASK values--but even those cases can be handled using
run control or other methods.
Until next time...