Backoffice search performance improvement

[MausamSinha]

SAP Commerce Backoffice search depends extensively on how the SQL queries are constructed. This article aims to provide tweaks such that the queries formed execute fast and do not consume high DTU.

This is especially useful when you have tables with large number of records and the search is slow on them

A typical simple search query looks like this.

SELECT item_t0.PK FROM users item_t0 WHERE (( (LOWER( item_t0.p_uid ) LIKE ? OR LOWER( item_t0.p_name ) LIKE ? ) )) AND (item_t0.TypePkString=? ) order by item_t0.p_name DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

You can capture your queries by enabling JDBC Logs

The queries are with the keywords

1. LOWER (attribute_name)


2. LIKE '%searchterm%'

This typically works fine for small tables but when the row count is high and these keywords are used, database index do not get applied and it leads to higher load on the DB.

 

The behaviour is similar with Backoffice advanced search. Advanced search comes with an additional option of setting the desired Comparator.

 

Tip #1 Read replica database

SAP Commerce Cloud in the Public Cloud (also known as CCv2) version 1905 onward comes with a Read Replica database in addition to the main database in Production environment. Typically, Backoffice flexible search queries like above are directed towards Read Replica database.

You can validate if you have a working Read Replica database in Production or not by checking the properties given here.

 

Tip #2 Set case sensitive to TRUE

Let me clarify in the beginning, this will not make your result case sensitive in CCv2 environment. The result set will remain the same as what you have without these.

Set below properties in local.properties file

cockpit.search.simplesearch.casesensitive=true

cockpit.search.advancedsearch.casesensitive=true

With this, the query changes to:

SELECT item_t0.PK FROM users item_t0 WHERE (( ( item_t0.p_uid LIKE ? OR item_t0.p_name LIKE ? ) )) AND (item_t0.TypePkString=? ) order by item_t0.p_name DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

Notice that LOWER keywords are removed.

Please note that this is a global setting and applied to simple search and advanced search on all item types.

As mentioned earlier, this will only change the query, but both the queries will provide the same result set. This is due to the fact that Azure SQL runs with collation SQL_Latin1_General_CP1_CI_AS in CCv2 and the queries are case insensitive in nature by default.

You can check Azure SQL collation for your database using this query:

SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

 

Tip #3 Use Computed columns if collation is case sensitive

If the collation is case sensitive, you can ask your DBA to add a compute column following the guidelines here

For example:

ALTER TABLE users ADD lower_uid AS LOWER(p_uid)

Also, make sure, the required indexes are created on this new column lower_uid.

CREATE UNIQUE INDEX users_loweruid_idx ON users (lower_uid);

Now SQL Server uses the index on the computed column even if you still use LOWER(p_uid) function in the query, and not only lower_uid column.

 

Tip #4 Restrict simple search to a single column

Create a new backoffice extension. If you already have one, you can use that too.

Inside <extensionname>-backoffice-config.xml file, add below line

<config xmlns="http://www.hybris.com/cockpit/config"

        xmlns:ss="http://www.hybris.com/cockpitng/config/simplesearch">

    <context merge-by="type" parent="GenericItem" type="User" component="simple-search">

        <ss:simple-search>

            <ss:field name="uid"/>

        </ss:simple-search>

    </context>

<config>

With this, simple search would get configured to use only uid attribute of item type user for performing search.

Notice that this has be set at an item type level.

 

Tip#5 Use STARTSWITH instead of CONTAINS

Set the below property in local.properties file

backoffice.flexible.search.comparison.operator.startswith.types=User

With this, the simple search query changes from

SELECT  item_t0.PK  FROM users item_t0 WHERE (( ( item_t0.p_uid  LIKE '%searchterm%' ) )) AND (item_t0.TypePkString IN  (8796097216594,8796097052754) ) LIMIT 50 OFFSET 0|

to

SELECT  item_t0.PK  FROM users item_t0 WHERE (( ( item_t0.p_uid  LIKE 'searchterm%' ) )) AND (item_t0.TypePkString IN  (8796097216594,8796097052754) ) LIMIT 50 OFFSET 0|

Notice the missing first % in LIKE. This helps in enabling index based search.

With this change, the default contains behaviour will change to startswith. As this changes the default behaviour, you may need an acceptance from your users.

Please note that this has to be set at an item type level. A comma separated list of item types can be set like this

backoffice.flexible.search.comparison.operator.startswith.types=User,Product,Catalog

 

Tip #6 Ensure that right indexes are present.

1. Navigate to hac -> Azure SQL -> Schema browser


2. Search for a table name and click Details.


3. Validate the available indexes.

Are the right set of indexes available? If not, you can create them following this help document.

Once the above changes are done, use hac -> Azure SQL ->  Execution Plan tab to validate if database index is getting applied on these search or not.

Without above optimisations:

In this case, the query is doing an Index scan which is relatively slower.

With above optimisations:

Notice that the query is now doing an Index Seek which is much faster than the earlier Index scan, as it now uses the underlying index to point to the record.

Conclusion

With minor properties changes, SAP Commerce Backoffice Search starts using the underlying indexes and the Backoffice search performance improves significantly. This also helps bring down the DTU utilisation.