topic Re: Performance issue in query ot get row of ACT_HI_VARINST in Alfresco Forum

Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Fri, 09 Mar 2018 20:06:52 GMT

Below query has wrong index - SELECT *FROM ( SELECT a.* ,ROWNUM rnum FROM ( SELECT RES.* FROM ACT_HI_VARINST RES WH

Re: Performance issue in query ot get row of ACT_HI_VARINST

ryandawson — Tue, 13 Mar 2018 09:46:07 GMT

You could raise an issue and submit a PR on GitHub - Activiti/Activiti: Activiti is a light-weight workflow and Business Process Management (BPM) Platform targeted … but I'm not sure if we would want to do this. The problem I'm concerned with is that I think hint syntax is different for different databases (e.g. 'use hint' for sqlserver and the /* syntax for oracle). The Activiti queries need to run on different databases.

Activiti does have different scripts for different databases that create the tables and indexes Activiti/activiti-engine/src/main/resources/org/activiti/db/create at develop · Activiti/Activiti · GitHub It also does bulk inserts differently for different databases - Search · oracle · GitHub But for queries the pattern is to stick to SQL that runs on any platform so doing what you suggest would be doing something new and could get complex.

The reason it could get complex is that the Activiti engine's java API lets you construct queries by adding conditions. You can see the conditions you add in the codebase in Activiti/HistoricVariableInstanceQueryImpl.java at develop · Activiti/Activiti · GitHub and they translate to SQL by matching to checks in Activiti/HistoricVariableInstance.xml at develop · Activiti/Activiti · GitHub . This means Activiti doesn't know exactly which queries will be executed since they get constructed as they go along. So the if conditions in the xml can get complex.

If you're looking for a quick solution for you particularly you might be best to use a native sql query like in Activiti/HistoricVariableInstanceTest.java at 126e89911d49c6ec0ab3f222baeea2a256e24c0d · Activiti/Activiti · GitHub But we'd be happy to discuss further if you are keen to see this handled directly by the engine.

Re: Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Tue, 13 Mar 2018 18:27:14 GMT

Hi,

Thanks for your reply.

The query wherein I asked to get the hint added, is performing badly and its response time is very high.

Is there any way or configuration by which the queries which are frequently getting executed, we can put hint in there if that helps to improve its response time?

Re: Performance issue in query ot get row of ACT_HI_VARINST

ryandawson — Tue, 13 Mar 2018 18:55:09 GMT

I was thinking you could define the above as a native query following the example in Activiti/HistoricVariableInstanceTest.java at 126e89911d49c6ec0ab3f222baeea2a256e24c0d · Activiti/Activiti · GitHub , include the hint in the native query and then use that instead of the query that you are currently using in your code. Or is your question about the database syntax for the hint? Do you have a query with the hint that you are able to run directly on the database (i.e. without going through Activiti)?

Re: Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Tue, 13 Mar 2018 19:05:23 GMT

We need the query to be formed with hint included -

Is it correct that I need to update HistoricVariableInstance.xml -

Will I be able to achieve it with adding if condition to below -

<sql id="selectHistoricVariableInstanceByQueryCriteriaSql">
from ${prefix}ACT_HI_VARINST RES
<where>
<if test="id != null">
RES.ID_ = #{id}
</if>
<if test="processInstanceId != null">
RES.PROC_INST_ID_ = #{processInstanceId}
</if>
<if test="executionId != null">
RES.EXECUTION_ID_ = #{executionId}
</if>
<if test="taskId != null">
and RES.TASK_ID_ = #{taskId}
</if>
<if test="excludeTaskRelated">
and RES.TASK_ID_ is NULL
</if>
<if test="variableName != null">
and RES.NAME_ = #{variableName}
</if>
<if test="variableNameLike != null">
and RES.NAME_ like #{variableNameLike}
</if>

Re: Performance issue in query ot get row of ACT_HI_VARINST

ryandawson — Tue, 13 Mar 2018 19:10:52 GMT

I'm suggesting that you might first want to write a query and run it in something like sqlplus or sql developer or toad or dbvisualizer. Once you have a SQL query that you know to work then you could use run that in Activiti using native query like in HistoricVariableInstanceTest.

Re: Performance issue in query ot get row of ACT_HI_VARINST

ryandawson — Tue, 13 Mar 2018 19:18:12 GMT

I should explain that I do think that code you copied is part of the query. The rest of the query comes from Activiti/HistoricVariableInstance.xml at develop · Activiti/Activiti · GitHub and the rownum part comes from Activiti/oracle.properties at develop · Activiti/Activiti · GitHub , which is what those expressions like {limitBefore} do. You can go the route of changing this in your cloned copy of the source code but you might find it easiest to ensure you have a working query that you're aiming for first.

Re: Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Tue, 13 Mar 2018 20:12:59 GMT

Ahh ok, I got you now,

The query works fine for oracle. That is tested.

I wanted to know what can be easiest way to get that hind added to query..!

The behavior that was observed is that, Activiti is insertion and deleting many of the variables very frequently and also there are select queries on these variables.

below select observed to have bad response time, so in order to make the improvement we want to have oracle hind added to it when it get run from Activiti.

SELECT *

FROM (

SELECT a.*

,ROWNUM rnum

FROM (

SELECT

RES.*

FROM ACT_HI_VARINST RES

WHERE RES.PROC_INST_ID_ = : 1

AND RES.NAME_ = : 2

ORDER BY RES.ID_ ASC

) a

WHERE ROWNUM < : 3

)

WHERE rnum >= : 4

How can I achieve to get hint added to select -

SELECT *

FROM (

SELECT a.*

,ROWNUM rnum

FROM (

SELECT /*+ index(RES, ACT_IDX_HI_PROCVAR_PROC_INST) */

RES.*

FROM ACT_HI_VARINST RES

WHERE RES.PROC_INST_ID_ = : 1

AND RES.NAME_ = : 2

ORDER BY RES.ID_ ASC

) a

WHERE ROWNUM < : 3

)

WHERE rnum >= : 4

will doing below change help ?

<sql id="selectHistoricVariableInstanceByQueryCriteriaSql">
from ${prefix} *+ index(RES, ACT_IDX_HI_PROCVAR_PROC_INST) */ ACT_HI_VARINST RES
<where>
<if test="id != null">
RES.ID_ = #{id}
</if>
<if test="processInstanceId != null">
RES.PROC_INST_ID_ = #{processInstanceId}
</if>
<if test="executionId != null">
RES.EXECUTION_ID_ = #{executionId}
</if>
<if test="taskId != null">
and RES.TASK_ID_ = #{taskId}
</if>

Re: Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Tue, 13 Mar 2018 22:38:49 GMT

Hi,

I extracted the jar - activiti-engine-5.18.0.jar

and it has only below directories -

create

drop

mapping

upgrade

The path you pointed to - Activiti/activiti-engine/src/main/resources/org/activiti/db/properties/oracle.properties

is not there either in activiti-engine-5.18.0.jar nor in activiti-engine-5.18.0-sources.jar

I think, this file might have missed in earlier in activiti adoption, but as it worked well (though performance is the issue ) then are there any default values considered for these variables?

Re: Performance issue in query ot get row of ACT_HI_VARINST

dharmeshyadav — Tue, 13 Mar 2018 23:52:38 GMT

Hi,

I checked further -

For version, 5.18,

env variables are hardcoded, like limitBefore.

File - DbSqlSessionFactory.java

databaseSpecificLimitBeforeStatements.put("oracle", "select * from ( select a.*, ROWNUM rnum from (");
databaseSpecificLimitAfterStatements.put("oracle", " ) a where ROWNUM < #{lastRow}) where rnum >= #{firstRow}");

Kindly, please correct me if my observation is wrong and advise how can I configure the hint.

If below configuration can be added, to oracle.properties -

limitBefore=select * from ( select a.*, ROWNUM rnum from (
limitAfter= ) a where ROWNUM < #{lastRow}) where rnum >= #{firstRow}
boolValue=1

Then, we can define few more such parameters with required hints and these parameters can be referred from HistoricVariableInstance.xml and job will be done

Re: Performance issue in query ot get row of ACT_HI_VARINST

ryandawson — Wed, 14 Mar 2018 09:23:25 GMT

You're right this was changed in MyBatis Multi-db vendor support + other simple tweaks · Activiti/Activiti@d5ce081 · GitHub

So in v6+ of the engine the database-specific parts of queries are handled by those variables like limitBefore that come from the properties file.

In the latest version of Activiti 5 the old approach is still used where db-specific statements are added Activiti/DbSqlSessionFactory.java at 5.x · Activiti/Activiti · GitHub and they can be used in the xml e.g. Activiti/ProcessDefinition.xml at 5.x · Activiti/Activiti · GitHub

So for v5 it would be possible to add a db-specific query to Activiti/HistoricVariableInstance.xml at 5.x · Activiti/Activiti · GitHub and reference it in DbSqlSessionFactory.

For newer versions of the engine to add the oracle-specific hint it would be necessary to define a new variable like limitBefore can come from oracle.properties and register it in ProcessEngineConfigurationImpl.