Click here to Skip to main content
15,914,488 members
Home / Discussions / ASP.NET
   

ASP.NET

 
GeneralRe: checkbox problem Pin
bokuceres24-Jan-08 22:19
bokuceres24-Jan-08 22:19 
GeneralRe: checkbox problem Pin
bhanu1234524-Jan-08 23:04
bhanu1234524-Jan-08 23:04 
Generalrunning exe in shared hosting Pin
Mogaambo24-Jan-08 3:19
Mogaambo24-Jan-08 3:19 
GeneralRe: running exe in shared hosting Pin
Vasudevan Deepak Kumar24-Jan-08 4:12
Vasudevan Deepak Kumar24-Jan-08 4:12 
GeneralRe: running exe in shared hosting Pin
Mogaambo24-Jan-08 4:46
Mogaambo24-Jan-08 4:46 
GeneralRe: running exe in shared hosting Pin
N a v a n e e t h24-Jan-08 7:51
N a v a n e e t h24-Jan-08 7:51 
GeneralDeployment issue Pin
samerh24-Jan-08 2:47
samerh24-Jan-08 2:47 
GeneralDAL Design Question Pin
Waleed Eissa24-Jan-08 2:38
Waleed Eissa24-Jan-08 2:38 
Hi gurus, I'm creating an application 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 example, 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...


GeneralSession Pin
Burim Rama24-Jan-08 1:44
Burim Rama24-Jan-08 1:44 
GeneralRe: Session Pin
Shaik Haneef24-Jan-08 1:50
Shaik Haneef24-Jan-08 1:50 
GeneralRe: Session Pin
Burim Rama24-Jan-08 1:51
Burim Rama24-Jan-08 1:51 
GeneralRe: Session Pin
Shaik Haneef24-Jan-08 1:51
Shaik Haneef24-Jan-08 1:51 
GeneralRe: Session Pin
bokuceres24-Jan-08 22:25
bokuceres24-Jan-08 22:25 
QuestionHow to export mail messenger list from gmail, hotmail,yahoo? Pin
hmx_51824-Jan-08 1:33
hmx_51824-Jan-08 1:33 
GeneralMicrosoft.VisualBasic.Devices Name space Pin
Lijo Rajan24-Jan-08 1:21
Lijo Rajan24-Jan-08 1:21 
GeneralMail Sending probs Pin
Member 387988124-Jan-08 1:06
Member 387988124-Jan-08 1:06 
GeneralYour also specify mail server name and port no, userid and password of emailid which your send from , use following method Pin
Shaik Haneef24-Jan-08 1:30
Shaik Haneef24-Jan-08 1:30 
GeneralRe: Your also specify mail server name and port no, userid and password of emailid which your send from , use following method Pin
Member 387988127-Jan-08 20:04
Member 387988127-Jan-08 20:04 
Generalplease try this website Pin
Shaik Haneef27-Jan-08 22:23
Shaik Haneef27-Jan-08 22:23 
QuestionWant to upload image to wwwroot as thumbnail..... Pin
Member 472423424-Jan-08 0:41
Member 472423424-Jan-08 0:41 
GeneralRe: Want to upload image to wwwroot as thumbnail..... Pin
N a v a n e e t h24-Jan-08 0:52
N a v a n e e t h24-Jan-08 0:52 
GeneralRe: Want to upload image to wwwroot as thumbnail..... Pin
Vasudevan Deepak Kumar24-Jan-08 4:11
Vasudevan Deepak Kumar24-Jan-08 4:11 
GeneralRe: Want to upload image to wwwroot as thumbnail..... Pin
N a v a n e e t h24-Jan-08 7:49
N a v a n e e t h24-Jan-08 7:49 
QuestionWhat are the benefits of using IEnumerator over other Loops?? Pin
Suryanairg24-Jan-08 0:36
Suryanairg24-Jan-08 0:36 
AnswerRe: What are the benefits of using IEnumerator over other Loops?? Pin
N a v a n e e t h24-Jan-08 0:49
N a v a n e e t h24-Jan-08 0:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.