Using the TempTable and OutputTempTable tokens in raw SQL queries

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, the <TempTable> token is required to ensure correct behavior. See below for a full explanation.

If you are using in-database optimization, also use the <OutputTempTable> token to maintain in-database optimization and prevent audience IDs from being retrieved from the database back 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 in-database optimization is in use:

When Use In-DB 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 Use In-DB 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.

For important information about using raw SQL with in-database optimization, see Improving flowchart performance using in-database optimization.

Example: Using the TempTable and OutputTempTable tokens