IBM Support

JR50052: Slow database query when displaying facets

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • With a large number of attributes and a very large number of
    attribute values for each attribute, the query with label
     SELECT_FACET_VALUE_IMAGE_AND_SEQUENCE performs slowly:
    
     BEGIN_SQL_STATEMENT
      name=SELECT_FACET_VALUE_IMAGE_AND_SEQUENCE
      base_table=ATTRDICTSRCHCONF
      sql=
      select distinct
    attrdictsrchconf.SRCHFIELDNAME,attrvaldesc.value,
     attrval.storeent_id,attrvaldesc.sequence,attrvaldesc.image1,att
    rvaldesc.
     image2
      from
        attrdictsrchconf, attr, attrvaldesc, attrval
      where
        attrdictsrchconf.attr_id=attr.attr_id and
        attr.attr_id=attrvaldesc.attr_id and
        attrval.attr_id = attr.attr_id and
        attrdictsrchconf.ATTR_ID is not NULL and
        attr.facetable=1 and
        attrval.storeent_id in (?storeList?) and
        attrvaldesc.language_id=?language_id? and
        attrdictsrchconf.SRCHFIELDNAME in (?searchFieldList?)
     END_SQL_STATEMENT
    

Local fix

Problem summary

  •  USERS AFFECTED:
     WebSphere Commerce v7.0 users on FEP5/FEP6/FEP7 that have large
    ATTRVAL and ATTRVALDESC tables
    
     PROBLEM ABSTRACT:
     Slow database query when displaying facets
    
     BUSINESS IMPACT:
     Performance impact on store pages
    
     RECOMMENDATION:
    

Problem conclusion

  • The query has been corrected to provide better performance.
    
     JR49929 is a pre-requisite and needs to be applied along with
    this iFix.
    
     -------------------------------------------------------------
     The latest available maintenance information can be obtained
    from the Recommended Fixes for WebSphere Commerce technote:
     http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR50052

  • Reported component name

    WC BUS DEV ED W

  • Reported component ID

    5724I3900

  • Reported release

    700

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-04-23

  • Closed date

    2014-09-30

  • Last modified date

    2015-05-15

  • 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 DEV ED W

  • Fixed component ID

    5724I3900

Applicable component levels

  • R700 PSY

       UP

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSYT2H","label":"WebSphere Commerce Developer Enterprise"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Edition":"","Line of Business":{"code":null,"label":null}}]

Document Information

Modified date:
15 May 2015