Is timer good solution for this?

Question

Hi! I have application that use MSSQL database. Application have module that is using for sending messages between application users. When one user send message to another i insert message in database, and set message status to 1( after user read message database I update and set message status to 0).

Now,i am using system.timers.timer for checking message status, and if message status 1 user get alert that he has one message inbox.

The problem is that this application can be used by many users, and if timer run ever 5 minutes this gone slow application and database.

Is there any other solution to do this, with out runing timer?

Thanks!

Answer 1

Maybe you should look into having a "monitor" service, which is the only one looking at changes in the database and then sending a message to the other applications (a delegate) that data has updated, and they themselves should fetch their own data only when they get that message.

Answer 2

I don't think the solution using a timer which does polling is that bad. And 50 Users is relatively little.

Does each user run a client app, which directly connects to the database? Or is this a ASP.NET app? Or a service which connects to the db and notifies client apps?

If you have client apps connecting directly to the DB, I'd stay with the timer and probably reduce the timeout (the number of queries seems to be extremely low in your case).

Other options

1. Use SqlDependency/Query notifications MSDN
2. Only if your message processing logic gets more complex, probably take a look at service broker. Especially if you need queuing behavior. But as it seems, this would be far too complex.

I wouldn't use a trigger.

Answer 3

If checking always against the message table you can use add a column to your user table named: HasNewMessage, which is updated by a trigger on the message table

To illustrate it:

User 1 gets a new message Messagetable trigger sets HasNewMessage to 1 for user1

You then check every 5 minutes if user1 HasNewMessage (should be faster due to indexed user table)

If user1 looks into his mailbox you set HasNewMessages back to 0

Hope this helps