SAP Knowledge Base Article - Preview

2729633 - Slow performance on reference editor usage when hundreds of thousands of entries exist for a type

Symptom

Using the reference editor to search for an item on the backoffice can take a long time when the type contains a large amount of instances.

As an example, due to large volume of data (e.g. catalog containing 1 million products), creating a promotion rule for specific qualifying products, or selecting a product in "Free gift" action works very slow. Requests for products sometimes take more than 30s to provide any result.

Investigating the JDBC logs shows the following consuming query:

SELECT item_t0.PK FROM products item_t0 LEFT JOIN productslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK AND lp_t0.LANGPK =? WHERE 
(( ( (LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code )
LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) LIKE ? OR LOWER( item_t0.p_code ) 
...
..


Read more...

Environment

Issue was observed on hybris Commerce Suite versions 6.1, 6.2, 6.3, 6.4 and was fixed on 6.5.

Product

SAP Hybris Commerce 6.1 ; SAP Hybris Commerce 6.2 ; SAP Hybris Commerce 6.3 ; SAP Hybris Commerce 6.4

Keywords

  • hybris
  • commerce
  • cockpitng
  • lower
  • database 
, KBA , CEC-COM-CPS , SAP Commerce , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.