select * from INV_INVESTMENTS is slow with SQL Curve

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

• Running a basic select * from INV_INVESTMENTS query on 8k-10k records is taking over 30-40 seconds.
• We have verified that it is not the application or network by going directly into the Clarity database server and running a simple select statement.
• We are seeing this issue in different Clarity environments
• DBAs state there is no CPU/memory constraints.
• The database is re-indexed nightly and stats are taken daily which show no apparent cause to this slowdown.
• We have tested this on multiple tables in multiple environments and it is impacting all Clarity databases in the same way.

Environment

Release : Any

Resolution

• This is an expected behavior for all tables containing SQL Curve attributes: INV_INVESTMENTS, PRTEAM, PRASSIGNMENT, FIN_COST_PLAN_DETAILS etc.
• This timing increase started happening in Clarity as we have added SQL Curve attributes as a new feature
• The timings are comparable to a vanilla environment
• It is not expected to cause any performance issues as the product already runs selects in which the columns are called explicitly and never just an open select.
• Broadcom Support recommends never to run an open select on any of the tables that contain SQL Curves. Best practice is to always call the columns explicitly