Improving flowchart performance using in-database optimization

You can improve flowchart performance by turning in-database optimization on globally or for an individual flowchart. Best practice is to turn off the global setting, and set the option at the flowchart level. The flowchart-level option overrides the global setting.

In-database optimization is not supported for all databases. Your Campaign administrator can confirm whether this option is available for your data sources.

The in-database optimization option determines:

what operations are done at the database level or the local Campaign server level; and

what happens to the results of operations.

When In-DB optimization is on:

In-database optimization prevents IDs from your database from being unnecessarily copied to the Campaign server for processing.

Processing tasks such as sorting, joining, and merging of data are done on the database server whenever possible.

Output cells of processes are stored in temporary tables on the database server.

Some functions are still performed on the Campaign server, when necessary. For example, to calculate a derived field, Campaign first evaluates the derived field formula to see if any part of the calculation can be performed using SQL. If simple SQL statements can be used to perform the calculation, then the calculation is done "in-database". If not, then temporary tables are created on the Campaign server to handle the calculations and persist the results from process to process within a flowchart.

In-database processing cannot be done if you specify any limitations on the output cell size or if temporary tables are disabled for the process.

Custom macros consisting of raw SQL statements can be processed in-database, with the following limitations:

All raw SQL custom macros must begin with select and contain exactly one from in the rest of the text.

For databases that support only insert into <TempTable> syntax, you must map at least one base table to the same data source at the same audience level as the raw SQL custom macro. If the fields selected by the raw SQL custom macro are too large for the fields of the temp table, a runtime error will occur.

If you use raw SQL with in-database optimization, you must code the raw SQL to join with the temp table from the upstream process. Otherwise, the results will not be scoped by the results from the upstream process.

To improve flowchart performance using in-database optimization