Performance issue in query ot get row of ACT_HI_VARINST

[dharmeshyadav]

Below query has wrong index - 

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

To enforce the index, somehow can we add below hint ?

/*+ index(RES, ACT_IDX_HI_PROCVAR_PROC_INST) */

[ryandawson]

You could raise an issue and submit a PR on GitHub - Activiti/Activiti: Activiti is a light-weight workflow and Business Process Management (BPM...  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 · ...   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/Ac...  But we'd be happy to discuss further if you are keen to see this handled directly by the engine.

[dharmeshyadav]

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?

[ryandawson]

I was thinking you could define the above as a native query following the example in Activiti/HistoricVariableInstanceTest.java at 126e89911d49c6ec0ab3f222baeea2a256e24c0d · Activiti/Ac...  , 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)?

[dharmeshyadav]

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>

[ryandawson]

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.

[ryandawson]

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.

[dharmeshyadav]

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>

[dharmeshyadav]

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?

[dharmeshyadav]

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