getting taskList with process variables non lazy

[holly77]

I got about 1500 taskInstances in my DB applied to a single user.
I'm extracting all these TaskInstances via

jbpmCtx.getTaskList("actorId");

Then I'm accessing the contextVariables of the according contextInstance by

Token tk = taskInstance.getToken();
 ProcessInstance pi = tk.getProcessInstance();
 ContextInstance ci = pi.getContextInstance();
 ci.getVariable("nameOfVariable");

Problem is, that the ProcessInstance, ContextInstance and Variables are fetched non lazy by Hibernate. Thus for each access to a Set of variables a new query is executed. It takes ca. 50 sec to retrieve all the results i need, wich is much too long!

Currently I'm trying a workaround based upon HQL like this:

List<Object> list =
s.createQuery("select ti, tk, pi, vi " +
"from TaskInstance ti " +
"join ti.token tk " +
"join tk.processInstance pi, " +
"VariableInstance vi " +
"where vi.token=tk").
list();

This is rather ugly...

Does anyone know, or can anyone think of another, better way to initialize the Variables of the according ContextInstance of a TaskInstance.?

By the way...
Shouldn't the global variables of a Token be passed through to the local variables of a TaskInstance? Currently they are not... Is this a bug?

[holly77]

I'm now doing it this way:

select distinct ti from TaskInstance ti
left join fetch ti.token tk
left join fetch tk.processInstance pi
left join fetch pi.instances ci
left join fetch ci.tokenVariableMaps tvm
left join fetch tvm.variableInstances vi
where ti.isOpen=:isOpen
and ti.actorId=:actorId;

Anyway it takes about 15 sec for the 1500 TaskInstances to be initialized...

[olivier_debels]

Indeed when you want to remove the laziness in hibernate you have two options:
1) change the mapping files to remove lazy retrieval
2) make queries where you perform non lazy joining etc...

The second approach is more preferable IMO since you may want to perform lazy querying for some queries and non-lazy for others.

So the solution you provided is indeed similar to the thing we came up (doing fetch joins) at this point. This gives better performance, however I still need to investigate the querying a bit further because there are still too much SQL queries passing by.

We also played around with batch fetching and subselect fetching.

Just a question: how many sql queries are done when you retrieve the list of 1500 task instances?

[holly77]

With the query I posted a single SQL statement is executed to fetch the TaskInstances.
This is why I'm wondering, that it takes that long retrieving the results, though.
Maybe an index on a foreignk key column is missing? I'm not that far into it up to now.
Any other suggestions appreciated.
Thanks!

[kukeltje]

1500 taskinstances for one user? Has he/she been on holiday the last year? when doe they need to be completed?

On one hand I'm just joking, on the other hand I'm not. There is indeed room for improvement, but seriously. I'f one of the people in our system has over 10 open tasks it's a lot, with 20 a manager gets nervous.