Hybris Best way to create index on custom table?

[superpalmi]

Hi, I have this custom table of promotions, each promotion can be related to a list of point of services, a list of products (partnerProducts) or just a single product productCode, I need to insert an index to improve this kind of query:

SELECT DISTINCT {PK} FROM {PromoWrapper AS pw LEFT JOIN PromoWrapper2PointOfService AS pw2p ON {pw2p.source}={<a rel="noopener noreferrer" href="http://pm.pk/" target="_blank">pw.pk</a>}} WHERE ({pw:productCode}=?productCode OR {pw:partnerProducts} LIKE '%...%' OR {pw:includedProducts} LIKE '%...%') AND {pw:typology} IN (?typology) AND ({pw:pointOfService} = ?pointOfService OR {pw2p.target} = ?correctPosPk) AND current_timestamp' BETWEEN {pw:startDate} AND {pw:endDate} AND {pw:category} IN (?category)<br>

What is the best way to improve this query? How much can Indexes improve performances for this?

the model is built like this:

 <itemtype code="PromoWrapper"><br>          </description><br>          <deployment table="promowrapper" typecode="..."/><br>          <attributes><br>              <attribute qualifier="code" type="java.lang.String"><br>                  <persistence type="property"/><br>                  <modifiers unique="true" optional="false"/><br>              </attribute><br>              <attribute qualifier="typology" type="ConadPromoTypology"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="name" type="java.lang.String"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="description" type="java.lang.String"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="startDate" type="java.util.Date"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="endDate" type="java.util.Date"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="productCode" type="java.lang.String"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="partnerProducts" type="StringCollection"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="basePrice" type="java.math.BigDecimal"><br>                  <persistence type="property"/><br>              </attribute><br>              <attribute qualifier="finalPrice" type="java.math.BigDecimal"><br>                  <persistence type="property"/><br>              </attribute>

<attribute qualifier="category" type="PromoCategory"><br>    <persistence type="property"/><br></attribute>

</itemtype>

<relation code="PromoWrapper2PointOfService" localized="false"><br>    <description>n:n relation between PromoWrapper and PointOfService</description><br>    <deployment table="promowrapper2pos" typecode="..."/><br>    <sourceElement qualifier="promowrapper" type="promowrapper" cardinality="many"/><br>    <targetElement qualifier="pointOfServiceList" type="PointOfService" cardinality="many"/><br></relation>

[eli_pairoux]

Hi Riccardo,

If you have on prem Hybris, you should explore the promotion engine to deal with the requirement within the standard functionalities to garantuee performance:

https://help.sap.com/docs/SAP_COMMERCE/9d346683b0084da2938be8a285c0c27a/6931dac585fc4b94816e5abf8227...

If you have a cloud (commerce/hybris) solution, this engine has been depreciated and replaced by a new one (see documentation for accelerator Telco/Utilities: https://help.sap.com/docs/TELCO_ACCELERATOR/32f0086927f44c9ab1199f1dab8833cd/92e2b1c8851a4525a0e4714....

Alternatively you could investigate to store the custom promo table in the S/4 HANA backend, where the indexes on custom tables are freely to be defined. I would advise to put validity dates in the index (like you did), but not the product. I would have add a promo eligible flag to the product attribute instead.

Also have a look at the Global pricing discount feature on SAP's roadmap for (headless) Omnichannel Commerce, promotion solution where you have an OCC REST API GetPromotion.

https://help.sap.com/docs/TELCO_ACCELERATOR/f59b0ac006d746caaa5fb599b4270151/620a19f591cf4846a0160c7...

Of course, this all means back to the architect's drawing board....

Krgds,
Eli