How to send messages to large number of people and is there ways for doing it
Hey everyone, I need some advice on optimizing how we show notifications to users. Here's the scenario:
Current Setup:
Our database has over 1.5 million messages stored.
Every 5 minutes, we query the database to check for new messages for users and display them as notifications.
@DesignationID bigint,
@startDate datetime,
@endDate datetime,
@UserID bigint
AS
BEGIN
SELECT
displaymessage AS DisplayMessage,
'frmViewMessage' AS form_name
FROM pl_action_message pm
INNER JOIN pl_user u ON pm.sent_to = u.user_id
INNER JOIN pl_action_taken pa ON pm.action_taken_id = pa.action_taken_id
WHERE pa.status <> 'Completed'
AND u.designation_id = @DesignationID
AND pm.sent_date BETWEEN @startDate AND @endDate
AND u.user_id = @UserID
END
Problem:
The query is taking a significant amount of time to execute.
I've already implemented indexes and used the UPDATE STATISTICS query to improve performance, but there's no noticeable improvement.
Question:
How would you handle this scenario to ensure notifications are delivered efficiently without querying such a large dataset every 5 minutes?
Are there any best practices, techniques, or articles you can share to help optimize this process?
Current Setup:
Our database has over 1.5 million messages stored.
Every 5 minutes, we query the database to check for new messages for users and display them as notifications.
@DesignationID bigint,
@startDate datetime,
@endDate datetime,
@UserID bigint
AS
BEGIN
SELECT
displaymessage AS DisplayMessage,
'frmViewMessage' AS form_name
FROM pl_action_message pm
INNER JOIN pl_user u ON pm.sent_to = u.user_id
INNER JOIN pl_action_taken pa ON pm.action_taken_id = pa.action_taken_id
WHERE pa.status <> 'Completed'
AND u.designation_id = @DesignationID
AND pm.sent_date BETWEEN @startDate AND @endDate
AND u.user_id = @UserID
END
Problem:
The query is taking a significant amount of time to execute.
I've already implemented indexes and used the UPDATE STATISTICS query to improve performance, but there's no noticeable improvement.
Question:
How would you handle this scenario to ensure notifications are delivered efficiently without querying such a large dataset every 5 minutes?
Are there any best practices, techniques, or articles you can share to help optimize this process?
