the response time of the BI Systems should be fast enough for the user not to become impatient (ideally). This can be achieved in most of the cases known to us be preprocessing the requested dataset prior to access by reportserver (i.e. overnight). If you deal with millions of rows you might as well consider using a columns store to achieve fast results for the given set of selected columns. Thus said the optimization approach has limits. There is a minimum processing time for a given setup especially if you consider that reportserver itself capsules the queries and makes them more complex by selecting columns and filtering attributes.
There is a way to track running reports in the system:
SELECT initial.date_field, final.date_field, initial.action, final.action, initial.KEY_FIELD, initial.VALUE FROM
(SELECT E.*, P.KEY_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE E.ENTITY_ID = P.LOG_ENTRY_ID AND E.ACTION IN ('REPORT_EXECUTION') AND P.KEY_FIELD = 'report_id'
) initial left join
(SELECT DISTINCT e.action, e.DATE_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE E.ENTITY_ID = P.LOG_ENTRY_ID AND E.ACTION IN ('END_REPORT_EXECUTION','REPORT_EXECUTED_FAILED') AND P.KEY_FIELD in ( 'report' , 'report_id' )
) final on initial.VALUE = final.VALUE
where final.value is null
order by final.DATE_FIELD desc
Note the P.KEY_FIELD in ( 'report' , 'report_id' ). In the current version, the "report" is correct, while in the next version "report_id" is correct. So the query above should always work. I raised ticket RS-3166 for this purpose.
We will consider implementing a "currently running reports" view in future versions: RS-3163.
Regards,
Eduardo