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?
11 Replies
Just of the top of my head I would say you might want to shard your database.
There is probably other things you can do but sharding would probably bring the most significant gains.
Btw what database are you using?
I am using SQL server
But is it mysql, litesql, postgresql or something else?
It's from Microsoft
Mssql
Huh, heard of it once, and that is pretty much it.
Ya, can't help with much a side from recommending to try to shard it, or to move to a nosql database.
I assume the second is not something you wan't to do, but it is an option.
Company won't move to 2nd option
Ya I can definitely see why they wouldn't want to. I belive discord actually had a similar problem, and made a blog post about it.
https://discord.com/blog/how-discord-stores-trillions-of-messages#heading-4
How Discord Stores Trillions of Messages
Engineer Bo Ingram shares insight into how Discord shoulders its traffic and provides a platform for our users to communicate.
Ok I will read about it 🤕🤕
Are you running this query for every user every 5 minutes?
Because that's what it looks like to me, and if that's the case, then that's not gonna scale well
Storing new messages in something like redis, and then just wiping them from there after they got queried is also an option.
Mukomo also raised a good point, if it is possible to do something other than an exreamly large query every 5 minutes it should redistribute the load across time, which should help.
I think you can make use of a cache and a queue. In the cache, store the message, and when it’s “completed”, read it from the cache and push it on the queue. Listeners to the queue will send the notification (and probably eject the cached object). Perhaps this requires more system components than you might actually have in place.