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 ONE Support launchpad (Login required).

Search for additional results

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