Faster reports in Priority

Caveat: there is little reason to read this post if you don't use Priority!

As I may have written before, my company merged two Priority databases a few months ago. One result of this is that certain reports now work very slowly: this is because they have to create a temporary table containing parts. Once this table contained about 35,000 rows but now it contains around 900,000 rows! This means that counter-intuitively, reports will work faster if they are not passed a list of parts.

To be more accurate, the time that a report requires to operate can be divided into three:

1. Obtaining parameters
2. SQL statements on those parameters
3. Displaying the results

It's stage one which can take a large amount of time. Today I discovered a method of  shortening this time, which I am going to describe here.

If there is a parameter requesting a range of part numbers, it seems that Priority first creates a temporary file, then it copies all the existing parts into that file so that the user may choose parts, then gets the user's input and then creates a second temporary file with the chosen parts (this step generally takes place in stage two as described above, under the programmer's control). Thus if there are 900K parts in the database and the user chooses all of them, 1.8M parts will be copied!

Obviously, there should be a way of allowing the user to choose parts (which means that the first copy will have to take place), but if the user wants the report to work on all the parts, or the parts can be distinguished by means other than their part number, then it's better not to ask the user to choose parts at all.

The way I achieved this was as follows. I wrote a multi-stage procedure in Priority:

1. Obtain values for other tables (e.g. warehouses) along with an option asking whether the user wishes to choose parts
2. If the option is not 'Y', then jump to stage 4
3. Obtain values for the parts table
4. Process the data
5. Output the data

At the beginning of stage 4, I entered the following lines:

GOTO 10 WHERE :$.OPT <> 'Y';
LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
LABEL 10;

The action resulting from above is that the processing stage will use either the chosen list of parts (the "linked table", in Priority speak) or all the parts; the number of parts is not particularly important to SQL, which is a set orientated language. The hack is the amount of time saved by not building (twice) the temporary list of parts.

For my reference, this tip is used in procedure GLOB_PART_MONTHS.

[SO: 3947; 3, 16, 37]