4 Replies Latest reply on Apr 3, 2007 6:34 PM by Ronald van Kuijk

    getting taskList with process variables non lazy

    Wolfgang Hollweck Newbie

      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?

        • 1. Re: getting taskList with process variables non lazy
          Wolfgang Hollweck Newbie

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

          • 2. Re: getting taskList with process variables non lazy
            Olivier Debels Newbie


            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?

            • 3. Re: getting taskList with process variables non lazy
              Wolfgang Hollweck Newbie

              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!

              • 4. Re: getting taskList with process variables non lazy
                Ronald van Kuijk Master

                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.