listPage doesn't improve query time for createHistoricProcessInstanceQuery when using includeProcessVariables

[p4w3l]

includeProcessVariables() in HistoricProcessInstanceQuery increases query time very very seriously.

Query times are:

Without includeProcessVariables() : &lt 1s
With includeProcessVariables() : 20-30s

so I decided to use .listPage( first, pageSize ) but with values like ( 0, 10 ) it is still the same 20-30s

interesting that when using variableValueLike( "searchValue%", searchValue ) that returns 10-20 items it queries quick: 1-2s

I understand that includeProcessVariables() increases query time but why listPage( first, pageSize ) doesn't improve?

Below is my query:

List<HistoricProcessInstance> result = historyService.createHistoricProcessInstanceQuery()
            .processDefinitionKey( "Process1" )
            .finished()
            .variableValueLike( "searchString", searchValue )
            .includeProcessVariables()
            .orderByProcessInstanceStartTime()
            .desc()
            .listPage( first, pageSize );‍‍‍‍‍‍‍‍‍

[vasile_dirla]

Hi,
if you'll have a look into the code in the class: HistoricProcessInstanceEntityManager.java  at method: findHistoricProcessInstancesAndVariablesByQueryCriteria

you'll see this comment:
<code>
    // paging doesn't work for combining process instances and variables due to an outer join, so doing it in-memory
</code>

Sorry for the inconvenient but this is what was possible to be done at that moment.