Using the TempTable and OutputTempTable tokens in raw SQL queries

Temporary tables provide a workspace for intermediate results when processing or passing data. When the operation is finished, temp tables are discarded automatically.

For best performance, use the <TempTable> token in raw SQL queries, especially when querying large tables.

If you are using in-database optimization and you specify a raw SQL query in a Select process with an input cell, you must use the <TempTable> token to ensure correct behavior. See below for a full explanation.

If you are using in-database optimization, also use the <OutputTempTable> token to prevent audience IDs from being unnecessarily copied from the database to the Campaign server.

When you use a raw SQL query in a Select process with an input cell, the processing behavior depends on whether you are using in-database optimization. (In-database optimization is controlled globally with the Use In-DB Optimization configuration setting. It is controlled for individual flowcharts with the useInDbOptimization during Flowchart Run option on the Admin menu.)

When in-database optimization is off: The list of IDs from the raw SQL query is automatically matched against the ID list from the incoming cell. The resulting list of IDs is a subset of the cell, as expected.

When in-database optimization is on: Campaign assumes that the ID list generated from the Select process is the final list. Campaign does not match this list against the ID list of any incoming cell. Therefore, the raw SQL query written for an intermediate Select process (a Select process with an input cell) must use the <TempTable> token to properly join against the incoming cell. Joining against the input cell ensures correct results and improves performance by preventing extraneous processing for audience IDs that are not in the input cell.

In-database optimization is explained in the IBM® Campaign Administrator's Guide.

Example: Using the TempTable and OutputTempTable tokens