Increase Performance on Product Cockpit

hybris: Increase Performance on Product Cockpit

 

The issue

On many hybris installations with a big amount of products, variants and more you will recognize a long login time on the product cockpit and also when you switch catalogs in product cockpit.

 

Two common and cheap solutions are to

  • Disable the initial search on login by setting disabledInitialSearch to true
  • Disable the auto search on selecting a catalog by setting disabledAutoSearch to true

 

This is explained in detail in SAP KBA article 2435094.

 

On a hybris installation of a customer we had after applying the config changes still a bad user experience because after selecting a catalog the searching was still slow. After a deeper investigation of this issue it turned out that the bad performance was caused by the SQL query executed on the DB and especially by the “ORDER BY” clause of the query. The order is given by the (first listed) sort-property for i.e. products as specified in “Base_Product.xml” cockpit UI component configuration. (Removing all sort-properties will give an XML violation and a sort-property “creationtime” or “PK” as fallback.)

 

By default the query is built like i.e.

 

SELECT {item:PK} FROM {Product AS item } WHERE  ( NOT EXISTS ({{ SELECT {CollElem:PK} FROM {ObjectCollectionItemReference AS CollElem } WHERE  ({CollElem:item} = {item:pk} AND {CollElem:collection} = ?gs.param.1 ) }}) AND  ({item:catalogVersion} IN (?gs.param.3) OR  EXISTS ({{ SELECT {CategoryProductRelation:target} FROM {CategoryProductRelation AS CategoryProductRelation  JOIN Category AS cat  ON {CategoryProductRelation:source} = {cat:PK} } WHERE  ({cat:catalogVersion} IN (?gs.param.4) AND {CategoryProductRelation:target} = {item:PK} ) }}) ) AND {item:itemtype} IN (?gs.param.5,?gs.param.6,?gs.param.7,?gs.param.8,?gs.param.9,?gs.param.10, ?gs.param.11,?gs.param.12,?gs.param.13,?gs.param.14,?gs.param.15)) ORDER BY {item:modifiedtime} DESC

 

(Using “JOIN” together with “ORDER BY” will mostly lead to the creation of temporary tables which should be avoided due to bad performance.)

 

The customer wanted the sorting on DB level switched off. In SAP KBA we found 2107507 and 2105270. But we created an alternate (and more lightweight) solution.

 

The solution

To actually remove the “ORDER BY” we must overwrite one method in the SearchBrowser class and one method in the QueryProvider class.

 

In the “SearchBrowser.doSearchInternal()” the query is created. The solution here is to call “searchQuery.addSortCriterion()” only if the selected type is not “Product” and the sort property is not “Item.creationtime“. When the query is created it will be executed by calling “searchProvider.search(searchQuery)“. In “SearchProvider” the query/order will be checked by “genQuery.getOrderByList()“: If it’s empty then a default “ORDER BY” of “PK” will be added. This code must be removed.

 

To make it work we must be sure that the first sort-property for type Product is actually “creationTime“. For that we created a matching “Base_Product.xml“.

 

This simple approach will lead to all Product searches sorted by “Creation Time” to be actually not sorted. Additional conditions to restrict the usage of the limited query may be added depending on the requirements of the customer.

 

(To actually show in product cockpit that the sorting is turned off a special translation for creation time was included.)

 

The steps to implement

In your cockpit extension:

  1. Add the Java class CustomerProductCockpitProductSearchBrowserModel.java
  2. Add the Java class CustomerProductCockpitProductPerspectiveQueryProvider.java
  3. Extend the correct web spring file as shown in CustomerProductCockpit-web-spring.xml
  4. Extend the correct service spring file as shown in CustomerProductCockpit-spring-services.xml
  5. Set sort properties as shown in Base_Product.xml
  6. Add a translation like i.e. shown in attached CustomerProductCockpit-locales_de.properties

 

The source

CustomerProductCockpitProductSearchBrowserModel.java

package de.customer.cockpit.session.impl;

