Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

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.