Adding a Configuration Item to Specify a Time Range When Purging Expired Messages

[hamedzsmm]

Description:
We are experiencing a significant issue with the Cap. The problem arises when the system attempts to delete expired messages. This operation frequently causes database locks, leading to performance degradation.

Current Behavior:
The following SQL query is used to delete expired messages in SQL Server:

(@timeout datetime, @batchCount int)
DELETE TOP (@batchCount)
FROM CRM.MessagingInbox WITH (readpast)
WHERE ExpiresAt < @timeout
AND (StatusName = 'Succeeded' OR StatusName = 'Failed');

This query often results in database locks.

Expected Behavior:
The system should be able to delete expired messages without causing significant database locks, ensuring smooth and efficient operation.

Proposed Solution:
We propose adding a configuration option to Cap that allows users to specify a time range during which expired messages can be deleted. For example, users could configure the system to delete expired messages only between 1 AM and 6 AM, thereby avoiding high load periods on the site.

Additional Context:
This issue is critical as it affects the overall performance and reliability.

[yang-xiaodong]

Which version are you using? In version 8.3.1, we have already added indexes for StatusName and ExpiresAt, and modified the delete syntax to use IN instead of OR. The delete expired message sql is guaranteed to hit the index.

[hamedzsmm]

We are currently using version 8.2.0. It's great to see that in version 8.3.1, you have optimized the query and created the necessary indexes. We will definitely update on our end.

However, do you think it would be beneficial to add a feature that allows users to specify a time range during which message deletion operations can occur? If this feature is deemed appropriate, it could be added to Cap. As a user, I would really appreciate having this feature.

#1614

[yang-xiaodong]

Hi,
After discussion not going to add this config item at this time as we haven't received the same feature request as you over the last few years, I've set this issue to backlog, if anyone needs it please vote!