Hello ,
When we try to run "interaction flow report" through GCXI . it take forever to run. and timeout
I tried to get the query as per the following link
Slow Response and run it to the database directly and found it very slow.
When I tried to change the date time key search
and itf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121)))- 2764800
and itf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121)))+2764800
to be
and itf.START_DATE_TIME_KEY >= 1604347200- 2764800
and itf.START_DATE_TIME_KEY <= 1604433600+2764800
and it changed to be as per the following link
Fast Response And that become super fast 2 second response
Is there Any way to enhance the original query ? I tried to reorganize the indexes but still the same.
The system is very fresh up for 3 months only
Attached both queries
Best regards,
#Reporting/Analytics------------------------------
Abdelrahman Mohamed
IST Integration Services And Technologies Co.
------------------------------