JR57259: The COUNT ATTRIBUTE query takes a long time to complete

APAR status

• Closed as program error.

Error description

• The query SELECT_ATTRIBUTE_COUNT in wc-query-utilities.tpl
  causes performance delays. The query is called to determine
  whether the ATTRIBUTE table is empty by completing a full table
  scan:
  
  SELECT COUNT(*) AS COUNT FROM ATTRIBUTE;
  
  When there are millions of records in the ATTRIBUTE table, this
  query causes performance issues.
  

Local fix

• Either remove those promotions that use classical attributes or
  reduce the number of entries in the ATTRIBUTE table by manually
  deleting entries from the database table.
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce Version 7 or Version 8.
  
  PROBLEM ABSTRACT:
  The COUNT ATTRIBUTE query takes a long time to complete
  
  BUSINESS IMPACT:
  A small number of shoppers might experience delays when
  promotions are evaluated against an order.
  
  RECOMMENDATION:
  

Problem conclusion

• A new query was introduced to determine whether the ATTRIBUTE
  table is empty. The new query selects the first record in the
  table, instead of the entire table:
  
  DB2:
  select count(*) as COUNT from (select 1 from ATTRIBUTE fetch
  first 1 rows only) as subquery
  
  Oracle:
  select count(*) as COUNT from (select 1 from ATTRIBUTE where
  ROWNUM=1) as subquery
  
  This query returns an empty result set if the ATTRIBUTE table is
   empty. It will return "1" if the table has at least 1 record.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WC BUS EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP