We are experiencing performance issues in our OutSystems O11 production environment. During our initial investigation, we observed that the GetNotifications Aggregate, which retrieves records from the Notification entity, is taking considerable time to load.
The Aggregate is configured with a Max. Records limit of 50, but the notification data is still taking longer than expected to load.
We would like assistance in identifying the root cause and determining the appropriate optimization approach.
Could you please help us investigate the following?
How can we identify the SQL query generated by the GetNotifications Aggregate and analyze its execution time?
Could the Aggregate's filters, sorting, joins, or entity indexes be contributing to the slow loading?
How can we identify expensive database queries and review their execution plans?
What are the recommended OutSystems O11 best practices for optimizing notification retrieval?
Could you review the attached screenshots and recommend the appropriate troubleshooting steps and corrective actions?
@SreenivasuluReddy Lingala : May I know why there is no join between To(Employee) and Notification ?
Looks like you are trying to retrieve the 50 most recent unread notifications for a specific user, including information about the employee who sent each notification. However the query does not actually connect the notifications to the specified recipient. In the current implementation it creates a CROSS JOIN.
Have an inner join between Notification and To should resolve the issue by ensuring that only notifications addressed to the specified user are returned.
Hi @Siya thanks for your response
I have already implemented the JOIN and checked the indexes on the IsRead and DateTime columns. However, the same issue persists, and the SLOWSQL impact is still occurring in the production environment.
@SreenivasuluReddy Lingala : Thanks for the update. A couple of things to check:
Hi Sreenivasulu,
Siya spotted the main culprit: without a join condition between Notification and the "To" Employee, the aggregate becomes a cross join, so the database builds Notifications × Employees rows before your filter and Max Records apply. Max Records = 50 only limits what is returned, not what the database has to process. Fix that first, then use the steps below to confirm and harden it, which also answers your four questions.
1. See the SQL and how long it takes - In Service Studio, select the aggregate: the Executed SQL property shows the exact query the platform generates (physical table names like OSUSR_xxx_NOTIFICATION). - In Service Center > Monitoring > General, filter by module SLOWSQL: every query above the slow-query threshold is logged there with its duration and the action that ran it. That tells you whether it's really the query or something else (e.g. many calls).
OSUSR_xxx_NOTIFICATION
2. Execution plan Copy the Executed SQL, replace the parameters with real values (e.g. a real employee Id), and run it in SQL Server Management Studio with "Include Actual Execution Plan". Look for Table/Clustered Index Scans on the notification table and for a big Sort operator; both mean an index is missing. Do this against a copy or at a quiet time, not during peak load.
3. Filters, sort and indexes For "latest unread notifications for a user", the query filters by recipient and read flag and sorts by date. Create one composite index on the Notification entity in that order, e.g. ToEmployeeId, IsRead, CreatedOn. The database can then jump straight to that user's unread rows already in date order and stop after 50, instead of scanning and sorting the whole table. Keep the joins to Employee (sender) as "Only With" on the Id, which uses the primary key.
ToEmployeeId, IsRead, CreatedOn
4. Best practices for notifications - Fetch only what the screen shows; avoid binding unused attributes or big Text fields like a full message body in the list. - If you show an unread badge, don't count the whole table on every load. Keep an UnreadCount on a small per-user record that you increment when a notification is created and decrement when it's read; the badge then costs one primary-key read. - Archive or delete old read notifications with a Timer (e.g. older than 90 days) so the table stays small. - If the list auto-refreshes, check the interval; polling every few seconds from every open browser multiplies the cost.
UnreadCount
A quick way to validate: after adding the join and index, the Executed SQL should show an INNER JOIN on the recipient, and the SLOWSQL entries for that action should disappear.
Hope this helps!