Imixs Workflow ...the open source workflow technology for business applications

Engine

Performance

In large databases with a lot of workitems there can occur a performance issue which slows down the response time of an application in some situations. The reason for this issue is the default database schema. This schema is generated by the application server the first time the Imixs Workflow engine where deployed.

This is a typical example with more than one JOIN clauses that can result in a slow-down of the response time :

SELECT DISTINCT environment FROM Entity AS environment
 JOIN environment.textItems as n 
 JOIN environment.textItems as v
 JOIN environment.textItems as c
 AND environment.type = 'WorkflowEnvironmentEntity'
 AND n.itemName = 'txtname' AND n.itemValue = 'environment.profile'
 AND v.itemName = '$modelversion' AND v.itemValue = 'public-de-standard-0.0.1'
 AND c.itemName = 'case'
 ORDER BY c.itemValue

To fix this problem it is necessary to add additional indices to the database tables generated by the application servers OR-Mapper. This can be done with the database tools provided by your database vendor. For MySQL use the "MySQL Administrator Tool"

The most important tables where an additional index should be added are the database tables "ENTITY", "TEXTITEM", "INTEGERITEM" and "CALENDARITEM". This tables typical holds the most data rows. Useful default indices to the columns "TYPE", "ITEMVALUE" and "ITEMNAME" are not created per default. So to add the necessary index manually you run call the following SQL commands: 

 ALTER TABLE `mydatabase`.`ENTITY` ADD INDEX `index1`(`TYPE`);
 ALTER TABLE `mydatabase`.`TEXTITEM` ADD INDEX `index1`(`ITEMNAME`, `ITEMVALUE`);
 ALTER TABLE `mydatabase`.`INTEGERITEM` ADD INDEX `index1`(`ITEMNAME`, `ITEMVALUE`);
 ALTER TABLE `mydatabase`.`CALENDARITEM` ADD INDEX `index1`(`ITEMNAME`, `ITEMVALUE`);

After adding the index the response time should be nice again.

Note:
It can be necessary to add also indices to other tables/columns created by the OR-Mapper. To find out useful indices contact your database administrator or use Query analyser tools provided by your database vendor.