import de.hybris.platform.catalog.model.CatalogVersionModel;
import de.hybris.platform.cockpit.model.meta.ObjectTemplate;
import de.hybris.platform.cockpit.model.meta.PropertyDescriptor;
import de.hybris.platform.cockpit.model.search.ExtendedSearchResult;
import de.hybris.platform.cockpit.model.search.Query;
import de.hybris.platform.cockpit.model.search.SearchType;
import de.hybris.platform.cockpit.services.search.SearchProvider;
import de.hybris.platform.productcockpit.session.impl.DefaultProductSearchBrowserModel;

import java.util.Collection;
import java.util.Collections;
import java.util.Map;

import org.apache.log4j.Logger;


public class CustomerProductCockpitProductSearchBrowserModel extends DefaultProductSearchBrowserModel
{

  private static final Logger LOG = Logger.getLogger(CustomerProductCockpitProductSearchBrowserModel.class);

  @Override
  protected ExtendedSearchResult doSearchInternal(final Query query)
  {
    if (query == null)
    {
      throw new IllegalArgumentException("Query can not be null.");
    }

    ExtendedSearchResult result = null;

    final SearchProvider searchProvider = getSearchProvider();
    if (searchProvider != null)
    {
      Query searchQuery = null;

      final int pageSize = query.getCount() > 0 ? query.getCount() : getPageSize();

      SearchType selectedType = null;
      if (query.getSelectedTypes().size() == 1)
      {
        selectedType = query.getSelectedTypes().iterator().next();
      }
      else if (!query.getSelectedTypes().isEmpty())
      {
        selectedType = query.getSelectedTypes().iterator().next();
        LOG.warn("Query has ambigious search types. Using '" + selectedType.getCode() + "' for searching.");
      }

      if (selectedType == null)
      {
        selectedType = getSearchType();
      }

      searchQuery = new Query(Collections.singletonList(selectedType), query.getSimpleText(), query.getStart(), pageSize);
      searchQuery.setNeedTotalCount(!isSimplePaging());
      searchQuery.setParameterValues(query.getParameterValues());
      searchQuery.setParameterOrValues(query.getParameterOrValues());
      searchQuery.setExcludeSubTypes(query.isExcludeSubTypes());

      final ObjectTemplate selTemplate = (ObjectTemplate) query.getContextParameter("objectTemplate");
      if (selTemplate != null)
      {
        searchQuery.setContextParameter("objectTemplate", selTemplate);
      }


      if (query.getContextParameter("selectedCatalogVersions") != null)
      {
        try
        {
          setSelectedCatalogVersions((Collection) query.getContextParameter("selectedCatalogVersions"));
        }
        catch (final Exception e)
        {
          LOG.warn("Could not select catalog versions", e);
        }
      }
      if (query.getContextParameter("selectedCategories") != null)
      {
        try
        {
          setSelectedCategories((Collection) query.getContextParameter("selectedCategories"));
        }
        catch (final Exception e)
        {
          LOG.warn("Could not set selected categories", e);
        }
      }


      Collection<CatalogVersionModel> catver = getSelectedCatalogVersions();
      if ((catver.isEmpty()) && (getSelectedCategories().isEmpty()))
      {
        catver = getProductCockpitCatalogService().getAvailableCatalogVersions();
      }
      searchQuery.setContextParameter("selectedCatalogVersions", catver);
      if (!getSelectedCategories().isEmpty())
      {
        searchQuery.setContextParameter("selectedCategories", getSelectedCategories());
      }



      final Map<PropertyDescriptor, Boolean> sortCriterion = getSortCriterion(query);

      PropertyDescriptor sortProp = null;
      boolean asc = false;

      if ((sortCriterion != null) && (!sortCriterion.isEmpty()))
      {
        sortProp = sortCriterion.keySet().iterator().next();
        if (sortProp == null)
        {
          LOG.warn("Could not add sort criterion (Reason: Specified sort property is null).");
        }
        else
        {
          /**
           * Check for selected type "Product" and only "creationtime" as sort property. As this is expected to be
           * the initial search we remove the sortCriterion to avoid SQL "order by" clause and speed up initial
           * search in DB.
           *
           * To get "creationtime" as only sort property we remove all sort properties for Product. After that we
           * get creationtime as fallback. To set the sort property we use a cockpit UI config for "Base" and
           * "Product". Example Base_Product.xml:
           *
           * <pre>
           *
           * <?xml version="1.0" encoding="UTF-8"?>
           * <base>
           *     <search>
           *         <search-properties>
           *             <property qualifier="Product.code" />
           *             <property qualifier="Product.name" />
           *         </search-properties>
           *         <sort-properties>
           *             <property qualifier="Product.creationTime"/>
           *         </sort-properties>
           *     </search>
           *     <label>
           *         <property qualifier="Product.name" />
           *     </label>
           * </base>
           *
           * </pre>
           *
           * Additionally we must remove...
           *
           * <pre>
           * genQuery.addOrderBy(new GenericSearchOrderBy(new GenericSearchField("item", "pk"), true));
           * </pre>
           *
           * ... from SearchProvider.search().
           */
          if ((selectedType.getCode().compareTo("Product") == 0)
              && (sortProp.getQualifier().compareTo("Item.creationtime") == 0))
          {
            LOG.info("Disabled sorting for 'Product' and 'Item.creationtime' to remove 'ORDER BY'.");
            //sortProp = null; // optionally, for advanced search
          }
          else
          {
            if (sortCriterion.get(sortProp) != null)
            {
              asc = sortCriterion.get(sortProp).booleanValue();
            }
            searchQuery.addSortCriterion(sortProp, asc);
          }
        }
      }

      updateAdvancedSearchModel(searchQuery, sortProp, asc);

      try
      {
        final Query clonedQuery = (Query) searchQuery.clone();
        setLastQuery(clonedQuery);
      }
      catch (final CloneNotSupportedException localCloneNotSupportedException)
      {
        LOG.error("Cloning the query is not supported");
      }

      if (getBrowserFilter() != null)
      {
        getBrowserFilter().filterQuery(searchQuery);
      }

      result = searchProvider.search(searchQuery);

      updateLabels();
    }

    return result;
  }
}

 

