Performance issue in Clarity due to high amount of notifications

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Clarity is performing slow, some of the pages are very slow to load. The below query is identified by the DBA as a culprit:

SELECT COUNT(*) as unread_notifications_count FROM CLB_NOTIFICATIONS WHERE (EVENT_TYPE IN
          (SELECT LOOKUP_CODE FROM CMN_LOOKUPS LOOKUPS WHERE LOOKUP_TYPE = 'NOTIFICATION_TYPE' and LOOKUPS.ID IN
          (SELECT FUNCTIONAL_AREA_ID FROM CMN_NOTIFICATION_DEF_OBJECTS WHERE IS_PUSH_ENABLED = 1 AND (OBJECT_TYPE_CODE != 'timesheet' AND OBJECT_NAME != 'thisTimesheet')
          UNION
          SELECT FUNCTIONAL_AREA_ID FROM CMN_NOTIFICATION_DEF_OBJECTS WHERE OBJECT_TYPE_CODE = 'timesheet' AND OBJECT_NAME = 'thisTimesheet'
          AND EXISTS (SELECT 1 FROM CMN_FEATURES WHERE CODE = 'F51696_ENABLE_BELL_NOTIFICATIONS_FOR_TIMESHEET' AND ENABLED = 1) ))
          OR (EVENT_TYPE = 'SCHEDULER_TYPE' AND ORIGINATING_OBJECT_INSTANCE_ID in (SELECT ID FROM CMN_SCH_JOB_DEFINITIONS WHERE JOB_CODE in
          ('CSV_EXPORT_ITEMS', 'ODF_OBJECT_COPY', 'BULK_PDF_EXPORT', 'RECALCULATE_ORDER'))))
          AND IS_READ = 0 AND RECEIVER_ID = ?

Environment

Any Supported Clarity Release

Cause

Upon checking we find that CLB_NOTIFICATIONS has many million rows and the Purge Notifications job has not been scheduled

Resolution

• Follow the Best practices on purging notifications in Clarity
• Schedule the job in batches to clear the records and bring them under 1M or less if possible
• Work with DBA to defragment the CLB_NOTIFICATIONS table at the earliest possibility
• Ensure the Purge Notifications job is scheduled regularly going forward