I’m using Flowable 5.22 on a system with ~200000 historic process instances and ~5000000 historic variables and am hitting some issues where queries that involve the ACT_HI_VARINST or ACT_RU_VARIABLE tables are sometimes very slow e.g. 20 seconds instead of under a second. We have already added the index on the execution ID to ACT_HI_VARINST from 5.23. The database server is MS SQL Server 2014.
Digging into this has revealed that this is due to a query plan being used that is a poor match for the data resulting in a slow query. Adding FORCESEEK hints to queries against these tables seems to work to mitigate the issue but I’m wondering if anyone else has experienced this and what has been done to resolve it? Forcibly updating the table stats also seems to work at least for a while but seems too fragile to be an appealing solution.