odf.getAllUserGroups statement performance should be optimized

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The odf.getAllUserGroups query is heavily used in the application. On a very large dataset with hundreds of thousands of Groups even the smallest timing increase can add up.

STEPS TO REPRODUCE:

1. On a very large dataset with 2.2M records in cmn_sec_user_groups and 260k in cmn_sec_groups, run the below statement:

select sg.ID as group_id from cmn_sec_groups sg
left join cmn_sec_user_groups sug on sug.group_id = sg.id
where ( sug.user_id = 5000000 OR sg.is_hidden = 1 ) AND sg.is_active = 1

Expected Results: The query to run under 0.05 sec

Actual Results: Note the execution time with a good execution plan is 0.4 to 0.7sec

Environment

Clarity 16.2.2

Resolution

This is DE94822,  fixed in 16.2.3 and patch 16.2.2.1