A set of SQL queries tied to the Activity Snapshot tool is creating an overhead on the database and could be effectively optimised.

We have an issue with the overhead of a specific set of SQLs in SFG 6.1.1.1. IBM confirmed that these queries have been looked at for version 6.1.1.3 but as we have no details of the changes, we’d like to create an Idea.

These are the SQLs seen running regularly on the database. They are run in tandem and have been tied to the Activity Snapshot tool in the main SFG UI.

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.REVIEWED <> 0 AND FG_ARRIVEDFILE.STATE = 'Failed' AND FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY;

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.STATE IN('Routed', 'Failed', 'Ignored') AND FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY;

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.STATE = 'Routed' AND FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY;

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.STATE IN('Routed', 'Failed', 'Ignored');

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.REVIEWED <> 0 AND FG_ARRIVEDFILE.STATE = 'Failed' AND NOT EXISTS (SELECT * FROM FG_ROUTE WHERE FG_ROUTE.ARRIVEDFILE_KEY = FG_ARRIVEDFILE.ARRIVEDFILE_KEY);

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE WHERE FG_ARRIVEDFILE.STATE IN('Started', 'Determining Routes', 'Routing', 'Unknown');

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.STATE IN('Routed', 'Failed', 'Ignored') AND NOT EXISTS (SELECT * FROM FG_ROUTE WHERE FG_ROUTE.ARRIVEDFILE_KEY = FG_ARRIVEDFILE.ARRIVEDFILE_KEY);

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.REVIEWED = 0 AND FG_ARRIVEDFILE.STATE = 'Failed' AND NOT EXISTS (SELECT * FROM FG_ROUTE WHERE FG_ROUTE.ARRIVEDFILE_KEY = FG_ARRIVEDFILE.ARRIVEDFILE_KEY);

SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= TO_DATE(:1 ,'YYYY-MM-DD HH24:MI:SS') AND FG_ARRIVEDFILE.REVIEWED = 0 AND FG_ARRIVEDFILE.STATE = 'Failed' AND FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY;

This rewrite was suggested by our DBA (Oracle DB):

select /*SWAT_ARRIVEDFILECOUNTS*/
count(case when arr.state in ('Routed', 'Failed', 'Ignored') then rou.route_key end) rou_fail_ignore_cnt,
count(case when arr.state = 'Routed' then rou.route_key end) rou_cnt,
count(case when arr.state = 'Failed' and arr.reviewed = 0 then rou.route_key end) reviewed_fail_cnt,
count(distinct arr.arrivedfile_key) arr_cnt,
count(distinct case when arr.state in ('Routed', 'Failed', 'Ignored') and rou.route_key is null then arr.arrivedfile_key end) noroute_rou_faile_ignore_cnt,
count(distinct case when arr.state = 'Failed' and arr.reviewed = 0 and rou.route_key is null then arr.arrivedfile_key end) noroute_reviewed_fail_cnt
from fg_arrivedfile arr
left join fg_route rou
on arr.arrivedfile_key = rou.arrivedfile_key
where arr.modifyts >= to_date ( :1, 'YYYY-MM-DD HH24:MI:SS')
and arr.state in ('Routed', 'Failed', 'Ignored');

This could greatly reduce the number of calls as one SQL could be used instead of 8 or 9.