SIL_GLRevenueFact_GL_Info_Update and SIL_ARTransactionFact_Gl_Info_Update

I have seen some performance issues with SIL_GLRevenueFact_GL_Info_Update and SIL_ARTransactionFact_Gl_Info_Update mapping. The issue is with both Full run and Incremental run. The throughput is really low, around 90-100 rows per second. We have notice this throughput when the target table W_GL_REVN_F has 20-30 million rows.

With 20-30 million rows in the target table, these mapping takes 48-72 hours for the full run.

The only workaround we found was to update the target table directly in the database and setting the DAC task as Completed. You can use MERGE statement in Oracle DB to update the target table, which is more optimized than UPDATE and INSERT combination. For example

MERGE INTO W_GL_REVN_F A USING W_GL_REVN_F_TMP B
ON (A.INTEGRATION_ID = B.INTEGRATION_ID AND A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID)
WHEN MATCHED THEN
UPDATE SET
A.ACCT_DOC_NUM = B.ACCT_DOC_NUM,
A.ACCT_DOC_ITEM = B.ACCT_DOC_ITEM,
A.ACCT_DOC_SUB_ITEM = B.ACCT_DOC_SUB_ITEM,
A.DOC_HEADER_TEXT = B.DOC_HEADER_TEXT,
A.LINE_ITEM_TEXT = B.LINE_ITEM_TEXT,
A.DOC_STATUS_WID = B.DOC_STATUS_WID,
A.POSTED_ON_DT_WID = B.POSTED_ON_DT_WID,
A.POSTED_ON_TM_WID = B.POSTED_ON_TM_WID,
A.GL_RECONCILED_ON_DT = B.GL_RECONCILED_ON_DT,
A.GL_RECONCILED_ON_PROC_WID = B.GL_RECONCILED_ON_PROC_WID,
A.DELETE_FLG = B.DELETE_FLG,
A.W_UPDATE_DT = B.W_UPDATE_DT,
A.ETL_PROC_WID = B.ETL_PROC_WID;


Here W_GL_REVN_F_TMP is the temp table, which was setup and it has the output of the original SIL_GLRevenueFact_GL_Info_Update mapping. This TEMP table was populated from the Source SQL of SIL_GLRevenueFact_GL_Info_Update.

Same solution was used for SIL_ARTransactionFact_Gl_Info_Update.

We used this workaround only for Full ETL. Incremental run is also slow for these 2 mappin but sine the insert/update are not that much, they are executing in our expeted duration of maintenance window.