CustomerProductCockpitProductPerspectiveQueryProvider.java

package de.customer.cockpit.session.impl;

import de.hybris.platform.cockpit.model.search.ExtendedSearchResult;
import de.hybris.platform.cockpit.model.search.Query;
import de.hybris.platform.cockpit.model.search.SearchType;
import de.hybris.platform.cockpit.model.search.impl.DefaultExtendedSearchResult;
import de.hybris.platform.cockpit.services.search.impl.GenericSearchParameterDescriptor;
import de.hybris.platform.core.GenericCondition;
import de.hybris.platform.core.GenericQuery;
import de.hybris.platform.core.GenericSearchField;
import de.hybris.platform.core.GenericSearchOrderBy;
import de.hybris.platform.core.model.type.ComposedTypeModel;
import de.hybris.platform.productcockpit.services.search.impl.ProductPerspectiveQueryProvider;

import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;


public class CustomerProductCockpitProductPerspectiveQueryProvider extends ProductPerspectiveQueryProvider
{

  @Override
  public ExtendedSearchResult search(final Query query)
  {
    final SearchType oldRootType = getDefaultRootType();

    final Set<SearchType> searchTypes = query.getSelectedTypes();
    if ((searchTypes != null) && (searchTypes.size() == 1))
    {
      setDefaultRootType(searchTypes.iterator().next());
    }

    final List<GenericCondition> conditions = new ArrayList<GenericCondition>();
    final Set<GenericSearchParameterDescriptor> activeParameters = new LinkedHashSet<GenericSearchParameterDescriptor>();

    final ComposedTypeModel rootTypeModel = extractRootTypeModel(activeParameters);
    final Set<ComposedTypeModel> permittedTypeModels = rootTypeModel == null ? Collections.EMPTY_SET
        : getPermittedTypes(rootTypeModel, query.getSelectedTypes(), query.isExcludeSubTypes());
    final ExtendedSearchResult ret;
    if ((rootTypeModel == null) || (permittedTypeModels.isEmpty()))
    {
      ret = new DefaultExtendedSearchResult(query, Collections.EMPTY_LIST, 0);
    }
    else
    {
      final GenericQuery genQuery = new GenericQuery(rootTypeModel.getCode());
      genQuery.setInitialTypeAlias("item");

      conditions.addAll(createConditions(query, genQuery));

      final GenericSearchOrderBy orderBy = createOrderBy(query);
      if (orderBy != null)
      {
        genQuery.addOrderBy(orderBy);
      }

      //			final Collection<GenericSearchOrderBy> orderByList = genQuery.getOrderByList();
      //			if (orderByList.isEmpty())
      //			{
      //				genQuery.addOrderBy(new GenericSearchOrderBy(new GenericSearchField("item", "pk"), true));
      //			}

      conditions.add(GenericCondition.createConditionForValueComparison(new GenericSearchField("itemtype"),
          de.hybris.platform.core.Operator.IN, permittedTypeModels));

      genQuery.addCondition(GenericCondition.and(conditions));

      beforeSearch();
      try
      {
        ret = performQuery(query, genQuery);
      }
      finally
      {
        afterSearch();
      }
    }

    setDefaultRootType(oldRootType);
    return ret;
  }
}

