Context

In SAP Commerce Cloud 1905, there was bug that impacts the performance of media queries. The most common examples are the queries for the media gallery populator and the synchronization process. DroolsKIEModuleMedia extends the “Media” itemtype but uses its own deployment table, it creates unnecessary UNION operation with droolskiemodulemedia table for each media query.

Here is an example of query:

SELECT item_t0.PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = @P0 AND item_t0.p_mediaformat = @P1) AND (item_t0.TypePkString IN (@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21) ) UNION ALL SELECT item_t0.PK FROM droolskiemodulemedia item_t0 WHERE ( item_t0.p_mediacontainer = @P22 AND item_t0.p_mediaformat = @P23) AND (item_t0.TypePkString=@P24 )

This bug has been fixed since SAP Commerce Cloud 2005 but unfortunately there is no back-port available for earlier releases. Customers who have migrated from a previous version are also probably impacted.

As a workaround, we will redefine the item type definition of DroolsKIEModuleMedia so that it no longer declares its own deployment table.

Below are the steps to apply the workaround.

Remove existing DroolsKIEModuleMedia type

First we need to remove all the items of this type.

• Login to Backoffice
• Navigate to System -> Types and perform search for DroolsKIEModuleMedia

• Search for the items of this type

• Delete all items

Remove existing DroolsKIEModuleMedia type

Once that is done, we need to remove the type itself.

• Login to hAC
• Navigate to Console -> Scripting Languages
• Paste in

modelService = spring.getBean('modelService')
typeService = spring.getBean('typeService')
type = typeService.getComposedTypeForCode('DroolsKIEModuleMedia')
de.hybris.platform.jalo.JaloSession.getCurrentSession().setAttribute('disableItemCheckBeforeRemovable', Boolean.TRUE)
modelService.remove(type)

• Make sure to click ‘Commit’ button
• Click ‘Execute‘

Redeclare DroolsKIEModuleMedia type definition

If you are running on SAP Commerce Cloud 2005 or more recent, this step is not required.

Publish promotions

If there were any published promotions, these would have to be explicitly republished once after the above steps.

Disclaimer

Always test such item type change on dev and stage environment first.