nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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) */

Find more posts tagged with

Alfresco Process Services

Comments

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

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/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)?

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>