Showing results for 
Search instead for 
Did you mean: 

Hybris Best way to create index on custom table?

0 Kudos

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="" target="_blank"></a>}} WHERE ({pw:productCode}=?productCode OR {pw:partnerProducts} LIKE '%...%' OR {pw:includedProducts} LIKE '%...%') AND {pw:typology} IN (?typology) AND ({pw:pointOfService} = ?pointOfService OR {} = ?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>
<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>

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

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:

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:

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.

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