When you run the GEO_PROD_AGG or GEO_PROD_AGG_DAILY tasks for %DI_JOB_RO as part of your batch processing for SAS^® Regular Price Optimization, you might have poor performance during the di_dm_promo_price_fact_extract portion of the job.

To determine if this issue is causing the job to run slowly, look in the DDMMMYYYY_HH_MM_SS__mp.log..di_etl_geo_prod_agg.#.log unit logs and search for the statement "NOTE: The data set WORK.EXP_PROMO_PRICE_FACT has" and look at lines that follow that provide information about the resources used by the data step. An example of poor performance follows:

NOTE: There were 22089668 observations read from the data set WORK.GP_FILTER.
NOTE: The data set WORK.EXP_PROMO_PRICE_FACT has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           5:28:12.67
      user cpu time       55:42.30
      system cpu time     1:59:38.48

The data step is trying to find a match for the 22+ million records in the WORK.GP_FILTER table using an index search across many PARTITION_nnnnn tables. The fact that there are no matches is immaterial. The step takes 5.5 hours to run and uses about 3 hours of CPU time of which 2 hours is system CPU time most which is spent processing indexes. There is also 2.5 hours of I/O wait time.

There is no work-around for this issue.

Click the Hot Fix tab in this note to access the hot fix for this issue.

The hot fix introduces logic to compute the ratio of the number of records in the WORK.GP_FILTER table to the number of records in the PLIB.PARTITION_nnnn tables. If that ratio is below a certain threshold, the data step will retrieve the data using an index lookup. If that ratio is above the threshold, then the data step will use MERGE logic and a BY statement.

Once the hot fix has been applied, a new global setting will be available for you to specify the ratio value that works best in your environment. The global setting is PROMO_PRICE_FACT_EXTRACT_RATIO and if it is not specified then a default value of 0.1 will be.

The output in the unit log shows the value of PROMO_PRICE_FACT_EXTRACT_RATIO, the number of records in the WORK.GP_FILTER table, the number of records in the PLIB.PARTITION_nnnnn tables and the computed ratio. The log also shows which method, an index lookup or a MERGE will be used. Both methods create the same output table.

Operating System and Release Information

A fix for this issue for SAS Merchandise Intelligence 4.2_M2 is available at: