First thoughts.

Approach 1. Store intermediate results (once in a period in background)

E.g. table ACTIVITY_STATISTICS (may be metrics table can be used) with fields:

• datetime
• proc_def_id
• act_inst_id
• finished
• running
• completedScope
• cancelled

Request statistics for dateX:
1. Select from ACTIVITY_STATISTICS where datetime <= dateX
2. Calculate statistics for the residuary period (datetime, dateX]
3. Aggregate the results

Problems:
1. What if history cleanup has run?
2. What if delete historic process instance was called?

Possible solutions:
1. Correct the data in statistics table before removal -> can be tricky as new data at the same time can be coming and we can't just move the datetime, we then need to take into account the whole delta: removed + added. This can slow down history cleanup.
2. Clean all previously stored statistics and recalculate it period by period -> in case of constantly running history cleanup won't work.

Approach 1a.
Sacrifice the accuracy and not correct the data after it was added (the same as Optimize do).

Approach 2.
Only allow selecting statistics (and showing hashmap) for some period. + Make the period selectable and default period configurable on the front-end.

To be discussed.

Second approach was implemented. Now we can define restrictions on start date and end end of historic process instances, for with the statistics will be shown.

Indices are tuned to correspond to the statistics SQL query, but it can still happen that in some specific situation more analysis of SQL execution plan will be needed and may be some further index adjustments will be required.