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.

[mansurarisoy]

Very helpful article, thank you! Just a small suggestion, you can give additional information about the properties whether they are effective on runtime (by changing them on HAC) or needs to be changed on local.properties and only affective after server restart.

I found that cockpit.search.simplesearch.casesensitive and  cockpit.search.advancedsearch.casesensitive properties are used in GenericConditionQueryBuilder and they are queried at runtime so it's possible to change these on HAC and see their effects immediately.

I could not find the exact location of following properties, but they seem to work on runtime as well. However, they are working for the exact itemtypes. For example, when you define User with one of the following properties, it does not work with Customer or Employee searchs, you need to explicitly set subtypes.

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

backoffice.flexible.search.comparison.operator.endswith.types

backoffice.flexible.search.comparison.operator.equals.types

[MausamSinha]

Thanks for your feedback Mansur. I will update the article.

That is right, startwith, endswith and equals work at an itemtype level and that is what I mentioned "Please note that this can be set at an item type level." but i will make it a bit more explicit.

[YannickRobin]

Tip #2 is duplicated.

[YannickRobin]

By default, I think we have this search in read only replica.

Maybe you can add as Tip #1 and indicate the SAP Commerce Cloud version where this query has been moved to read replica by default.

[YannickRobin]

In Tip #3, the index will not work because of the lower function if the database is case sensitive.

Maybe, you should add an additional to recommend checking the database sensitivity. If the database is case sensitive, a computed column should be added in addition to the relevant index. See https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-tabl...

[MausamSinha]

Thanks Yannick, i will check this option.

[benjaminbignell]

mausamsinha yannick.robin

Maybe I've misunderstood, but if the collation in the database is case-insensitive then I should be able to search with any case and get the same correct, result?

 

In one of our customer environments I confirmed that the collation is case-insensitive: SQL_Latin1_General_CP1_CI_AS

 

I applied the following setting and did some testing:

cockpit.search.simplesearch.casesensitive=true

None of my searches using mixed case (or the "incorrect" case vs the actual data) returned any results.


I did the queries manually in the HAC as follows, and I get zero results:

select pk from {b2bunit} where {uid} like '%fbcfd%'

 

 

Using uppercase values to match the actual data gives me the expected result:

select pk from {b2bunit} where {uid} like '%FBCFD%'

 

Any idea why this is happening, or where I might have gone wrong?

 

 

[mansurarisoy]

Collation of the column might be case-sensitive. Can you check for collation of the column with the following query:

SELECT c.collation_name

FROM SYS.COLUMNS c

JOIN SYS.TABLES t ON t.object_id = c.object_id

WHERE t.name = 'usergroups' AND c.name = 'p_uid'

[benjaminbignell]

mansurarisoy you're right, it's the column collation:

SQL_Latin1_General_CP1_CS_AS

 

So... now I need to figure out how this happened. Thanks!