Optimize a transaction query for performance improvements

You can use temporary tables instead of database-specific views to improve your performance.

In HCL® Contact Optimization, you can add business rules on offer version attributes to control and manage investments and resources that are allocated for offer versions. During a Contact Optimization session, for offer version rules, HCL Contact Optimization creates a query to get a list of transactions that are in scope of the offer version conditions defined in the rules by marketers. This query is called Attribute Filter Query (AFQ).

The AFQ query uses PAV and APA database views. These views are created, used, and deleted during a Contact Optimization session run. The query mentioned in the PAV and APA views runs whenever the AFQ query runs. The result of these view queries is held in memory for the AFQ query, and if the result of these queries is millions of records, it is difficult for the database to keep this result in memory. This might result in an Out-Of-Memory error on the database server.

If there is at least one rule that has an Over The Period value greater than zero, HCL Contact Optimization creates an HAV view.

To optimize the AFQ and improve your performance, you can enable the use of temporary tables. You can enable or disable the temporary table by going to Advanced settings > Database tuning and setting Allow temporary tables to True. You can also configure this parameter in the Configuration settings at Affinium|Campaign|partitions|partition1|Optimize|DatabaseTuning| AllowTempTables. By default, this setting is enabled because there is an improvement in the performance of larger sessions when you use temporary tables.