Frequent Database Query for Instant Message

Question

I am creating an Instant Messaging application for our department. The features of this application are:

1. The messages will be stored in a database
2. The messages may be sent to one, multiple, or all users/locations
3. The logged in user will be able to see a history of the messages they are included in.

My question: is it appropriate to constantly query the database from each client - there should be less than 20 clients running - say every 15 - 30 secs or so? I have seen examples of a server/client messaging app using tcipclient but am not familiar with that subject. So I thought querying the database might be the approach I could go with. What are the ramifications of performing these queries so often? I'm also looking at sqldependencies??? Should I really go back to and try and learn tcip technology?

Thanks

Answer 1

If you know that you will always have of the order of tens of clients but not of the order of thousands of clients, then polling will work just fine, and you do not have to poll every 15 seconds, (it would be unusable if you did so,) you can poll every 100 or 200 milliseconds, so chatting will appear instantaneous.

Just make sure that each polling operation is as simple as possible. The simplest operation you can do is this:

SELECT * FROM chat_log WHERE chat_log.id > ? where id is your IDENTITY primary key, and ? is the last id that your client has seen so far from the server. Therefore, if there are no new chat messages, no rows are retrieved. With every row retrieved by a client, update the largest id that the client has seen so far, and you are good to go.

I have done it and it works like a charm.

From a technical point of view polling is a very ignoble technique, but in many situations it can be a practical compromise which may yield good enough results with very little development. (The alternative would be to create a proper chat server which sends push notifications to the clients, good luck with that.)

Answer 2

If its less that 20 clients (20 select queries every 20 seconds + some writes), SQL Server will have no issues to process these messages.

Selection of tools and technology depends on your actual requirements. (size of messages, allow file transfers, delete/edit messages...)

I can suggest few options to improve performance,

• Reading Messages - You can use Caching (e.g. Azure Redis Cache) for recent messages (last 30days). You can come up with background cache update strategy to make sure it's continuously updated with new messages. Read messages will call the cache first, it will hit the database only if there is a cache miss.

Also you can create a local message cache (client side) which will dramatically improve performance for end user. You can create a SQLite for this (like Skype does. Win + R -> %appdata%\skype -> folder -> main.db)

Or else you can simply have an Archive table in your db where a scheduled (every 24 hours) background process archives messages older than 14/30 days. So you will have recent messages

• Writing - Writing messages will be chatty, rather than directly updating the database you can use a Message queue (Azure Message Queue, Rabbit MQ.. etc). Then you can have another process to write messages to the database.

Each technology selection will have it's own cost, pros and cons and learning time. Therefore start simple and leave room to scale later.