We have identified a performance issue for a customer, FACT_PROJECT_CONN_DET is taken 3 + hours to do internal IFS data mart refresh. 

• The Refresh for FACT_PROJECT_CONN_DET is taking 3+ hours to run on each refresh.
• They have set a separate refresh category to run this overnight, but as they informed it is now impacting their month end process as they cannot run data mart jobs in parallel.
• With the timely manner performance issue got worse up to 9+ hours with the growing data load of 7+ million in the environment.

Note:
Highest time-consuming SQL query running when refresh data mart for FACT_PROJECT_CONN_DET.

INSERT INTO PROJECT_CONN_DET_MVT
SELECT * FROM ( SELECT F.*
FROM PROJECT_CONN_DET_MVS F
WHERE ((F.PROJECT_ID) IN (SELECT P1.PROJECT_ID FROM T1512369F9074D569E0635904F_TAB P1))
OR ((F.ACTIVITY_SEQ_KEY) IN
(SELECT A1.ACTIVITY_SEQ FROM S1512369F905BD569E0635904F_TAB A1))
OR ((F.ACTIVITY_SEQ_KEY, F.OBJECT_TYPE_KEY, F.KEYREF1_KEY, F.KEYREF2_KEY,
F.KEYREF3_KEY, F.KEYREF4_KEY, F.KEYREF5_KEY, F.KEYREF6_KEY) IN
(SELECT PC1.ACTIVITY_SEQ,
PC1.PROJ_LU_NAME,
PC1.KEYREF1,
PC1.KEYREF2,
PC1.KEYREF3,
PC1.KEYREF4,
PC1.KEYREF5,
PC1.KEYREF6
FROM C1512369F906FD569E0635904F_TAB PC1))
OR ((F.ACTIVITY_SEQ_KEY, F.OBJECT_TYPE_KEY, F.KEYREF1_KEY, F.KEYREF2_KEY,
F.KEYREF3_KEY, F.KEYREF4_KEY, F.KEYREF5_KEY, F.KEYREF6_KEY,
F.CONTROL_CATEGORY_KEY, F.ELEMENT_TYPE_DB_KEY) IN
(SELECT PCD.ACTIVITY_SEQ,
PCD.PROJ_LU_NAME,
PCD.KEYREF1,
PCD.KEYREF2,
PCD.KEYREF3,
PCD.KEYREF4,
PCD.KEYREF5,
PCD.KEYREF6,
PCD.CONTROL_CATEGORY,
'COST'
FROM L1512369F9065D569E0635904F_TAB PCD))
OR ((F.ACTIVITY_SEQ_KEY, F.OBJECT_TYPE_KEY, F.KEYREF1_KEY, F.KEYREF2_KEY,
F.KEYREF3_KEY, F.KEYREF4_KEY, F.KEYREF5_KEY, F.KEYREF6_KEY,
F.CONTROL_CATEGORY_KEY, F.ELEMENT_TYPE_DB_KEY) IN
(SELECT PCR.ACTIVITY_SEQ,
PCR.PROJ_LU_NAME,
PCR.KEYREF1,
PCR.KEYREF2,
PCR.KEYREF3,
PCR.KEYREF4,
PCR.KEYREF5,
PCR.KEYREF6,
PCR.CONTROL_CATEGORY,
SUBSTR('REVENUE', 1, 4)
FROM P1512369F906AD569E0635904F_TAB PCR))
OR ((F.ACTIVITY_SEQ_KEY, F.OBJECT_TYPE_KEY, F.KEYREF1_KEY, F.KEYREF2_KEY,
F.KEYREF3_KEY, F.KEYREF4_KEY, F.KEYREF5_KEY, F.KEYREF6_KEY,
F.CONTROL_CATEGORY_KEY) IN
(SELECT PCB.ACTIVITY_SEQ,
PCB.PROJ_LU_NAME,
PCB.KEYREF1,
PCB.KEYREF2,
PCB.KEYREF3,
PCB.KEYREF4,
PCB.KEYREF5,
PCB.KEYREF6,
PCB.CONTROL_CATEGORY
FROM V1512369F9060D569E0635904F_TAB PCB)))

RnD provide the Bug fix 169509 for the above issue and after that there is a performance gain around 5+ hours from the provided fix.

Question:

Customer is looking for further performance improvement, and the RnD recommendation is to look into a design-level change.

According to the latest discussions of RnD team, they have mentioned that they have already gained maximum improvement for this issue using micro-caching from the previous fix in the support scope.

So, this forum is created to request further improvement from design-level change.