Custom parameterized lookup slow performance in MUX Grid

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

A custom parameterized lookup NSQL is slow when added to MUX Grid or object properties page. You have added a large query to retrieve parameters with a complex logic and multiple tables involved, and it contains cross joins

Environment

All Supported Clarity Releases

Resolution

• As a best practice, always use a very optimized SQL for all parameterized lookups
• Broadcom Support recommends to avoid cross joins with Clarity parameterized lookup 
  • The issue with SQL queries that are not optimized is that they may stop performing well at any time after upgrade, data increase etc.
  • Cross joining tables, especially large ones may create a large intermediate result set, which is known to cause slow execution time
  • Review the query logic and business need, and determine the best way to add explicit joins to the lookup query
• Also make sure that if you use the data for two different logical purposes within the same overall resultset, you consider splitting into two lookups depending on what you are trying to achieve
  • Example: For default allocation and specific allocation