Poor performance when retrieving a list of tasks using getTasksAssignedAsPotentialOwnerByStatus(..) method in BPMS 6.0.x

Issue

• It has been observed that retrieving user's assigned tasks using getTasksAssignedAsPotentialOwnerByStatus(..) method in BPMS 6.0.x can take up to 5 seconds even on nominal hardware. Extra indexes were created (as listed below), but were of little or no use. Explaining queries could turn up 6 full table scans on the I18NTEXT table for each run of the query.

create index IDX_I18NTEXT_TASK_NAMES_ID on I18NTEXT('TASK_NAMES_ID');
create index IDX_I18NTEXT_TASK_SUBJECTS_ID on I18NTEXT('TASK_SUBJECTS_ID');
create index IDX_I18NTEXT_TASK_DESCR_ID on I18NTEXT('TASK_DESCRIPTIONS_ID');
create index IDX_PPLASN_POTOWNERS_ENTITY_ID on PEOPLEASSIGNMENTS_POTOWNERS('ENTITY_ID');
create index IDX_PPLASN_POTOWNERS_TASK_ID on PEOPLEASSIGNMENTS_POTOWNERS('TASK_ID');
create index IDX_TASK_PROCESSID on TASK('PROCESSID');
create index IDX_TASK_PROCESSINSTANCEID on TASK('PROCESSINSTANCEID');
create index IDX_TASK_STATUS on TASK('STATUS');
create index IDX_TASK_ARCHIVED on TASK('ARCHIVED');
create index IDX_TASK_WORKITEMID on TASK('WORKITEMID');
create index IDX_I18NTEXT_LANGUAGE on I18NTEXT('LANGUAGE');

However, with some relevant corrections made in the community jBPM 6.1.0-SNAPSHOT version, new load tests indicated much better results. Has there been any such improvement made to the community based jBPM 6.1.0-SNAPSHOT to improve such performance ? When would that be available in BPMS 6.x product?