CRM and CX Blogs by SAP
Stay up-to-date on the latest developments and product news about intelligent customer experience and CRM technologies through blog posts from SAP experts.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Workaround to fix performance issue with DroolsKIEModuleMedia table

YannickRobin
Product and Topic Expert
Product and Topic Expert
‎2023 Oct 14 11:03 AM
1,247


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.

Workaround


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.
1 Comment
Popular Blog Posts