Slow custom portlets on MSSQL Compatibility 130 and security view ODFSEC_INV_V2

Products

• Clarity PPM On Premise

Issue/Introduction

STEPS TO REPRODUCE:

1. Use a large MSSQL dataset with 15.7.1 and Compatibility set to 130
2. Connect to Clarity as admin user
3. Go to security.caches and select Flush All
4. Now go to Home - <Portlet page> with this custom portlet

Expected Results: The custom portlet to open in a reasonable time (5-10 sec maximum)

Actual Results: The portlet loads for 1 min 30 sec

When setting Compatibility Mode on the SQL server back to 110, the portlet loads significantly faster (under 5 sec) . 120 is also slow (55 sec) not as much as 130. Only Compatibility level 130 is supported on Clarity 15.7.1

Environment

Release : 15.7.1

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Resolution

This is logged as DE53948. Engineering approves using Compatibility 110 as exception with SQL Server 2016