Historic variable query: bad performance due to many separate generated queries

Camunda 7 Topics Discussion & Questions
1

Hello,

I use the Java API to query for historic variables like this:

        var vars = historyService.createHistoricVariableInstanceQuery()
                .processInstanceIdIn(.... List of process instance Ids...)
                .variableNameIn(... list of variable names...)
                .unlimitedList();

This works, but is rather slow because according to SQL log (logger org.camunda.bpm.engine.impl.persistence.entity set to DEBUG) camunda creates many separate queries for all serialized variables stored in ACT_GE_BYTEARRAY. The log contains dozens of separate queries like this:

select * from ACT_GE_BYTEARRAY where ID_ = ?
2e9b3367-f2dd-11ec-b2b7-005056911918(String)

Is it possible to tell Camunda to create a single query like the following instead?

select * from ACT_GE_BYTEARRAY where ID_ IN ('uid1', 'uid2', ...)

Or can I only optimize performance by using a native query or a creating a custom view and a mybatis mapping for it?

2

Hi @Dierk ,

personally I do know of any option to anyhow give Camunda a hint on how to build queries. Depending on your usecase HistoricVariableInstanceQuery#disableBinaryFetching might be an option. Otherwise I think your only option is to manually access the ACT_HI_VARINST table.

Kind regards
Adagatiya

3

Hello,

I already tried to use disableBinaryFetching like this, but this does not seem to have an effect. The SQL log still shows many separate queries on the table ACT_GE_BYTEARRAY

        var vars = historyService.createHistoricVariableInstanceQuery()
                .disableBinaryFetching()
                .processInstanceIdIn(processes.keySet().toArray(String[]::new))
                .variableNameIn(varNames)
                .unlimitedList();

Is there another parameter or flag to be set in order to prevent fetching of serialized variable values? I tried to disable deserialization as well (code below), but still many single queries are executed on table ACT_GE_BYTEARRAY

        var vars = historyService.createHistoricVariableInstanceQuery()
                .disableBinaryFetching()
                .disableCustomObjectDeserialization()
                .processInstanceIdIn(processes.keySet().toArray(String[]::new))
                .variableNameIn(varNames)
                .unlimitedList();