-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
7.15.6
-
None
Environment (Required on creation):
- Camunda 7.15.6 (but should be the same for other versions)
- MySQL 5.7.12
Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):
When executing the selectProcessInstances SQL query (from Cockpit or manually) the ACT_HI_PROCINST table is joined (when history is enabled). This table is necessary for startedBefore and startedAfter query parameters. If the parameters are not set the table is not used.
On MySQL systems (maybe also others), this leads to a full table scan on the historic table which can take a while depending on the size of the table. Example: In one case the query took 30.5 seconds (with ~1.9 million entries in ACT_HI_PROCINST) whereas without joining the table the query was executed in 0.7s.
Steps to reproduce (Required on creation):
This issue was not reproducible on Camunda systems.
Observed Behavior (Required on creation):
The query (and therefore Cockpit) is slow.
Expected behavior (Required on creation):
The query executes faster.
Root Cause (Required on prioritization):
A full table scan is done on the ACT_HI_PROCINST table which takes a long time for larger tables.
Solution Ideas (Optional):
The ACT_HI_PROCINST table should only be joined when it is needed. Also, an index might help to reduce the execution time, when the table must be joined.