CustomerProductCockpit-web-spring.xml

<!-- add at least alias and bean and defaultBrowserClass CustomerProductCockpitProductSearchBrowserModel to your web-spring: -->
  <alias name="CustomerProductCockpitProductPerspective" alias="ProductPerspective" />
  <bean id="CustomerProductCockpitProductPerspective" scope="session" parent="defaultProductPerspective">
    <property name="browserArea">
      <bean class="de.hybris.platform.productcockpit.session.impl.BrowserArea">
        <property name="defaultBrowserClass" value="de.customer.cockpit.session.impl.CustomerProductCockpitProductSearchBrowserModel" />
        <property name="viewURI" value="/productcockpit/productBrowser.zul" />
        <property name="multiSelectActions">
          <ref bean="ContentBrowserMultiSelectActionColumn" />
        </property>
        <property name="multiSelectContextActionsRegistry">
          <ref bean="ContextAreaActionColumnConfigurationRegistry" />
        </property>
        <property name="additionalToolbarActions">
          <ref bean="ProductBrowserAdditionalToolbarActionColumn" />
        </property>
        <property name="defaultBrowserViewMapping">
          <map>
            <entry key="cockpitgroup" value="GRID" />
            <entry key="productmanagergroup" value="GRID" />
          </map>
        </property>
        <property name="inspectorRenderer" ref="coverageInspectorRenderer" />
        <property name="openInspectorOnSelect" value="true" />
      </bean>
    </property>
  </bean>

 CustomerProductCockpit-spring-services.xml

<!-- add this to spring-services: -->
  <alias alias="productSearchProvider" name="CustomerProductCockpitProductPerspectiveQueryProvider" />
  <bean id="CustomerProductCockpitProductPerspectiveQueryProvider" class="de.customer.cockpit.session.impl.CustomerProductCockpitProductPerspectiveQueryProvider"
    scope="tenant">
    <property name="maxCategoryCount" value="900" />
  </bean>

 Base_Product.xml

<?xml version="1.0" encoding="UTF-8"?>
<base>
  <search>
    <search-properties>
      <property qualifier="Product.code"/>
      <property qualifier="Product.name"/>
      <property qualifier="Product.description"/>
    </search-properties>
    <sort-properties>
      <property qualifier="Product.creationTime"/> <!-- actually Item.creationtime will be used for this on setting up SQL Query but the translation will give "no sorting" when displaying in cockpit -->
      <property qualifier="Item.modifiedtime"/>
      <property qualifier="Product.code"/>
      <property qualifier="Product.name"/>
      <property qualifier="Product.description"/>
      <property qualifier="Item.pk"/>
    </sort-properties>
  </search>
  <label>
    <property qualifier="Product.name" />
  </label>
</base>

CustomerProductCockpit-locales_de.properties

type.item.creationtime.name=Erstellungszeit

# This is actually some kind of hack to display that there is no sorting for this type in product cockpit (see Base_Product.xml). The sort-property Product.creationtime will be taken as Item.creationtime and SearchBrowser/QueryProvider will remove this sorting ("ORDER BY")

type.product.creationtime.name=Erstellungszeit (no sorting)

 

 

Wir verwenden auf unserer Webseite Cookies. Diese helfen uns bei der Bereitstellung unserer Dienste. Durch das Anklicken des „OK-Buttons“ erklären Sie sich damit einverstanden, dass wir Cookies setzen. Mehr erfahren

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close