Performance issue with BTG evaluation

[Former Member]

Hello everybody,

We have defined a BTG rule to hide/show a banner in the front.

The scope defined is session, however after few tests weare observing that the btgresult tables contains a big number of records and some requests take considerable time.

 SELECT  item_t0.PK  FROM btgresult item_t0 WHERE ( item_t0.p_user  = 8796093087748 AND  item_t0.p_rule  = 8796093188449 AND  item_t0.p_resultscope  = 8796100460635 AND  item_t0.p_sessionid  = 's53894307177936') AND (item_t0.TypePkString=8796101017682 ) order by  item_t0.createdTS  desc,  item_t0.p_sequence  desc

And

 INSERT INTO btgresult ( hjmpTS,PK,createdTS,modifiedTS,TypePkString,p_forced,p_fulfilled,p_invalidated,p_resultscope,p_rule,p_sequence,p_sessionid,p_user ) VALUES (0,8966993742182,'2014-08-20 12:26:41.957','2014-08-20 12:26:41.957',8796101017682,0,0,0,8796100460635,8796093188449,'05217475','s36302122174928',8796093087748)

The result shouldn't be deleted after session end so the table will not contain big number of records ?

Thanks for your advice.

Kind Regards,

Imad Eddine

[former_member1336901]

Check the database execution plan of the SELECT statement. Most likely it is doing a full table scan, because the existing indexes are not selective enough to make usage of the index attractive. See my other post about adding an index that contains the session ID if that is the case.

[jitenbhanushali]

We are having the same issue and this leads to exception "Problem Getting Database Connection". We are thinking about deleting entries in this table but we don't know why this table is used and what would be the impact of deleting it. Is it just used for BTG Restriction or does it store any other data?

[former_member1336901]

At least in Hybris 5.0.x, the BTGSegmentResult type was missing appropriate indexes for retrieving BTG data at runtime. Especially the most selective attribute (sessionId) was not indexed, leading to a full table scan every time BTG data was retrieved. If you are using registered users most of the time (rather than anonymous), the performance impact for the missing index is much smaller, because the user is part of the existing index(es).

 <itemtype code="BTGSegmentResult" autocreate="false" generate="false">
     <indexes>
         <!--
              The ootb indexes on the table are pretty useless for queries with AND-equalities on
              (user,segment,resultScope,sessionId), which are run very frequently. There is an index
              on (user,segment,resultScope), which could be used, but these 3 attributes have a very
              low cardinality (between 1 and 4 each), making the usage of that index worthless (the table
              contains 1+ million rows), whereas the session ID has a very high cardinality. Therefore an
              index on just session ID makes a lot of sense here. Most BTG queries query by session ID (among
              other fields), so this index should help in other situations as well.
         -->
         <index name="sessionID" unique="false">
             <key attribute="sessionId" />
         </index>
     </indexes>
 </itemtype>
     

[former_member1336901]

The same seems still to be true in Hybris 5.7.

[former_member387866]

It's the same in all the versions 5.1 -> 6.2:
He is suggesting to remove the existing Index from the following

 <itemtype code="BTGSegmentResult" autocreate="true" generate="true" jaloclass="...BTGSegmentResult"  extends="BTGAbstractResult">
     <indexes>
         <index name="btgSegResIDX" unique="false">
             <key attribute="user"/>
             <key attribute="segment"/>
             <key attribute="resultScope"/>
         </index>
     </indexes>
 </itemtype>

 <itemtype code="BTGRuleResult" autocreate="true" generate="true" jaloclass="...BTGRuleResult" extends="BTGAbstractResult">
     <indexes>
         <index name="btgRulResIDX" unique="false">
             <key attribute="user"/>
             <key attribute="rule"/>
             <key attribute="resultScope"/>
             <key attribute="sessionId"/>
         </index>
     </indexes>
 </itemtype>

[Former Member]

Use a CMS Restriction here. BTG is not required.

[Former Member]

Hello,

Basically we have a kind of market-place in front site . So in the same area (Category page for example) customers can see different CMS content according to shopping center they are in. So we can talk about Segments of customers , so, using BTG, we can know behaviour and statistics of customer that bellong to diffrerent shopping centers. The rule is very simple, if I've chosen a shopping center SC1, i can only see component C1 in the same page. ( the SC is variable stored in session).

Hope it's more clear,

Kind Regards,

Imad Eddine

[Former Member]

Can you provide any details on the BTG rule you've defined and how it is configured (what's the evaluation method - full or optimized, etc)?

Yes, the Advanced Personalization module will certainly increase server processing load and careful consideration needs to be made on how/why it's being used.

[christoph_meyer]

I have it on good authority, that BTG is indeed slow.

[Former Member]

Any facts? Is this still true?

[former_member1336901]

In Hybris 5.7, there is one index on the btgresult table with a session ID. It uses (user, rule, resultscope, sessionId) - in this order. This index is not usable for anonymous users, as the user attribute is only used for logged in users.

Therefore you probably still should create a separate index which uses the session ID alone or session ID plus further attributes behind that.

You should check if this is different in Hybris 6.0, but I think it's not.