Configure PromotionArgument database records to reduce locking conflicts

We can configure the site to use PromotionArgument records from multiple database tables to reduce database table locking issues, and improve overall storefront performance.

Before starting

Ensure that the following conditions exist, otherwise do not perform this task:

• No custom code exists that relies on records in the PX_PROMOARG database table.

• The VoucherAdjustment promotion adjustment is not being used. This adjustment offers a coupon to a customer when a promotion is applied and an order submitted. By default, this adjustment is not used. For the site to use this adjustment, customization is required.

Task info

When a promotion is evaluated, the results of the evaluation, called PromotionArguments, are persisted in the PX_PROMOARG database table. These records are used whenever an order is placed to perform the following actions:

• Update promotion usage statistics in the PX_USAGE database table.

• Mark redeemed promotion codes and coupons.

• Release unused promotion codes and coupons.

• Remove obsolete promotion reward choice records.

In sites with a high volume of traffic, locking can occur on the PX_PROMOARG table. If this locking occurs when shoppers are going through the checkout flow, a performance degradation can result. If there is no customization that uses the data in the PX_PROMOARG table, we can configure your Promotion Engine so the PX_PROMOARG records do not persist to the database. If you complete this configuration, when an order is placed, the PromotionArgument actions are performed by looking at records in the ORDADJUST, CLCDPROMO, PX_CDUSAGE,
PX_CDPROMO, PX_COUPON, PX_PROMOTION tables instead of the PX_PROMOARG table. This configuration can result in an overall storefront performance improvement.

Procedure

1. Open a connection to the database, and run the following SQL insert statement:

   INSERT INTO CMDREG (STOREENT_ID, INTERFACENAME, CLASSNAME) 
   VALUES (0,'com.ibm.commerce.order.calculation.FinalizeDiscountCalculationUsageCmd', 
   'com.ibm.commerce.order.calculation.FinalizeDiscountCalculationUsageNoPromoargCmdImpl'); 

   Note: The SQL statement is separated into different lines for display purposes. You might need to format the SQL statement into a single line to run successfully.

2. Change the implementation class for PromotionArgumentPersistenceManager: Modify the file

   1. Go to your workspace_dir/WC/xml/PromotionEngineConfiguration directory.

   2. Open the WCSPromotionEngineConfig.xml for editing.

   3. Locate the implementation class for the PromotionArgumentPersistenceManager. Change the implementation class to be:

      com.ibm.commerce.marketing.promotion.runtime.PromotionArgumentNoPromoargSessionBeanPersistenceManager

      Ensure that your update to the PromotionArgumentPersistenceManager implementation class resembles:

      <PromotionArgumentPersistenceManager 
          impl="com.ibm.commerce.marketing.promotion.runtime.PromotionArgumentNoPromoargSessionBeanPersistenceManager" />

3. Restart the server.

Related reference
Promotion performance tuning