Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drfonz
Starting Member
3 Posts |
 Posted - 2003-07-20 : 16:19:56
|
| I am working on a project at the moment which centres around a web-based instant messaging system (i.e. user A sends message to user B, user B replies, etc, etc). All messages are currently stored in one table, which now has approx. 3 million records (and counting), and they are experiencing some performance issues - particularly as there are often many simultaneous users (up to 2000). The proposed solution (by the company) is to change it so that there is one message table per user. As they have over 30,000 active users, and many new users sign up daily, this will mean 30,000+ tables in the database (and new ones created for each new user, etc).To me this seems absolutely bonkers, though I have heard of this sort of structure being employed before. I would appreciate anyone's opinions on this - is it a sound method? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-20 : 16:33:11
|
| Yes, it is bonkers. If you think you're having performance problems now, they'll only get worse with that many tables. Never mind even TRYING to program something like that. I don't know where you've heard of that method being used before, but they were smoking crack.First thing to do is to clean out messages from the message table. No point in keeping anything that's over 24 hours old in the main table, especially for an instant messaging app.And frankly, why is a database being used for this app anyway? Why do the messages need to be saved at all? |
 |
|
|
drfonz
Starting Member
3 Posts |
Posted - 2003-07-20 : 18:26:36
|
Glad I'm not alone in thinking it's bonkers. As for why messages are saved, the messaging is is part of an online - ahem - dating website ; so "conversations" happen over days, rather than minutes, and users will often wish to refer back to what was said some time ago! As far as I can see the problem could most likely be resolved with some judicious performance optimisation rather than a complete re-write of 40odd years of database theory. If anyone could point me towards some useful resources on this (aside from bol and sqlteam, which i already scour constantly) it would be most helpful!  ps. what the hell are you doing here on a sunday afternoon?;)Edited by - drfonz on 07/20/2003 18:35:39 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-20 : 20:03:27
|
| Strange - a short while ago I posted about a colleague who had created an access based bug reporting system which created new tables for each new user - but then he was a tester and wasn't meant to know anything about database design.You can only have 2,147,483,647 objects in a database - never thought that might be a problem.It is probably a problem that the users are querying the same tables as they are inserting to.You could split the table into an archive and current table.The new messages go to the current table and then are moved (overnight, trickled?) to the archive table.The archive table will then only have a single thread writing to it.It is up to you whether the users can read from the current messages table (union with the archive) - but this is so small now there shouldn't be any problems. It should also not need many indexes so insert performance should improve.I assume you don't update messages once they are inserted - if you do then redesign so that you don't as that is always going to cause problems.If necessary you can split up the archive tables maybe using a partitioned view onto different filegroups.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
drfonz
Starting Member
3 Posts |
Posted - 2003-07-21 : 07:29:21
|
| Well, I think I've managed to persuade them to re-think their strategy... Cheers for the tips folks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-21 : 09:16:38
|
| How about a partitioned view?Say about 10 tables underneath...while also using the archiving methodology...as another partioned view...And if it's a dating site...how long do the relationships last anyway?(Rob: SEX: Sure where is she?)Brett8-) |
|
|
|
|
|
|
|
|
One table per user?