ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Extremely slow performance while reading the data from DWH_INV_SECURITY_V using ODATA API in PostgresSQL in comparision with Oracle

book

Article ID: 200265

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

SUMMARY: Extremely slow performance while reading the data from DWH_INV_SECURITY_V using ODATA API in PostgresSQL in comparison with Oracle 

STEPS TO REPRODUCE: 

1. Prerequisite to reproduce the problem is to have medium size dataset with around 8+ million record in view DWH_INV_SECURITY_V
2. Login to a system and navigate to Administration --> General Settings --> Datawarehouse ODATA SERVICE
3. Use the DATA WAREHOUSE ODATA V4 ENDPOINT URL and connect via Postman 
4. Perform a GET Call https://cppmbixxx.ondemand.ca.com/api/odata4/cppmxxxxtDataSourceV4/DWH_INV_SECURITY_V

Expected Results: The API get call DWH_INV_SECURITY_V should be able to fetch records at the same time it used to take in oracle 

Actual Results: The API get call DWH_INV_SECURITY_V takes too long in PostgresSQL

 

 

Cause

Defect DE63862

Environment

Release : 15.8.x, 15.9.x, 16.x 

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

  • We have exposed below tables and use the below tables for your reporting till the view DWH_INV_SECURITY_V is optimized 

Attachments