Staff Record finding query optimization

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Query that runs on Staffing to get the objects for the records is running relatively slow on large datasets with >1M records in PRTEAM and >100K records in INV_INVESTMENTS

 

STEPS TO REPRODUCE

1. On a very large customer dataset with prteam > 1M and inv_investments >100K have multiple users access Staffing pages

Expected Results: The Staffing record finding query to be taking under 0.06 sec

Actual Results: The query below is taking up to 3 sec

 

select oo.id, oo.code

from odf_objects oo

where exists (select :"SYS_B_0" from prteam t

join inv_investments ii on t.prprojectid=ii.id

where ii.odf_object_code=oo.code)

and oo.code not in (:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")

Environment

Clarity 16.1.x, 16.2.x

Resolution

This is DE94927, fixed in 16.3.0 and targeted in 16.2.3 patch 1