3655735 - Performance issue while openning Device History Report

Device History Report is loading slowly. When checking the SQL traces, it appears that the following query taking a long time to execute:

 select distinct al.DATE_TIME, al.CREATED_TIMESTAMP DATA_TIME_WITH_MILLIS, al.SITE, al.SFC, al.ACTION_CODE, alc.DESCRIPTION ACTION_CODE_DESCRIPTION, al.ACTION_DETAIL, al.ACTION_DETAIL_HANDLE, al.ITEM, COALESCE(al.CREATED_TIMESTAMP, al.DATE_TIME) CREATED_TS_OR_DATE_TIME, COALESCE((SELECT distinct  TABLE_T.DESCRIPTION FROM AL_ITEM PARENT_TABLE LEFT JOIN AL_ITEM_T TABLE_T ON TABLE_T.ITEM_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = i.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_ITEM PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = i.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT distinct  TABLE_T.DESCRIPTION FROM AL_ITEM PARENT_TABLE LEFT JOIN AL_ITEM_T TABLE_T ON TABLE_T.ITEM_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = i.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_ITEM PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = i.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT DESCRIPTION FROM ITEM_T TABLE_T WHERE i.HANDLE = TABLE_T.ITEM_BO AND TABLE_T.LOCALE = 'en'), (SELECT DESCRIPTION FROM ITEM_T TABLE_T WHERE i.HANDLE = TABLE_T.ITEM_BO AND TABLE_T.LOCALE = 'en'), (SELECT MIN(DESCRIPTION) FROM ITEM_T TABLE_T WHERE i.HANDLE= TABLE_T.ITEM_BO)) ITEM_DESCRIPTION, al.ITEM_REVISION, al.OPERATION, al.OPERATION_REVISION, COALESCE((SELECT distinct  TABLE_T.DESCRIPTION FROM AL_OPERATION PARENT_TABLE LEFT JOIN AL_OPERATION_T TABLE_T ON TABLE_T.OPERATION_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = o.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_OPERATION PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = o.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT distinct  TABLE_T.DESCRIPTION FROM AL_OPERATION PARENT_TABLE LEFT JOIN AL_OPERATION_T TABLE_T ON TABLE_T.OPERATION_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = o.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_OPERATION PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = o.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT DESCRIPTION FROM OPERATION_T TABLE_T WHERE o.HANDLE = TABLE_T.OPERATION_BO AND TABLE_T.LOCALE = 'en'), (SELECT DESCRIPTION FROM OPERATION_T TABLE_T WHERE o.HANDLE = TABLE_T.OPERATION_BO AND TABLE_T.LOCALE = 'en'), (SELECT MIN(DESCRIPTION) FROM OPERATION_T TABLE_T WHERE o.HANDLE= TABLE_T.OPERATION_BO)) OPERATION_DESCRIPTION, al.ROUTER, al.ROUTER_REVISION, al.RESRCE, COALESCE((SELECT distinct  AL_TABLE.DESCRIPTION FROM AL_RESRCE AL_TABLE WHERE  AL_TABLE.HANDLE = r.HANDLE and AL_TABLE.AL_START_TIME = (SELECT MAX( AL_TABLE.AL_START_TIME) FROM  AL_RESRCE AL_TABLE WHERE  AL_TABLE.HANDLE = r.HANDLE AND AL_TABLE.AL_START_TIME<=al.DATE_TIME)),(SELECT  WIP_TABLE.DESCRIPTION FROM RESRCE WIP_TABLE WHERE  WIP_TABLE.HANDLE = r.HANDLE )) RESOURCE_DESCRIPTION, al.WORK_CENTER, COALESCE((SELECT distinct  TABLE_T.DESCRIPTION FROM AL_WORK_CENTER PARENT_TABLE LEFT JOIN AL_WORK_CENTER_T TABLE_T ON TABLE_T.WORK_CENTER_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = wc.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_WORK_CENTER PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = wc.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT distinct  TABLE_T.DESCRIPTION FROM AL_WORK_CENTER PARENT_TABLE LEFT JOIN AL_WORK_CENTER_T TABLE_T ON TABLE_T.WORK_CENTER_BO = PARENT_TABLE.AL_HANDLE WHERE  PARENT_TABLE.HANDLE = wc.HANDLE and PARENT_TABLE.AL_START_TIME = (SELECT MAX( PARENT_TABLE.AL_START_TIME) FROM  AL_WORK_CENTER PARENT_TABLE WHERE  PARENT_TABLE.HANDLE = wc.HANDLE AND PARENT_TABLE.AL_START_TIME<=al.DATE_TIME) AND TABLE_T.LOCALE = 'en'),(SELECT DESCRIPTION FROM WORK_CENTER_T TABLE_T WHERE wc.HANDLE = TABLE_T.WORK_CENTER_BO AND TABLE_T.LOCALE = 'en'), (SELECT DESCRIPTION FROM WORK_CENTER_T TABLE_T WHERE wc.HANDLE = TABLE_T.WORK_CENTER_BO AND TABLE_T.LOCALE = 'en'), (SELECT MIN(DESCRIPTION) FROM WORK_CENTER_T TABLE_T WHERE wc.HANDLE= TABLE_T.WORK_CENTER_BO)) WORK_CENTER_DESCRIPTION, al.USER_ID, al.STEP_ID, al.SHOP_ORDER_BO, so.SHOP_ORDER, so.CUSTOMER_ORDER, lcc.LABOR_CHARGE_CODE LCC, al.QTY, al.TXN_ID, rs.SUBSTEP, rs.SUBSTEP_ID, rs.SUBSTEP_GROUP, rs.TIMER, rs.DESCRIPTION SUBSTEP_DESCRIPTION , rs.LONG_DESCRIPTION SUBSTEP_LONG_DESCRIPTION, rs.ERP_SUBOPERATION, ss.SUBSTEP_CATEGORY, ss.SUBSTEP_TYPE, ss.REVISION SUBSTEP_REVISION from ACTIVITY_LOG al inner join ACTIVITY_LOG_CONFIG alc on (al.SITE = alc.SITE and al.ACTION_CODE = alc.ACTION_CODE) left outer join ROUTER_STEP rst on (al.STEP_ID = rst.STEP_ID and rst.ROUTER_BO like CONCAT('%,', CONCAT(CONCAT(al.ROUTER,',_,'), al.ROUTER_REVISION))) left outer join ROUTER_SUBSTEP rs on (al.SUBSTEP = rs.SUBSTEP and al.SUBSTEP_ID = rs.SUBSTEP_ID and rs.ROUTER_STEP_BO = rst.HANDLE ) left outer join SUBSTEP ss on (ss.SUBSTEP = rs.SUBSTEP and ss.SITE = al.SITE and ss.CURRENT_REVISION = 'true') left outer join OPERATION o on (al.SITE = o.SITE and al.OPERATION = o.OPERATION and al.OPERATION_REVISION = o.REVISION) left outer join LABOR_CHARGE_CODE lcc on al.LCC_BO = lcc.HANDLE left outer join SHOP_ORDER so on al.SHOP_ORDER_BO = so.HANDLE left outer join WORK_CENTER wc on (al.WORK_CENTER = wc.WORK_CENTER and wc.SITE = al.SITE) left outer join ITEM i on (i.SITE = al.SITE and i.ITEM = al.ITEM and i.REVISION = al.ITEM_REVISION) left outer join RESRCE r on (r.SITE = al.SITE and r.RESRCE = al.RESRCE) where al.SITE = 'SZS'  and al.SFC = 'SZS15'  order by COALESCE(al.CREATED_TIMESTAMP, al.DATE_TIME)

• SAP Manufacturing Execution

Visiprise, Device History Report, DHR, performance issue , KBA , MFG-ME , SAP Manufacturing Execution , Problem