3 Replies Latest reply on Nov 15, 2008 7:00 AM by Ronald van Kuijk

    [3.1.4] access to variables somewhat slow

    Johan Parent Newbie

      Hi all,

      We use 3.1.4 in production for more than a year now. Overall we are satisfied. The bottlenecks appears to be access to variables instance, something we do plenty of when populating our jBPM driven SWING GUI. Add to that the fact that our app puts put a fair amount of values in the jBPM engine. All the values are associated with the process instance right after it is created.

      Our dba has already created a fair amount of indexes on the different tables. This has, obviously, helped a great deal. Yet, accessing process variables from a task instance remains the slowest operation (j-profiler says so).

      Below the SQL (not pretty printed...) caused by a single 'task.getVariables()' call. That's quite a number of queries. These all make sense when I read the code. Jumping from the variable container, i.e. that of the TaskInstance, to the parent variable container i.e. the ProcessInstance's and so on. But the result is fair number of milliseconds per call.

      Some my question is: is there a way to avoid this cost? Our process is simple (no nest processes).

      Any suggestion is much appreciated.

      Best regards,

      Johan

       - @10454aa 2,052 ms [2,132] 15 executing PreparedStatement: select taskinstan0_.ID_ as ID1_23_0_, taskinstan0_.NAME_ as NAME3_23_0_, taskinstan0_.DESCRIPTION_ as DESCRIPT4_23_0_, taskinstan0_.ACTORID_ as ACTORID5_23_0_,
      taskinstan0_.CREATE_ as CREATE6_23_0_,
      taskinstan0_.START_ as START7_23_0_, taskinstan0_.END_ as END8_23_0_,
      taskinstan0_.DUEDATE_ as DUEDATE9_23_0_,
      taskinstan0_.PRIORITY_ as PRIORITY10_23_0_, taskinstan0_.ISCANCELLED_ as ISCANCE11_23_0_, taskinstan0_.ISSUSPENDED_ as ISSUSPE12_23_0_, taskinstan0_.ISOPEN_ as ISOPEN13_23_0_,
      taskinstan0_.ISSIGNALLING_ as ISSIGNA14_23_0_, taskinstan0_.ISBLOCKING_ as ISBLOCKING15_23_0_, taskinstan0_.TASK_ as TASK16_23_0_, taskinstan0_.TOKEN_ as TOKEN17_23_0_, taskinstan0_.SWIMLANINSTANCE_ as SWIMLAN18_23_0_, taskinstan0_.TASKMGMTINSTANCE_ as TASKMGM19_23_0_ from workflow_db..JBPM_TASKINSTANCE taskinstan0_ where taskinstan0_.ID_=15000000115821
      
       - @10454aa 1,869 ms [2,010] 15 executing PreparedStatement: select variablein0_.TASKINSTANCE_ as TASKINS15_1_,
      variablein0_.ID_ as ID1_1_,
      variablein0_.NAME_ as NAME3_1_,
      variablein0_.ID_ as ID1_29_0_,
      variablein0_.NAME_ as NAME3_29_0_,
      variablein0_.CONVERTER_ as CONVERTER4_29_0_,
      variablein0_.TOKEN_ as TOKEN5_29_0_, variablein0_.TOKENVARIABLEMAP_ as TOKENVAR6_29_0_, variablein0_.PROCESSINSTANCE_ as PROCESSI7_29_0_,
       variablein0_.BYTEARRAYVALUE_ as BYTEARRA8_29_0_, variablein0_.DATEVALUE_ as DATEVALUE9_29_0_, variablein0_.DOUBLEVALUE_ as DOUBLEV10_29_0_,
       variablein0_.LONGIDCLASS_ as LONGIDC11_29_0_, variablein0_.LONGVALUE_ as LONGVALUE12_29_0_, variablein0_.STRINGIDCLASS_ as STRINGI13_29_0_,
       variablein0_.STRINGVALUE_ as STRINGV14_29_0_,
      variablein0_.CLASS_ as CLASS2_29_0_ from workflow_db..JBPM_VARIABLEINSTANCE variablein0_ wher
      e variablein0_.TASKINSTANCE_=15000000115821
      
       - @10454aa 5,591 ms [2,318] 15 executing PreparedStatement: select token0_.ID_ as ID1_25_0_,
      token0_.VERSION_ as VERSION2_25_0_,
      token0_.NAME_ as NAME3_25_0_,
      token0_.START_ as START4_25_0_,
      token0_.END_ as END5_25_0_,
      token0_.NODEENTER_ as NODEENTER6_25_0_,
      token0_.NEXTLOGINDEX_ as NEXTLOGI7_25_0_, token0_.ISABLETOREACTIVATEPARENT_ as ISABLETO8_25_0_, token0_.ISTERMINATIONIMPLICIT_ as ISTERMIN9_25_0_,
      token0_.ISSUSPENDED_ as ISSUSPE10_25_0_,
      token0_.NODE_ as NODE11_25_0_,
      token0_.PROCESSINSTANCE_ as PROCESS12_25_0_,
      token0_.PARENT_ as PARENT13_25_0_, t
      oken0_.SUBPROCESSINSTANCE_ as SUBPROC14_25_0_ from workflow_db..JBPM_TOKEN token0_ where token0_.ID_=15000000048948
       - @10454aa 1,617 ms [1,735] 15 executing PreparedStatement: select processins0_.ID_ as ID1_16_0_,
      processins0_.VERSION_ as VERSION2_16_0_,
      processins0_.START_ as START3_16_0_,
      processins0_.END_ as END4_16_0_,
      processins0_.ISSUSPENDED_ as ISSUSPEN5_16_0_, processins0_.PROCESSDEFINITION_ as PROCESSD6_16_0_, processins0_.ROOTTOKEN_ as ROOTTOKEN7_16_0_, processins0_.SUPERPROCESSTOKEN_ as SUPERPRO8_16_0_ from workflow_db..JBPM_PROCESSINSTANCE processins0_ where processins0_.ID_=15000000048648
       - @10454aa 1,648 ms [1,648] 15 executing PreparedStatement: select instances0_.PROCESSINSTANCE_ as PROCESSI3_1_,
      instances0_.ID_ as ID1_1_,
       instances0_.NAME_ as NAME5_1_,
      instances0_.ID_ as ID1_11_0_,
      instances0_.PROCESSINSTANCE_ as PROCESSI3_11_0_, instances0_.TASKMGMTDEFINITION_ as TASKMGMT4_11_0_, instances0_.CLASS_ as CLASS2_11_0_ from workflow_db..JBPM_MODULEINSTANCE instances0_ where instances0_.PROCESSINSTANCE_=15000000048648
       - @10454aa 1,806 ms [1,597] 15 executing PreparedStatement: select tokenvaria0_.CONTEXTINSTANCE_ as CONTEXTI3_1_,
      tokenvaria0_.ID_ as ID1_1_, tokenvaria0_.TOKEN_ as TOKEN2_1_, tokenvaria0_.ID_ as ID1_26_0_, tokenvaria0_.TOKEN_ as TOKEN2_26_0_, tokenvaria0_.CONTEXTINSTANCE_ as CONTEXTI3_26_0_ from workflow_db..JBPM_TOKENVARIABLEMAP tokenvaria0_ where tokenvaria0_.CONTEXTINSTANCE_=15000000097292
       - @10454aa 1,929 ms [2,063] 15 executing PreparedStatement: select variablein0_.TOKENVARIABLEMAP_ as TOKENVAR6_1_, variablein0_.ID_ as ID1_1_, variablein0_.NAME_ as NAME3_1_, variablein0_.ID_ as ID1_29_0_, variablein0_.NAME_ as NAME3_29_0_, variablein0_.CONVERTER_ as CONVERTER4_29_0_, variablein0_.TOKEN_ as TOKEN5_29_0_, variablein0_.TOKENVARIABLEMAP_ as TOKENVAR6_29_0_, variablein0_.PROCESSINSTANCE_ as PROCESSI7_29_0_, variablein0_.BYTEARRAYVALUE_ as BYTEARRA8_29_0_, variablein0_.DATEVALUE_ as DATEVALUE9_29_0_, variablein0_.DOUBLEVALUE_ as DOUBLEV10_29_0_, variablein0_.LONGIDCLASS_ as LONGIDC11_29_0_, variablein0_.LONGVALUE_ as LONGVALUE12_29_0_, variablein0_.STRINGIDCLASS_ as STRINGI13_29_0_, variablein0_.STRINGVALUE_ as STRINGV14_29_0_, variablein0_.CLASS_ as CLASS2_29_0_ from workflow_db..JBPM_VARIABLEINSTANCE variablein0_
      where variablein0_.TOKENVARIABLEMAP_=15000000048610
       - @10454aa committing connection