MSSQL Project and Idea list very slow on Compatibility 130/150

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Performance Issue. Project and Idea list page is very slow on SQL Server in Clarity Classic UI. 

Steps to Reproduce: 

1. Use a large MSSQL dataset with multiple partitions set up(see environment below)
2. Connect to Clarity as admin/admin
3. Go to security.caches and select Flush All
4. Now go to Home - Projects or Home - Ideas 

Expected Results: The lists to open in a reasonable time (5-10 sec).

Actual Results: Project list and Idea list take anytime from 50 sec - 2.2 min to load for the first time. Subsequent load is 1-5 sec.

Environment

Release : 15.9.2, 15.9.3

Resolution

This is DE61778, fixed in Clarity 16.0.0

Workaround: Change Compatibility levels. When setting Compatibility Mode on the SQL server back to 110, the list loads significantly faster (5-10 for customer and 25 sec for us) . 120 is also slow as 130 and Compatibility 150 is very slow as well.