FVA fields kills document performance

[mrump]

We have been facing performance issues in our document managment for a while since we've updated from 10.6 to 11.1.

The symptoms are as follows:

- view/edit live documents works normally

- "view as of" for doucments is extremely slow (up to several minutes before a 10 node document opens)

- "view document differences" is extremely slow (up to several minutes before a 10 node document difference view opens)

- "Edit in Word" - workin with a single node is ok, but useing the chapter or entire document option is extremely slow (up to several minutes - sometime even errors in a timeout)

The behaviour was not consistant over all our segment and node types (some where extremely, while others worked much faster), so I took a deeper look into our setup and found this:

IMHO there is a general problem in the handling of FVA multi-valued user fields when it comes to historical operations. 

In our node setup of the affected segment types we have a FVA field "project authors" which is referencing a multi-value user field in the project backing item.
It's use was to allow editability rules on the nodes like .... and "project authors" = -current user- ....

I first removed all rules (editability and visibility) for fields and the node itself which had no effect.
But when i removed the "project authors" field from the node type's visible fields definition, 
I saw an incredible performance boost (seconds instead of minutes).

Re-adding the field decreased the performance instantly to the slow values i had before.

Removing the FVA field speed up the system again.

For cross checking I added a different FVA "project stakeholders"- again referencing a multi valued user field from the project backing item- , that was never part of the node type before.
The new field decreased the performance instantly to the slow values i had before.
Removing the FVA field speed up the system again.

Has anybody made the same experience?

I'd like to hear your thoughts

BTW:

Of course I initially made an case with ptc support (but after 2 week I still got no helpfull answer yet)

[MichaelChatel]

Hi mrump,

Is your Support case still open and being investigated, to reference? 

Are you using Oracle?  In regard to the FVA/performance issue, I think I recall Support seeing that enabling FVA fields as columns inside the document view significantly affected open document performance, so perhaps you could be seeing something like that?

I think in that case, some Oracle tuning/skipping nested loops was required.

[mrump]

Hi @MichaelChatel,

thx for the response.

YES we are using Oracle (as it was recommended by PTC years ago).

Just to be absolutely clear on this; the FVA field is not to be displayed in a (document-) view. Actually it is not even part of the node's presentation template.

Just having a multi-valued User FVA referencing your Project Backing Item in the node type's list of visible items is enough to trigger the problem.

PTC support provided a 1.st patch this morning (containing a modified oracle function definition).

My first tests show, that it had barely an effect at all.

At least it seems not to worsen the problem