Currently I am working on a Siebel Financial Applications project using a lot of Business Rules Processor (BRPs) . The BRP way of working with Business Components is by using the various methods available under the FINS CAP Buscomp Handler Business Service.
FINS CAP Buscomp Handler Business Service provides the following five methods:
The BS works without a Business Object context, ie while specifying the Business Component on which to operate, the Business Object name is not provided. It is the only BS I know in Siebel which operates directly on business components without taking the BO context. But as we realized, this is not always the best way of operating. As the amount of data grew, we found the BRPs going slower and performance degradation.
On spooling out the SQL when the BRPs were running, we found that null queries being run in the tables, without a search criteria. When the InsertRecord method of the BS is used to insert a record into say..Opportunity BC which is based on S_OPTY table, the BS was running this query first.
SELECT
T1.CONFLICT_ID,
T1.LAST_UPD,
T1.CREATED,
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
T14.USAGE,
T8.TRDIN_EXPIRE_DAYS,
T7.NAME,
T1.PR_DEPT_OU_ID,
T7.INTEGRATION_ID,
T7.LOC,
T7.OU_NUM,
T10.NAME,
T7.CURR_PRI_LST_ID,
T7.PR_BL_ADDR_ID,
T7.PR_BL_PER_ID,
T7.PR_SHIP_ADDR_ID,
T7.PR_SHIP_PER_ID,
T1.CONSUMER_OPTY_FLG,
T13.BL_ACCNT_ID,
T13.BL_CON_ID,
T1.CHANNEL_TYPE_CD,
T1.CURCY_CD,
T1.CUST_ACCNT_ID,
T14.PROJ_STAT_CD,
T1.CLOSED_FLG,
T13.GROUP_TYPE_CD,
T13.DEPARTURE_DT,
T13.ARRIVAL_DT,
T4.STATUS_INBND_CD,
T7.ROW_ID,
T1.PR_CON_ID,
T1.NAME,
T1.NEW_LOAN_FLG,
T13.OPTY_MARKET_CD,
T12.STAGE_STATUS_CD,
T13.OPTY_SEGMENT_CD,
T4.STATUS_CD,
T1.APPL_OWNER_TYPE_CD,
T1.PAR_OPTY_ID,
T5.NAME,
T9.PAR_POSTN_ID,
T5.PROJ_PRPTY_ID,
T1.ALIAS_NAME,
T1.PR_OU_INDUST_ID,
T1.PR_OU_ADDR_ID,
T1.PR_REP_DNRM_FLG,
T1.PR_REP_MANL_FLG,
T1.PR_REP_SYS_FLG,
T1.PR_CMPT_OU_ID,
T6.COUNTRY,
T9.PR_EMP_ID,
T1.PR_OPTYORG_ID,
T1.PR_OPTYPRD_ID,
T1.BU_ID,
T1.PR_PRTNR_ID,
T1.PR_POSTN_ID,
T1.SUM_REVN_AMT,
T1.SUM_CLASS_CD,
T1.SUM_EFFECTIVE_DT,
T1.SUM_COMMIT_FLG,
T1.SUM_COST_AMT,
T1.SUM_DOWNSIDE_AMT,
T1.SUM_REVN_ITEM_ID,
T1.SUM_MARGIN_AMT,
T1.SUM_TYPE_CD,
T1.SUM_UPSIDE_AMT,
T1.SUM_WIN_PROB,
T11.LOGIN,
T1.PR_SRC_ID,
T6.STATE,
T1.PR_TERR_ID,
T1.PROG_NAME,
T1.PROJ_PRPTY_ID,
T13.REL_TYPE_CD,
T1.SALES_METHOD_ID,
T12.NAME,
T1.STG_START_DT,
T1.CURR_STG_ID,
T12.STG_ORDER,
T1.SECURE_FLG,
T1.OPTY_CD,
T1.PGROUP_PUBLIC_FLG,
T1.BU_ID,
T2.FCST_CLS_DT,
T2.FCST_REVN_CURCY_CD,
T16.LOGIN,
T17.EFFECTIVE_DT,
T17.COST_AMT,
T17.DOWNSIDE_AMT,
T17.MARGIN_AMT,
T17.WIN_PROB,
T17.REVN_AMT,
T17.ACCNT_ID,
T17.CLASS_CD,
T17.REVN_AMT_CURCY_CD,
T17.QTY,
T17.CRDT_POSTN_ID,
T17.TYPE_CD,
T17.UPSIDE_AMT,
T19.FST_NAME,
T19.LAST_NAME,
T20.SRC_CD,
T13.ROW_ID,
T13.PAR_ROW_ID,
T13.MODIFICATION_NUM,
T13.CREATED_BY,
T13.LAST_UPD_BY,
T13.CREATED,
T13.LAST_UPD,
T13.CONFLICT_ID,
T13.PAR_ROW_ID,
T14.ROW_ID,
T14.PAR_ROW_ID,
T14.MODIFICATION_NUM,
T14.CREATED_BY,
T14.LAST_UPD_BY,
T14.CREATED,
T14.LAST_UPD,
T14.CONFLICT_ID,
T14.PAR_ROW_ID,
T2.ROW_ID,
T3.ROW_ID,
T17.ROW_ID,
T18.ROW_ID,
T20.ROW_ID
FROM
SIEBEL.S_OPTY T1
INNER JOIN SIEBEL.S_OPTY_POSTN T2 ON T1.PR_POSTN_ID = T2.POSITION_ID AND T1.ROW_ID = T2.OPTY_ID
INNER JOIN SIEBEL.S_PARTY T3 ON T2.POSITION_ID = T3.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SYS_KEYMAP T4 ON T1.ROW_ID = T4.SIEBEL_SYS_KEY
LEFT OUTER JOIN SIEBEL.S_OPTY T5 ON T1.PAR_OPTY_ID = T5.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ADDR_PER T6 ON T1.PR_OU_ADDR_ID = T6.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT T7 ON T1.PR_DEPT_OU_ID = T7.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT_ATX T8 ON T1.BU_ID = T8.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_POSTN T9 ON T1.PR_POSTN_ID = T9.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_PRI_LST T10 ON T7.CURR_PRI_LST_ID = T10.ROW_ID
LEFT OUTER JOIN SIEBEL.S_USER T11 ON T9.PR_EMP_ID = T11.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_STG T12 ON T1.CURR_STG_ID = T12.ROW_ID
LEFT OUTER JOIN SIEBEL.S_OPTY_TNTX T13 ON T1.ROW_ID = T13.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_OPTY_DSGN_REG T14 ON T1.ROW_ID = T14.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_POSTN T15 ON T2.POSITION_ID = T15.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_USER T16 ON T15.PR_EMP_ID = T16.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_REVN T17 ON T1.SUM_REVN_ITEM_ID = T17.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PARTY T18 ON T1.PR_CON_ID = T18.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CONTACT T19 ON T1.PR_CON_ID = T19.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_SRC T20 ON T1.PR_SRC_ID = T20.ROW_ID
ORDER BY
T17.EFFECTIVE_DT DESC.
If you analyze the last part of the SQL, there is no “WHERE” clause with a search specification , nor are there and bind variables. This query will simply return all records present in the Opportunity Business Component. That’s right, its an empty query on the S_OPTY table and all other tables left joined. And this is fired every time the InsertRecord method is fired. I think Siebel tries to see if the record going to be inserted is not a duplicate of an existing record in the system, and it does this by first firing and empty query and then comparing the result with what we are trying to insert. But as the number of records in the tables grow, the performance degrades. And this is a vanilla/OOTB business service.
Anyway, we had to do away with “FINS CAP Buscomp Handler:InsertRecord” and replaced it with another vanilla BS: “Inbound E-mail Database Operations” Business Service and its “InsertRecord” method. The syntax is not exactly same, some modifications are required. But after using this BS, we found a tremendous improvement in speed in the system.
No comments:
Post a Comment