2

I am in the process of creating an instant messaging system for my forum which has roughly 5,000 users. So far I have determined how the messages are going to be pushed to the user (comet) and I am currently attempting to determine the best method for storing messages within an SQL database. Even though SQL might not be the most efficient for this type of task, I am going to use it because I have a reasonable knowledge of it.

Upon doing a large amount of research including: Best way to store chat messages in a database? I have discovered that the two main methods for storing the chat logs are either: 1) Storing each individual message as an entry into the database with its own individual row and each message is retrieved by searching the chat id index for a specific id and returning all results sorted by their timestamp. 2) Each conversation has its own row within the database and the conversation messages cell is updated every time a new message is sent. To retrieve the message you search the chat id index for a specific id and return that specific chat.

When the user reloads the webpage the chat log is retrieved from the hard drive of the server and not the cache as the servers memory is relatively small, therefore low latency is important. I want the system to be able to handle roughly 1 million messages before the system needs to be reassessed to make it more efficient.

Will method number 1 be able to handle this many messages with a latency of less than a second or would method two need to be used? Also does any method have any important advantages in comparison to the other? Would method number 2 still be able to store metadata?

Thanks :)

Community
  • 1
  • 1
dominicansell
  • 85
  • 2
  • 10
  • 1
    If you have correct indexing, you could have billions of rows in a table and get keyed results in under a few miliseconds. – Dai Dec 08 '14 at 23:22
  • 1
    The idea of having a single row that contains an entire conversation in a single `nvarchar(MAX)` column that is frequently `UPDATE`D is ludicrous - whoever suggested it does not appreciate how database storage works under-the-hood. – Dai Dec 08 '14 at 23:23
  • Option 1 with correct indexing will perform without issues. If you're concerned about the number of records in a table causing issues, you could always archive completed chats to an archive table. – Ashley Lee Dec 09 '14 at 00:37
  • So now matter how many conversations there are and how many messages there are in each, with correct indexing method 1 will be able to work with a low latency. I didn't think that method 2 sounded a logical way to do it but I read it in multiple sources so I decided to ask. I will also look into developing an archive system for the database as mentioned. Thanks :) – dominicansell Dec 09 '14 at 08:15

0 Answers0