Details about in-database optimization

In-database optimization avoids copying IDs from the database to the IBM® Campaign server for processing whenever possible. This option can improve flowchart performance.

In-database optimization determines:

Whether operations are done on the database server or the local IBM® Campaign server; and

Where the results of operations are stored.

When in-database optimization is on:

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

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

In-database optimization affects CPU consumption:

When in-database optimization is on, more CPU is consumed on the database server.

When in-database optimization is off, more CPU is consumed on the IBM® Campaign server.

You can apply in-database optimization globally and override the global setting for individual flowcharts. The best practice is to turn off the global configuration property (useInDbOptimization) and set the option at the flowchart level (Advanced Settings > Admin > Use In-DB Optimization during Flowchart Run).

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

Limitations of in-database optimization

In-database optimization is not supported for all databases.

Depending on the logic that is required, some functions are still performed on the IBM® Campaign server, even with in-database processing turned on. Some examples are given below:

The query uses tables from different data sources.

For example, if a Select process queries different data sources, IBM® Campaign automatically stores the ID lists for those cases on the application server.

The query contains non-SQL macros or derived fields.

For example, to calculate a derived field, IBM® Campaign evaluates the derived field formula to see whether any part of the calculation can be performed with SQL. If simple SQL statements can be used, the calculation is done in-database. If not, temporary tables are created on the IBM® Campaign server to handle the calculations and persist the results from process to process within a flowchart.

Processing raw SQL in macros

Custom macros that consist of raw SQL statements can be processed in-database, within the following guidelines:

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

For databases that only support 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 that are selected by the raw SQL custom macro are too large for the fields of the temp table, a runtime error occurs.

If you use a raw SQL query in a Select process that has an input cell, you must use the <TempTable> token to obtain the correct list of audience IDs. Also use the <OutputTempTable> token to prevent audience IDs from being retrieved from the database back to the IBM® Campaign server.

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 are not scoped by the results from the upstream process.