SP2 performance bottleneck: modifiying the type of workflow on a leaf takes dozens of minutes - DB mainly impacted by WorkflowService.getUnlinkedContentObjects method

Hi Thomas,

I saw yestereday your committ regarding some workflow optimization for the sitemap display. Indeed I just tested them on the 10'000 pages DB and the second access looks like quite better (and the third one is even better). I know only have some problems with the Zimbra Javascript which really takes time and use the local CPU in order to display complex trees. This gives a "feeling" of being slow which we will need to look at.

However I know have another problem when changing a type of workflow on a leaf object. On the 10'000 pages, I went on the main_content of page 2 (so should be a leaf of our Jahai tree as there is no sub-objects) and modfied the default workflow (no workflow) to the direct workflow + break the workflow inheritance on this page (quite a simple request that users will do a lot = putting a distinct workflow on a news or similar actions).

The process is running for the last 20 minutes. My DB is on another server and is running at 100%. My JVM is doing nearly nothing (5%).

So I do not understand why we need to parse the whole tree or modify half the DB in order to put a distinct workflow on a single container which represent a leaf in our tree (I would understand it if I modified the workflow type of the home page and such a change would have impacted the whole 10'000 pages). So what the problem here?

Else, as mentionned in another JIRA for the SP2, we really need to put such time consuming command on the dedicated processing server as no user will wait for 20 minutes before closing his pop-up or killing his browser.