High-traffic comment system database Stored Procedure Architecture

Website comment information is the most resource-consuming part. Optimizing comment data is crucial for large websites. No SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo]. [CommentsTables] ([ID] [int] IDENTITY (1, 1) NOTNULL, [Key] [nvarchar] (50) NOTNULL, [TableName] [nvarch

The comment information of a website is the most resource-consuming place. Optimizing the comment data is crucial for large websites. No SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate table [dbo]. [CommentsTables] ([ID] [int] IDENTITY (1,1) not null, [Key] [nvarchar] (50) not null, [TableName] [nvarch

Website comment information is the most resource-consuming place. Optimizing comment data is crucial for large websites. <无>

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate table [dbo]. [CommentsTables] ([ID] [int] IDENTITY (1,1) not null, [Key] [nvarchar] (50) not null, [TableName] [nvarchar] (80) not null, [StartID] [int] not null, [EndID] [int] not null, CONSTRAINT [PK_SysTables] primary key clustered ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ON [PRIMARY] GO -- Obtain the table name create function [dbo] Based ON SourceID and key [funGetTableName] (@ SourceID int, @ Key nvarchar (50) RETURNS nvarchar (80) as begindeclare @ tableName nvarchar (80); declare @ tableArea int; declare @ mod int; declare @ Size int; set @ Size = 1000; set @ mod = @ SourceID % @ Size; if @ mod> 0 set @ tableArea = Cast (@ SourceID-1) /@ Size as int) + 1; elseset @ tableArea = Cast (@ SourceID-1)/@ Size as int ); set @ tableName = 'comments _ '+ @ Key + Cast (@ tableArea as nvarchar (10); return @ tableNameendGO -- Comment on the CREATE proc [dbo] Stored Procedure for writing a call. [procAddComment] (@ ParentID int, @ SourceID int, @ NickName nvarchar (20), @ Content nvarchar (300), @ IP nvarchar (30), @ City nvarchar (30 ), @ BeFiltered bit, @ Disable bit, @ Key nvarchar (50), @ InsertedID int Output) asbegindeclare @ tableName nvarchar (80); declare @ tableArea int; declare @ mod int; declare @ Size int; set @ Size = 1000; set @ mod = @ SourceID % @ Size; if @ mod> 0 set @ tableArea = Cast (@ SourceID-1) /@ Size as int) + 1; elseset @ tableArea = Cast (@ SourceID-1)/@ Size as int ); set @ tableName = 'comments _ '+ @ Key + Cast (@ tableArea as nvarchar (10 )); if not Exists (select 'x' from [CommentsTables] where [Key] = @ Key and [TableName] = @ tableName) begindeclare @ StartID int; declare @ EndID int; set @ EndID = @ tableArea * @ Size; set @ StartID = @ EndID-(@ Size-1); -- create the table declare @ CreateSQL nvarchar (MAX ); set @ CreateSQL = 'create table [dbo]. ['+ @ tableName +'] ([ID] [int] IDENTITY (1,1) not null, [ParentID] [int] not null, [SourceID] [int] not null, [NickName] [nvarchar] (20) not null, [Content] [nvarchar] (300) not null, [Datetime] [datetime] not null, [IP] [nvarchar] (30) not null, [City] [nvarchar] (30) not null, [BeFiltered] [bit] not null, [Disable] [bit] not null, [Lou] [int] not null, [Ding] [int] not null, [Cai] [int] not null, CONSTRAINT [PK _ '+ @ tableName +'] primary key clustered ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] 'exec (@ CreateSQL ); -- create index id descexec ('create unique nonclustered index [IX _ '+ @ tableName +' _ ID_DESC] ON [dbo]. ['+ @ tableName +'] ([ID] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '); -- create index Ding DESCEXEC ('create nonclustered index [IX _ '+ @ tableName +' _ Ding_DESC] ON [dbo]. ['+ @ tableName +'] ([Ding] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '); -- create index SourceID DESCEXEC ('create nonclustered index [IX _ '+ @ tableName +' _ SourceID_DESC] ON [dbo]. ['+ @ tableName +'] ([SourceID] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '); -- create index Lou DESCEXEC ('create nonclustered index [IX _ '+ @ tableName +' _ Lou_DESC] ON [dbo]. ['+ @ tableName +'] ([Lou] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ') -- create the default EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ ParentID] DEFAULT (0) FOR [ParentID] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ Datetime] DEFAULT (getdate () FOR [Datetime] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ BeFiltered] DEFAULT (0) FOR [BeFiltered] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ Disable] DEFAULT (0) FOR [Disable] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ Lou] DEFAULT (1) FOR [Lou] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ Ding] DEFAULT (0) FOR [Ding] '); EXEC ('alter TABLE [dbo]. ['+ @ tableName +'] add constraint [DF _ '+ @ tableName +' _ Cai] DEFAULT (0) FOR [Cai] '); insert Into [CommentsTables] ([Key], [TableName], [StartID], [EndID]) values (@ Key, @ tableName, @ StartID, @ EndID ); end declare @ TemLou int; declare @ SQL nvarchar (MAX); set @ SQL = n' select @ TemLou = Count (ID) from dbo. ['+ @ tableName + N'] where SourceID = @ SourceID'; exec sp_executesql @ SQL, n' @ SourceID int, @ TemLou int output', @ SourceID, @ TemLou output; if @ TemLou = 0 set @ TemLou = 1; elseset @ TemLou = @ TemLou + 1; declare @ Lou int; set @ Lou = @ TemLou; declare @ InsertSQL nvarchar (MAX ); set @ InsertSQL = n' Insert Into dbo. ['+ @ tableName + N'] (ParentID, SourceID, NickName, Content, IP, City, BeFiltered, [Disable], [Lou]) values (@ ParentID, @ SourceID, @ NickName, @ Content, @ IP, @ City, @ BeFiltered, @ Disable, @ Lou); select @ InsertedID = SCOPE_IDENTITY (); '; exec sp_executesql @ InsertSQL, n' @ ParentID int, @ SourceID int, @ NickName nvarchar (20), @ Content nvarchar (300), @ IP nvarchar (30), @ City nvarchar (30 ), @ BeFiltered bit, @ Disable bit, @ Lou int, @ InsertedID int output', @ ParentID, @ SourceID, @ NickName, @ Content, @ IP, @ City, @ BeFiltered, @ Disable, @ Lou, @ InsertedID output; end GO -- get the latest comment stored procedure CREATE proc [dbo]. [procGetNewComments] (@ SourceID int, @ Key nvarchar (50), @ PageIndex int, @ PageSize int, @ Fields nvarchar (100), @ PageCount int output) asbegindeclare @ tableName nvarchar (80); set @ tableName = dbo. funGetTableName (@ SourceID, @ Key); declare @ Rc int; declare @ SQL nvarchar (MAX); set @ SQL = n' select @ Rc = COUNT (ID) from dbo. ['+ @ tableName + N'] where SourceID = @ SourceID'; exec sp_executesql @ SQL, n' @ SourceID int, @ Rc int output', @ SourceID, @ Rc output; if @ Rc % @ PageSize> 0 set @ PageCount = Cast (@ Rc/@ PageSize as int) + 1; elseset @ PageCount = Cast (@ Rc/@ PageSize as int ); if @ PageIndex = 1 beginset @ SQL = n' select top' + Cast (@ PageSize as nvarchar (30) + ''+ @ Fields + N' from dbo. ['+ @ tableName + N'] where SourceID = @ SourceID order by Lou desc'; exec sp_executesql @ SQL, n' @ SourceID int ', @ SourceID; end elsebegindeclare @ StartLou int; declare @ EndLou int; -- 1 20 1-20, 21-40, 41-60 set @ EndLou = @ Rc-(@ PageIndex-1) * @ PageSize; if @ EndLou> @ Rc set @ EndLou = @ Rc; set @ StartLou = @ EndLou-@ PageSize + 1; if @ StartLou <1 set @ StartLou = 1; set @ SQL = n'select' + @ Fields + N' from dbo. ['+ @ tableName + N'] where Lou >=@ StartLou and Lou <= @ EndLou and SourceID = @ SourceID order by Lou desc'; exec sp_executesql @ SQL, n'@ SourceID int, @ StartLou int, @ EndLou int', @ SourceID, @ StartLou, @ EndLou; endendGO -- step on Create proc [dbo]. [procCai] (@ ID int, @ SourceID int, @ key nvarchar (50), @ Times int output) asbegindeclare @ tableName nvarchar (80); set @ tableName = dbo. funGetTableName (@ SourceID, @ key); declare @ SQL nvarchar (MAX); set @ SQL = n' update dbo. ['+ @ tableName + N'] set Cai = Cai + 1 where ID = @ ID; select @ Times = Cai from dbo. ['+ @ tableName + N'] where ID = @ id'; exec sp_executesql @ SQL, n' @ ID int, @ Times int output', @ ID, @ Times output; endGO -- top create proc [dbo]. [procDing] (@ ID int, @ SourceID int, @ key nvarchar (50), @ Times int output) asbegindeclare @ tableName nvarchar (80); set @ tableName = dbo. funGetTableName (@ SourceID, @ key); declare @ SQL nvarchar (MAX); set @ SQL = n' update dbo. ['+ @ tableName + N'] set Ding = Ding + 1 where ID = @ ID; select @ Times = Ding from dbo. ['+ @ tableName + N'] where ID = @ id'; exec sp_executesql @ SQL, n' @ ID int, @ Times int output', @ ID, @ Times output; endGO