|
Hi gurus, I'm creating a web application for the internet with scalability in mind. In my application, I'll have forums used by different groups of people (each group will have their own forums). I will start from 1000 or 2000 groups with the potential to grow until 5000 groups (it's guaranteed that I won't exceed that number of groups, 5000, based on the nature of my application). I was thinking that having all the forum posts in one table will cause many problems like having a very large index and slow search among other problems (as I want to have many indexes on the table like the PostID, ForumID, GroupID and PostDate), so, I was thinking that it might be better to have a separate table for each group's forum posts in order to have small indexes so that I have faster searches and inserts take less time (esp. that some groups are expected to have a large number of posts per day) and also to be easier to move the tables to other database servers in case the application grows and so the web farm. Now I'm really confused how to design my DAL.
Assuming that the structure of the forum posts table is like that (this is just a simplified structure not the real one):
CREATE TABLE ForumPosts_x
(
PostID INT IDENTITY(1,1) PRIMARY KEY,
ForumID INT NOT NULL, -- which refers to the ForumID in another table named Forums which includes all forums for all groups
ParentPostID INT NULL,
PostSubject NVARCHAR(200) NOT NULL,
PostText NVARCHAR(5000) NOT NULL,
PostDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
Note that there's no group ID as x in the table name will be the group id e.g. ForumPosts_19 for group id 19
Now as to designing my DAL, should I:
1. Create stored procedures with dynamic SQL and pass the group id to the procedure i.e. use EXEC and sp_executesql (there's an interesting article on the subject here: http://www.sommarskog.se/dynamic_sql.html)
For example:
CREATE PROCEDURE InsertForumPost
@GroupID INT,
@ForumID INT,
@ParentPostID INT,
@PostSubject NVARCHAR(200),
@PostText NVARCHAR(5000)
AS
DECLARE @tablename NVARCHAR(50), @sql NVARCHAR(4000)
SET @tablename = N'ForumPosts_' + @GroupID
SET @sql = N'INSERT INTO dbo.' + quotename(@tblname) +
' (ForumID, ParentPostID, PostSubject, PostText) VALUES (' +
'@ForumID, @ParentPostID, @PostSubject, @PostText)'
EXEC sp_executesql @sql, N'@ForumID INT, @ParentPostID INT, @PostSubject NVARCHAR(200), @PostText NVARCHAR(5000)', @ForumID, @ParentPostID, @PostSubject, @PostText
2. Create the procedures with static SQL for each group. i.e. each group has its own set of procedures which would make us have a large number of procedures
For exmaple, the procedure for inserting a new forum post for GroupID #19 would be:
CREATE PROCEDURE InsertForumPost_19
@GroupID INT,
@ForumID INT,
@ParentPostID INT,
@PostSubject NVARCHAR(200),
@PostText NVARCHAR(5000)
AS
INSERT INTO dbo.ForumPosts_19
(ForumID, ParentPostID, PostSubject, PostText)
VALUES
(@ForumID, @ParentPostID, @PostSubject, @PostText)
3. Use SQL text directly in my code, C# in my case (which I'm highly considering but a little concerned about how to execute multiple SQL statements - as you have in stored procedures - without having to call ExecuteNonQuery() multiple times, which I believe could affect performance)
4. Drop the whole thing and stick to using one table for all the groups
What would you do if you were designing such application? Any suggestions are highly appreciated...
|
|
|
|
|
Trust your database server. In general having loads of indexes will slow your inserts, not your selects. You won't need to do any partitioning unless you have a _lot_ of data.
You can send multiple queries and get multiple responses in one round trip.
The stored procedures aren't giving you any benefit here - its just creating noise.
If I was designing this app i'd probably go with a Thread table inheriting Post, and giving Post a reference to the parent Thread (and possibly also the Post that was replied to - if you wanted to track that). Add your Thread table referencing Group as well for your groups. That lets you pull whole threads out off one index in one select, and Threads for each Group. Then I'd use Diamond Binding to handle my DAL...
|
|
|
|
|
Mark Churchill wrote: Then I'd use Diamond Binding to handle my DAL
ROTFLMAO You so had me until that part 
led mike
|
|
|
|
|
Well *I* don't have to weigh up cost/benefit because I can click a button and get a license 
|
|
|
|
|
Hi Mark, thanks a lot for your help, but would you mind explaining in more details?
Mark Churchill wrote: You can send multiple queries and get multiple responses in one round trip.
Well, actually I doubt this could be of much benefit in my case as I'm creating a web application so my queries will always be based on user actions so there's no way to send multiple queries at the same time in my case.
Mark Churchill wrote: The stored procedures aren't giving you any benefit here - its just creating noise.
Could you explain more please? I understand that you want me to go with one table so why not use sprocs in that case? We won't have the problem of caching query plans for every table as we're going to use only one table.
Mark Churchill wrote: If I was designing this app i'd probably go with a Thread table inheriting Post, and giving Post a reference to the parent Thread (and possibly also the Post that was replied to - if you wanted to track that). Add your Thread table referencing Group as well for your groups. That lets you pull whole threads out off one index in one select, and Threads for each Group. Then I'd use Diamond Binding to handle my DAL...
I'm a little lost here, what exactly do you mean by inheritance here? I was going to use a ParentPostID INT NULL field in the ForumPosts table (see the CREATE TABLE section in my original post) which will refer to the thread, is that what you mean? I was also going to index that field so that I can find threads and replies fast if this what you mean by pulling out the threads.
So I was exactly going to index the PostID, GroupID, ParentPostID and PostDate fields (this is why I thought about separating the data into tables, one table for each group, as I thought I would have too many indexes, 4 indexes as you can see, and this could affect the inserts performance tremendously)
By the way, if one table will be used, the PostID will be by group not an identity field. I was only going to use identity fields if had a table for each group but not with a single table (this is done for scalability sake, to be easy to move data to other databases or even tables with the same structure in the same database), I'll have another table with the last id used for every group e.g.
CREATE TABLE LastUsedID
(
GroupID INT NOT NULL,
LastUsedID INT NOT NULL
)
Thanks again for all your help, waiting for your reply...
|
|
|
|
|
I wrote: You can send multiple queries and get multiple responses in one round trip.
This was in reference to "...which I'm highly considering but a little concerned about how to execute multiple SQL statements..." - this isn't anything to worry about. A query isn't restricted to multiple statements - so you can send "select foo; select baz" in one ExecuteDataSet() and get back a DataSet containing multiple DataTables.
I wrote: The stored procedures aren't giving you any benefit here - its just creating noise.
The stored procedures were just performing basic CRUD operations. SQL server will cache the execution plan for your ad-hoc queries anyway. For a simplistic view, stored procedures are for providing abstraction/code reuse rather than performance (some would also say they help with security).
Waleed Eissa wrote: I'm a little lost here, what exactly do you mean by inheritance here?
A thread is basically a post that also has some extra information, like a title, a group it belongs in, etc. Say your database structure has a table, Product (Id, Description) and ServiceProduct(Id, CostPerHour) with ServiceProduct.Id being a foreign key to Product.Id. This defines that for every set of ServiceProduct data there is Product data, meaning ServiceProduct inherits Product, which is pretty analogous to how inheritance relationships work in code. This can be handy.
Using a ParentPostId field makes it difficult to pull a whole thread out the database. Given a parent post I would have to do an index scan to get the 2nd post, then again to get the 3rd, etc.
Say you have this setup:
Post (Id, Author, BodyText, TimePosted, ParentThreadId) and Thread (Id, Title, GroupId)
Thread.Id is a fk to Post.Id (inheritance)
Post.ParentThreadId is a fk to Thread.Id (reference)
This means that I can easily select threads in a group (Thread by GroupId), Posts in a Thread (Post by ParentThread).
If you are feeling uncomfortable with the inheritance relationship, then you could just have a Thread table that acts as a bit of a stub to group posts.
It might be worth having a look at how forums like phpbb handle their database structure (considering I'm coming up with this on the fly).
I'm not comfortable with the LastUsedId. It seems incredibly unlikely you would approach the 4 billion odd posts that just an int would provide. SQL Server could handle that kind of indexing using the processor in my phone - you'd be out of disk before you ran out of primary keys - and if you need to partition, just move everything out by GroupId - having holes in your index isn't an issue.
Insert performance isn't an issue for you - your users are reading and searching much more than they are posting. The more indexes the better - every millisecond you spend updating an index is going to save you a hundred of net lookup time
|
|
|
|
|
Mark Churchill wrote: I wrote:
You can send multiple queries and get multiple responses in one round trip.
This was in reference to "...which I'm highly considering but a little concerned about how to execute multiple SQL statements..." - this isn't anything to worry about. A query isn't restricted to multiple statements - so you can send "select foo; select baz" in one ExecuteDataSet() and get back a DataSet containing multiple DataTables.
Well, I'm sorry, I probably should've explained it more clearly, actually what I meant here is that when you use stored procedures you can easily use many sql statements in the same procedure
for example:
begin transaction
insert into foo
update foo2 set ..
.. etc
This is very easy with stored procedures but I guess not so easy with ad-hoc sql statements, this is what I meant to say
Mark Churchill wrote: A thread is basically a post that also has some extra information, like a title, a group it belongs in, etc. Say your database structure has a table, Product (Id, Description) and ServiceProduct(Id, CostPerHour) with ServiceProduct.Id being a foreign key to Product.Id. This defines that for every set of ServiceProduct data there is Product data, meaning ServiceProduct inherits Product, which is pretty analogous to how inheritance relationships work in code. This can be handy.
Using a ParentPostId field makes it difficult to pull a whole thread out the database. Given a parent post I would have to do an index scan to get the 2nd post, then again to get the 3rd, etc.
Say you have this setup:
Post (Id, Author, BodyText, TimePosted, ParentThreadId) and Thread (Id, Title, GroupId)
Thread.Id is a fk to Post.Id (inheritance)
Post.ParentThreadId is a fk to Thread.Id (reference)
This means that I can easily select threads in a group (Thread by GroupId), Posts in a Thread (Post by ParentThread).
If you are feeling uncomfortable with the inheritance relationship, then you could just have a Thread table that acts as a bit of a stub to group posts.
It might be worth having a look at how forums like phpbb handle their database structure (considering I'm coming up with this on the fly).
I like your idea about having a separate table for threads, I think this can speed things up as we can have less indexes on the same table, it might just be harder to maintain though as you have the data in two tables but I still like the idea.
Mark Churchill wrote: I'm not comfortable with the LastUsedId. It seems incredibly unlikely you would approach the 4 billion odd posts that just an int would provide. SQL Server could handle that kind of indexing using the processor in my phone - you'd be out of disk before you ran out of primary keys - and if you need to partition, just move everything out by GroupId - having holes in your index isn't an issue.
Actually this wasn't meant for avoiding reaching the maximum limit for int as it's large enough (by the way the max is 2 not 4 billions as int is signed), it's intended for scalability to make it easier to move data to different databases (assuming a group has way too many posts and it's making the table too large so you move the data of this group into a separate database), so that you don't have to worry about the correct value of the seed for the identity field.
Thanks for all your help...
|
|
|
|
|
If you are using SQL Server 2005 have a look at partitioned tables, partitioning by group and date - this gives very good performance.These are single tables, but physically split on the columns you define, allowing the underlying files to be located on different physical disks.
If you are using SQL Server 2000 take a look at partitioned views - not quite so friendly as partitioned tables, but good never the less. This is really a view over multiple tables, so table structure changes are a bit of a pain, but done properly you can insert into the view and it will add the record to the correct underlying table.
In either case you will be able to have a single stored proc to insert, and, although SQL Server will cache ad-hoc sql execution plans I would be very reluctant to use anything other than stored procs for data access. They provide a good degree of security against sql injection and are a single source of data, so any changes are abstracted from your code.
Hope some of this makes sense and helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi There
Thanks for a gr8 site. 
One of the most important aspects of being a developer is testing. No matter what the language is. And sometimes there are a few bugs that <i>slip</i> in and my application goes to production.
I need to improve on my Testing and Devepopemtn tech. Do any of you know of any books (hard/soft copy) that can help developers in this aspect?
I would really appreciate it.
Thanks.
Chris
|
|
|
|
|
Try google?
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
I am collaborating on a Visual Studion 2005 project with a German colleague. The application is being published in Germany, but he (in Germany) and I (in the US) work on different parts of the overall project to suit the needs of our constituency.
We have noticed a new problem lately. He wrote a routine to create a text file that we can both use. During the running of the code, however, I get a trapped error trying to create the file, while in Germany the routine works fine.
Stepping through the sub-routine it turns out the error is being generated because when the application is published to the German server, a file path to my colleague's Documents and Settings folder is being referenced. The program only shows me this in debug mode, and neither of us is sure how a Visual Studio program written in VB stores this kind of data.
Is anyone aware of the mechanics of this?
|
|
|
|
|
Mike Nelson wrote: I get a trapped error
So you don't think the error message might help us to help you? Is that why you didn't post it?
Mike Nelson wrote: is published to the German server
Is this an ASP.NET application?
led mike
|
|
|
|
|
This is primarily a desktop application but is published to an ftp site so that each time a user starts the app if there is an internet connection, it goes and searches for updates to the program.
|
|
|
|
|
Ok it's a desktop app. that helps. Now what about the error message?
led mike
|
|
|
|
|
Sorry for the delay in responding -- I'm a one man shop and got tied up in an entirely different project. Here's what I get when I step through the relevant subroutine:
First, I get this:
"The source file is different from when the module was built. Would you like the debugger to use it anyway?" The paths shown are:
Source file: C:\Program Files\Microsoft Visual Studio 8\CAPS Project\Src 1-21-08\CAPSpro.NET\frmDocManager.vb
Module: C:|Program Files\Microsoft Visual Studio 8\CAPS Project\Src 1-21-08\CAPSpro.NET\bin\CAPSpro.exe
Click Yes:
Then a message comes up telling it cannot find the file, showing a path which is the path of the German developer who originally published the program ("The file 'C:\Dokumente und Einstellungen\teg\Eigene Dateien\CAPS\Src\VB2005\CAPSpro.NET\frmDocManager.vb' does not exist.").
On one occasion I was able to change the path to my local machine where I am working on the source code, and the output file was successfully saved. Without the debugger, however, there is no message that the file cannot be found. This only appears when I step through the debugger one line at a time.
|
|
|
|
|
Mike Nelson wrote: "The source file is different from when the module was built. Would you like the debugger to use it anyway?"
Well clicking "yes" to that could cause any number of completely useless things to happen. This messages indicates you don't have a valid development environment for debugging your application.
Mike Nelson wrote: when I step through
In order to debug you should have the source code and be debugging from a build on your local machine. If you were doing that you should not see the first message about source file being different from the module.
Based on your latests post it seems likely that you are no where near discovering the actual problem. I have no idea how to help you at this point since it seems likely you lack understanding "how things work" that are helpful during debugging efforts.
led mike
|
|
|
|
|
I am debugging from source code on my local machine. The project, however, is a copy from the German developer. We both believe that is probably the source of the error, but don't know if our approach to collaboration on this project is typical or not. He sends me a copy of the code, I make corrections or additions necessary for US localization, test it, then send the code back to him where he recompiles and publishes the project. In this one case, however, trying to create a text file, neither of us understands where the reference to his file path comes from. I was hoping that someone else my have run into this during a similar collaboration.
Thanks, anyway, for your comments.
|
|
|
|
|
Mike Nelson wrote: where the reference to his file path comes from
What about "when" it comes. Based on your posts I am unclear when the reference is used, it seems in your last post there is a reference to source code meaning the reference is a development environment reference.
All the Visual Studio files are in XML form now I believe so you could open them in a text editor and search for the path string in the file.
led mike
|
|
|
|
|
Well, these messages only appear when stepping through the code in debug mode. During normal program execution there is no message, the text file is merely left empty.
I'll take your hint about searching through the files with a text editor. I'm just not sure what file this would even be in.
|
|
|
|
|
Mike Nelson wrote: He wrote a routine to create a text file that we can both use.
Well somewhere in that code the path for the file is obtained or created right? Post that code.
led mike
|
|
|
|
|
We finally tracked down the problem. It didn't really have anything to do with the filepath message. That is apparently just a smokescreen -- VB couldn't figure out the problem and popped up that message. The problem was a database one and once we fixed the database, the module works corectly.
I did, also, find the coded reference to the local filepath in Germany. I need to discuss this with my German colleague to determine whether it is necessary or not to be hard-coded.
Thanks for taking the time to look at this.
|
|
|
|
|
Dear All
Is there a good tool for UML yet free?Or in another way what is the best free UML tool?
regards
|
|
|
|
|
|
I discovered StarUML, and have found it very powerful.
http://www.staruml.com/[^]
Regards
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Dear all
I am not sure if it is the proper place to put this question.
Can I draw UML diagrams with Visio 2007. If yes, is it built in Visio or a plugin. And how to draw them in Visio?
Best Regards
Mohammed
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
