Understanding Temporary Tables

Because Application Engine programs run in batch mode, multiple instances of the same program often execute in parallel. When this happens, the risk of data contention and deadlocks on tables is significant. To avoid this risk, you can dedicate specific instances of temporary tables for each program run.

You can also use temporary tables to improve performance. For example, if you find that, multiple times during a run the program accesses a small subset of rows from a much larger table, you can insert the necessary rows into a temporary table as an initialization task. Then the program accesses the data residing in the smaller temporary table rather than the large application table. This technique is similar to reading the data into an array in memory, except that the data never leaves the database, which is an important consideration when the program employs a set-based processing algorithm.

Any number of programs, not just Application Engine programs, can use temporary table definitions. When you specify a temporary table on the Temp Tables tab in the Application Engine program properties, Application Engine automatically manages the assignment of temporary table instances. When Application Engine manages a dedicated temporary table instance, it controls the locking of the table before use and the unlocking of the table after use.

Parallel Processing

Parallel processing is used when considerable amounts of data must be updated or processed within a limited amount of time or a batch window. In most cases, parallel processing is more efficient in environments containing multiple CPUs and partitioned data.

To use parallel processing, partition the data between multiple concurrent runs of a program, each with its own dedicated version of a temporary table (for example, PS_MYAPPLTMP). If you have a payroll batch process, you could divide the employee data by last name. For example, employees with last names beginning with A through M are inserted into PS_MYAPPLTMP1; employees with last names beginning with N through Z are inserted into PS_MYAPPLTMP2.

To use two instances of a temporary table, you would define your program (for example, MYAPPL) to access one of two dedicated temporary tables. One run would use A through M and the other N through Z.

The Application Engine program invokes logic to pick one of the available instances. After each program instance is matched with an available temporary table instance, the %Table meta-SQL construct uses the corresponding temporary table instance. Run control parameters passed to each instance of the MYAPPL program enable it to identify which input rows belong to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table. The following diagram illustrates this process:

Image: Multiple program instances running against multiple temporary table instances

This is a diagram of Multiple program instances running against multiple temporary table instances.

Multiple program instances running against multiple temporary table instances

No simple switch or check box enables you to turn parallel processing on and off. To implement parallel processing, you must complete the following task. With each step, you must consider details about your specific implementation.

  1. Define and save temporary table records in Application Designer.

    You do not need to run the SQL Build process at this point.

  2. In Application Engine, assign temporary tables to Application Engine programs, and set the instance counts dedicated for each program.

    Employ the %Table meta-SQL construct so that Application Engine can resolve table references to the assigned temporary table instance dynamically at runtime.

  3. Set the number of total and online temporary table instances on the PeopleTools Options page.

  4. Build temporary table records in Application Designer by running the SQL Build process.