topic performance issue with candidate tasks in Alfresco Archive

performance issue with candidate tasks

miroslav_slobod — Tue, 03 Apr 2012 10:50:23 GMT

Hello,I am testing Activiti framework from performance perspective. Actually I found problem with candidate tasks query performance if I have about 300 000 process instance in database. Problem is with this lines of code where I want to find out CANDIDATE tasks for concrete userrg.activiti.engine.

Re: performance issue with candidate tasks

trademak — Tue, 03 Apr 2012 11:20:51 GMT

Hi,

Interesting. Could it be some sort of index problem?
Can you compare the Oracle query path between the ASSIGNEE IS NULL and the ASSIGNEE IS NOT NULL query statements?

Best regards,

Re: performance issue with candidate tasks

miroslav_slobod — Wed, 04 Apr 2012 08:57:43 GMT

Hello,
I am not a database expert, but I tried to add some database indexes - performance did not improve. There are attached two explain plans from Oracle SQL Developer.

I am not sure if this is what you requested.

Best regards.

Re: performance issue with candidate tasks

miroslav_slobod — Tue, 10 Apr 2012 10:23:37 GMT

Some idea?

Re: performance issue with candidate tasks

heymjo — Thu, 03 May 2012 06:26:15 GMT

Interesting, we're using Oracle as well so i had a look into this. It turns out that Oracle does not always use the index for a NULL column value in the where clause. See here http://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search. It says that you could solve the problem by adding a constant to the index:

CREATE INDEX idx_null ON test(object_id, 1);

Re: performance issue with candidate tasks

ronald_van_kuij — Thu, 03 May 2012 19:49:24 GMT

Great catch…