When adding a new Staffing record from Staff Grid the Investments Name lookup is slow

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When adding a new Staffing record from Staff Grid the Investments Name lookup is slow. Upon reviewing further the API call with action /v1/lookups/INV_BROWSE_NON_NPIO/lookupValues 

Steps to Reproduce

Pre-Requisite: The dataset should have more then 50k instance of (Project/Idea/Custom Investments) 

1. Login to Clarity and navigate to MUX --> Staff Grid
2. Click on + Icon to create an instance of Staffing
3. Start filling the mandatory attributes like Investment Name, Booking Status, Resource
4. While fetching the data on Investment Name the user need to wait for long time

Expected Results: Creation on instance on staffing grid should be reasonably faster. 

Actual Results: The attribute Investment name lookup drop down values takes long time and same can be found while using Investment name attribute in filter. Upon reviewing the trace the below query execution is the slowest. 

select *
from (select row_number() over ( order by name asc) row_num, count(*) over () num_rows , q.*
	from (
		SELECT INV.ID AS ID ,INV.CODE AS CODE ,INV.NAME AS NAME ,INV.ODF_OBJECT_CODE AS OBJECT_CODE ,INV.TYPE_NAME AS TYPE_NAME
		FROM (
			SELECT INV.ID, INV.CODE, INV.NAME, INV.ODF_OBJECT_CODE ,(SELECT CAP.NAME
				FROM CMN_CAPTIONS_NLS CAP
				WHERE CAP.TABLE_NAME = 'ODF_OBJECTS'
				and CAP.LANGUAGE_CODE = 'en'
				and CAP.PK_ID = OO.ID) AS TYPE_NAME
			FROM INV_INVESTMENTS INV JOIN ODF_OBJECTS OO on INV.ODF_OBJECT_CODE = OO.CODE LEFT JOIN INV_PROJECTS PRJ ON INV.ID = PRJ.PRID
			WHERE 1=1
			and 1=1
			and 2 = 2
			and ( (exists (
						select 'x'
						from CMN_SEC_CHK_USER_V0 r, ODF_OBJECT_EXTENSIONS OE, ODF_OBJECTS O, CMN_SEC_OBJECTS S
						where S.ID = r.OBJECT_ID
						and r.USER_ID = XXXX
						and r.PERMISSION_CODE = 'InvestmentViewManagement'
						and INV.ID = r.OBJECT_INSTANCE_ID
						and O.CODE = OE.OBJECT_CODE
						and OE.EXTENSION_CODE = 'inv'
						and S.OBJECT_TYPE_CODE = 'RECORD'
						and S.COMPONENT_CODE = 'INV'
						and S.OBJECT_CODE in (O.RIGHT_CODE, CONCAT('odf_cst_', O.RIGHT_CODE))
						and S.OBJECT_CODE not in ('service', 'asset', 'product', 'application', 'other')))
				or exists (
					select 'x'
					from ODF_OBJECT_EXTENSIONS OE, ODF_OBJECTS O, CMN_SEC_CHK_USER_R_V S
					where S.USER_ID = XXXX
					and OE.OBJECT_CODE = INV.ODF_OBJECT_CODE
					and O.CODE = OE.OBJECT_CODE
					and OE.EXTENSION_CODE = 'inv'
					and S.OBJECT_TYPE = 'RECORD'
					and S.PERMISSION_CODE = 'InvestmentViewManagement'
					and S.COMPONENT_CODE = 'INV'
					and S.OBJECT_CODE in (O.RIGHT_CODE, CONCAT('odf_cst_', O.RIGHT_CODE))
					and INV.odf_object_code not in ('service', 'asset', 'product', 'application', 'other')) )
			AND INV.ODF_OBJECT_CODE NOT IN ('application','asset','other','service','product')
			AND NVL(PRJ.IS_TEMPLATE, 0) = 0
			AND NVL(INV.PURGE_FLAG, 0) = 0
			AND INV.is_active = 1 ) INV
		WHERE 1=1
		and ( NLS_UPPER( INV.NAME ) like NLS_UPPER( '%' ) ESCAPE '\'
			OR NLS_UPPER( INV.CODE ) like NLS_UPPER( '%' ) ESCAPE '\'
			OR NLS_UPPER( INV.TYPE_NAME ) like NLS_UPPER( '%' ) ESCAPE '\' ) ) q) q
where q.row_num between 1
and 25 order by q.row_num

Environment

Clarity Version 16.2.x

Cause

DE155721

Resolution

DE155721 is fixed in Clarity 16.3.0 and performance of the lookup (investment name) is improved. 

Workaround. Use the investment staff tab to add resources to investments.