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.
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.