Performance / pagination on Custom Script GEL execution, Data Extract (Best Practices)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Running large GEL scripts, which best practices have to be followed in order to avoid issues such as server slowdown, long running database queries and in some rare cases OutOfMemory (OOM) and application outage.

Environment

All Supported Clarity Releases

Resolution

For large GEL Scripts performing data extracts, follow Broadcom's best practices below:
 

• Always paginate the queries and the results - loop through the records to request and return results in batches
• When you paginate, set a proper amount, we recommend not more than 100MB memory to be taken by the GEL script.
  • This would usually be around 5k of records in a single request. We do not recommend going above this value
• Before going to Production, perform a thorough testing on a lower environment and ensure the memory is not affected and there is no slow running queries or blockings on the database
• If too much data is expected to be returned by the process, then we recommend:
  • Using a custom report for the data instead
    or
  • Exporting the results to a file on the FTP where it can be accessed (along with paginating)