Poor performance on exporting data into Excel due to AVP Settings

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

You are trying to download the project list with an attachment field included and the browser is timing out. No error message is received. You also tried to download the attachment field information using one of your custom portlets and it also times out. You have another user-defined portlet with the same columns and the export action is successful.

What is causing the slow performance or the timeout on the export action on the other portlets? You are using the Export to Excel (Data Only) option which allows us to export more than 300 rows of data

Steps to Reproduce:

1. Create a new user-defined portlet using the 'Project' Object as the data-provider
2. You have three user-defined attachment attributes configured in the list columns
3. Publish the portlet
4. Filter for more than 300 projects
5. Export to Excel (Data Only)

Expected Result: The export action should be successful and not time-out
Actual Result: The export action hangs or has a timeout and it is not successful

Note: This can happen on any object

Environment

Release: Any

Resolution

Comparing the configuration of the portlet to another portlet that had a similar configuration we found that the portlet [Options] setting for Attribute Value Protection (AVP) is configured differently.

In the use-case scenario described above, 3 user-defined attachment attributes were configured on a subpage with display conditions.

By default, when creating any new portlet that uses an Object as a data provider, the [Options] page setting for 'Attribute Value Protection' is set to 'Use display conditions and secured subpages to protect attribute values on this list'. This is the root cause of the poor performance based on several factors within the configuration of the environment.

Below are more details on how the application operates with the Attribute Value Protection (AVP) settings

Attribute Value Protection:

1. Use display conditions and secured subpages to protect attribute values on this list
   
   
   • The application will check each attribute column to determine if the attribute is on a secured subpage and check if the end-user has the appropriate right
   • The application will check each row of the attribute column to determine if the value for the attribute on the row meets the display condition criteria
2. Use only secured subpages to protect attribute values on this list
• The application will check each attribute column to determine if the attribute is on a secured subpage and check if the end-user has the appropriate right
3. Display all attribute values on this list
4. The application does not have to check for display conditions or secured subpage access rights - all data is shown on the list

When viewing the data in the application user interface, the performance is not impacted due to the limited number of rows displayed on a page which can range from 10 - 50 rows. However, when attempting to perform an export to Microsoft Excel or PowerPoint, the performance is longer due to the amount of data being checked by the application.

If option #1 is selected, the application will have to check each column and each row and if you have a large resultset, you will see it take longer to perform the export action.

Recommendations:

The best recommendation is to update the configuration of the AVP Settings to Improve Performance. Please refer to the following article:

How to Configure AVP Settings to Improve Performance

1. Educate your end-users on the use of performing an export action - to limit the amount of data based on filter criteria.
2. Use only Secured subpages instead of both secured subpages and display conditions. The display conditions impact performance the most due to the nature of checking each cell in the resultset.
3. Restrict the view to not allow personal configurations - this will prevent end-users from adding fields that are secured or have display conditions
4. Create a separate portlet that allows all attribute values to be displayed and give restricted access to this portlet for specific users that may have to export large result sets with the understanding that your users will be able to see all data regardless of the display conditions or secured subpage access.

Important:

All stock list views are pre-defined with option #1 'Use Display conditions and secured subpages to protect attribute values on the list'. If you are experiencing performance issues over various list views, it is recommended to modify the OOTB setting to "Use only secured subpages to protect attribute values on the list" as noted in Recommendation #2.

The important distinction to make is that display conditions are not security. They are a nifty way of hiding a subpage on an Edit properties view. Anyone using them for security should be using subpage security as it is FAR more efficient.