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 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

 DE94927 is fixed in 16.3.0 (Targeted to release November 2024) and also backported to 16.2.3 patch 1