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...
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?
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.
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.