Siebel EAI and Performance of Child Object updates

This post has been submitted by Sachin V Rao reader of Siebel Unleashed.


I would like to share a learning which I have had on a performance topic in Siebel EAI. We all would have worked on scenarios of updating Object hierarchy that is parent child relationship using EAI Siebel Adapter in our projects. Most of the time the number of records in child is in the range of 10-50 or 100 – 1000. But in my experience I came across scenario where we used to get updates for 1-2 records for a child component, where parent could have the record count in the range of 100k to 500k. This is very true for retail industry, which can have large assortments of products and they all are grouped under 1 price list.

When IO was designed it was developed with Price list as parent and Price list item as child as per the Vanilla IO and BO structure.

But when interface ran with the data as mentioned above it took considerable time to update just 1 price list item in a price list. On further investigation and thorough reading of bookshelf, following statement was found in the bookshelf EAI guide. This content has been added in to Siebel 8.1 bookshelf. But the behavior can be seen in version  7.7 as well.

NOTE: For performance reasons, user keys for child integration components are not included in the WHERE clause of the SQL generated to query for child component records in the Siebel database. If you must query the child component to find matching records, then consider redesigning your integration objects, such as creating a new integration object where the child component becomes the parent. For example, if Account is the parent and Asset the child, and you to query for specific assets, then create a new integration object where Asset is the parent and Account is the child.

Since for child components Siebel EAI Adapter picks users key based on the first instance, so this approach makes sense from performance stand point, but this is also based on the assumption that child entities would not have too many records.

Basically what Siebel does is, it queries all child records based on the where clause from the parent child Link spec. And then identifies the user key to be used and evaluates the operation to be performed that is Insert/Update on child object by applying the user key on cached data in memory. This gives performance benefits when the number of child records are less.

But this is not true always as was the case in Price List Item update scenario.

Solution:

Considering above the IO needs to be redesigned to have price list Item as primary Integration Component, so that for each record user key is part of where clause, and post this change one would see significant improvement in performance.

3 Responses to Siebel EAI and Performance of Child Object updates

  1. Hi Neel… I have a question …
    In escalation tables, the very old records of 2004 were getting inserted and the interesting part is when I saw the user column it was showing my id…:d
    I did not change any thing or did trigger any workflow policy in production. But the old records were inserted into the escalation tables continously and the XML’s were getting created to EAI where EAI failed to update the records since they are very old.

    Could you please let me know what are the chances of this scenario.

    Thanks,
    Saishree

    • Hi Saishree,
      There can be a few ways such records would be inserted. Please check the below options.
      1. If the created/updated date is 2004, that would be only possible by EIM jobs.
      2. Check if there are any WF’s with RTE at the starting step activated recently this would create RTE and in turn trigger the corresponding action sets.
      3. Please check if there were any old policies that got activated accidentally, you can back trace this by checking how these records are created and verifying the same in escalated req and log tables.
      4. Also check if there are some SQL procedures/CRON jobs running.

      Regards,
      Naresh

Leave a Reply

Contribute