Intermittent slow query plans on MSSQL?

Flowable Engine
1

Hi,

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.

Thanks,

Tom

2

Hi,

I am hitting the same slowness issue, with the same amount of data in these history tables.
This is very likely due to the nature of query issued by the Flowable REST API that I use which selects ACT_HI_VARINST with predicates on long_, var_type_ and name_ , because long_ column is not indexed.
So I plan to create a specific index for these 3 fields combination as it suits my use case.

What do you think?

Note that my DB is different: Postgres.

Thanks,
Rémi

3

That would indeed work, but it can’t be applied generically (by Flowable) because it’s specific for long variables.

4

Actually, I have created a partial index (with a where condition) in order to index these 3 columns only for a specific variable name used by my Flowable history API query. (whose value is almost unique so indexing is efficient)