ETL Tuning

When you configure the contact and response history module, the module uses a background Extract, Transform, Load (ETL) process to move data from the runtime staging tables to the Unica Campaign contact and response history tables.

This section describes the optional configuration settings you might want to change in Unica Interact to improve the performance of the ETL tool. You might not need to modify any of these configuration parameters from their default settings; however, if you do, follow the guidelines here and in the Unica Interact Administrator's Guide to modify the tool's performance.

All of the properties described here are found in Unica Campaign configuration properties, in Campaign | partitions | partition[n] | Interact | contactAndResponseHistTracking.

Configuration Property

Value and Description

processSleepIntervalInMinutes

The number of minutes the Unica Interact contact and response history module waits between copying data from the Unica Interact runtime staging tables to the Unica Campaign contact and response history tables. The default value is 60.

purgeOrphanResponseThresholdInMinutes

This property determines how long Unica Interact waits before purging responses that have no corresponding contact (also known as "orphaned responses"). The default is 180; however, for processing many records, the delay between processing contacts and responses may be greater, and you would therefore increase this value to prevent responses from being purged too quickly.

maxJDBCInsertBatchSize

Out of the total number of records that the contact and response history module processes in one iteration, this is the maximum number of records of a JDBC batch to process (and assemble together in a batch) before committing the query into the Unica Campaign system tables The default value is 1000.

Because this value works together with the maxJDBCFetchBatchSize property, you might need to increase this value if that property also increased significantly. For example, if you set maxJDBCFetchBatchSize to 2,500,000, you might increase this value to 10,000 to handle the increase in records.

Note that memory requirements increase as you increase this value; a setting of 10,000 for this property is a good upper-limit due to memory demands.

maxJDBCFetchBatchSize

Determines the maximum number of records to fetch from the staging database for an ETL batch processing operation. The default value is 1000, but to tune the performance of the contact and response history module, make sure to set this value to a number greater than the number of contact history records generally processed each day.

This property is used together with maxJDBCFetchChunkSize and maxJDBCInsertBatchSize to determine how the records are processed. For example, suppose the values were set as shown here:

  • maxJDBCFetchBatchSize: 30000

  • maxJDBCFetchChunkSize: 1000

  • maxJDBCInsertBatchSize: 1000

In this example, 30,000 records are fetched (or the total number of records if there are under 30,000). Then, the contact and response history module loops through that 30,000 records, processing 1,000 at a time, so that 1,000 records are marked in the staging tables, and 1,000 are inserted into the detail contact history table.

maxJDBCFetchChunkSize

Determines the maximum number of records in a JDBC chunk (from a total of up to maxJDBCFetchBatchSize records) to process with each pass The default value is 1000. In some cases, you might be able to improve performance by increasing this value above the MaxJDBCInsertBatchSize property value.

deleteProcessedRecords

This property, which specifies whether to retain contact and response history records after they are processed, is set to YES by default. Changing this value can give you more control over the data flow within the ETL process and affect performance (by delaying the purging of these records until a later time of your determination); however, you must be knowledgeable to handle the maintenance of these records manually to be sure that they are removed at appropriate times. Use caution when modifying this setting.

fetchSize

Providing a value for the JDBC fetchSize can improve performance for large batches of records, but the tradeoff for improved networking performance is the impact of larger fetch sizes on memory usage. See the description of this configuration property in the Unica Interact Administrator's Guide for more information about adjusting this setting.

For detailed descriptions of each of the configuration properties described here, see the online help for that configuration page, or see the Unica Interact Administrator's Guide.