I build database called MessageBoard and created StoredProcedure GetHomeView that return data for HomeView. I am completely new in this area.
Stored procedure contains next fields:
Id - Key fied.
ForumId - Field that should be in result query because it is need to know, which forum should be loaded if user will open it.
Category - Name of category.
Forum - Name of forum.
TopicsAmount - Total amount of Topics in Forum.
RepliesCount - Total amount of Posts in Forum.
LastDate - The date of last Post in Forum.
LastUserName - The Name of User that left last Post.
LastTopicName - Last topic where user left last post in forum.
Need advice how to improve structure and performance and keep the same output result. Also I am sure that I made common mistakes help me to find them and to fix.
I tried only CREATE NONCLUSTERED INDEX [TEST_INDEX] ON dbo.Posts (CreationDateTime) INCLUDE (TopicId,UserName) it gave some improvement but not much.
Script generating database structure:
USE [MessageBoard]
GO
/****** Object: Table [dbo].[Categories] Script Date: 4/1/2020 01:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_Categories] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Forums] Script Date: 4/1/2020 01:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Forums](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[CategoryId] [bigint] NOT NULL,
CONSTRAINT [PK_Forums] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Posts] Script Date: 4/1/2020 01:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Text] [nvarchar](max) NULL,
[CreationDateTime] [datetime2](7) NOT NULL,
[TopicId] [bigint] NOT NULL,
[UserName] [nvarchar](max) NULL,
CONSTRAINT [PK_Posts] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Topics] Script Date: 4/1/2020 01:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Topics](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Text] [nvarchar](max) NULL,
[CreationDateTime] [datetime2](7) NOT NULL,
[Author] [nvarchar](max) NULL,
[ForumId] [bigint] NOT NULL,
CONSTRAINT [PK_Topics] 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] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] WITH CHECK ADD CONSTRAINT [FK_Forums_Categories_CategoryId] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Categories] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Forums] CHECK CONSTRAINT [FK_Forums_Categories_CategoryId]
GO
ALTER TABLE [dbo].[Posts] WITH CHECK ADD CONSTRAINT [FK_Posts_Topics_TopicId] FOREIGN KEY([TopicId])
REFERENCES [dbo].[Topics] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_Posts_Topics_TopicId]
GO
ALTER TABLE [dbo].[Topics] WITH CHECK ADD CONSTRAINT [FK_Topics_Forums_ForumId] FOREIGN KEY([ForumId])
REFERENCES [dbo].[Forums] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Topics] CHECK CONSTRAINT [FK_Topics_Forums_ForumId]
GO
/****** Object: StoredProcedure [dbo].[GetRepliesCountByCategories_V21] Script Date: 4/1/2020 01:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Script generating stored procedure:
CREATE PROCEDURE [dbo].[GetHomeView]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
With
TopicsWithPosts(ForumId,TopicText,PostUserName,TopicId,PostCreationDateTime)
AS
(
SELECT
dbo.Topics.ForumId,
dbo.Topics.Text,
dbo.Posts.UserName,
dbo.Topics.Id,
dbo.Posts.CreationDateTime
FROM dbo.Topics
INNER JOIN dbo.Posts on dbo.Topics.Id=dbo.Posts.TopicId
),
ForumsWithTopics(ForumId,TopicId)
AS
(
SELECT
dbo.Forums.Id,
dbo.Topics.Id
FROM
dbo.Forums
INNER JOIN dbo.Topics on dbo.Topics.ForumId = dbo.Forums.Id
),
CountTopicsByForum (ForumId, Amount)
AS
(
SELECT
ForumsWithTopics.ForumId as ForumId,
COUNT(distinct ForumsWithTopics.TopicId) as Amount
FROM ForumsWithTopics
GROUP BY
ForumsWithTopics.ForumId
),
CountPostsByForum (ForumId, Amount)
AS
(
SELECT
ForumsWithTopics.ForumId as ForumId,
count(dbo.Posts.Id) as Amount
FROM
ForumsWithTopics
LEFT JOIN dbo.Posts on dbo.Posts.TopicId = ForumsWithTopics.TopicId
group by
ForumsWithTopics.ForumId
),
MaxPostDateInForum(ForumId,LastPostDate)
AS
(
SELECT TopicsWithPosts.ForumId, MAX(TopicsWithPosts.PostCreationDateTime) as LastPostDate
FROM TopicsWithPosts
GROUP BY TopicsWithPosts.ForumId
),
LastTopicsByDate(ForumId,TopicText,LastPostDate,PostUserName,TopicId,RowNumber)
AS
(
SELECT TopicsWithPosts.ForumId, TopicsWithPosts.TopicText, MaxPostDateInForum.LastPostDate,TopicsWithPosts.PostUserName,TopicsWithPosts.TopicId
,row_number() over(partition by TopicsWithPosts.ForumId order by MaxPostDateInForum.LastPostDate desc) as RowNumber
FROM
TopicsWithPosts
INNER JOIN
MaxPostDateInForum on TopicsWithPosts.ForumId = MaxPostDateInForum.ForumId and MaxPostDateInForum.LastPostDate=TopicsWithPosts.PostCreationDateTime
),
LastUserByDateInForum(ForumId, LastTopicName, LastPostDate,LastUserName,TopicId,RowNumber)
As
(
SELECT
LastTopicsByDate.ForumId,
LastTopicsByDate.TopicText,
LastTopicsByDate.LastPostDate,
LastTopicsByDate.PostUserName,
LastTopicsByDate.TopicId,
LastTopicsByDate.RowNumber
FROM LastTopicsByDate
where LastTopicsByDate.RowNumber = 1
)
SELECT
ROW_NUMBER() over (order by dbo.Categories.Id asc) as Id,
dbo.Forums.Id as ForumId,
dbo.Categories.Name as Category,
dbo.Forums.Name as Forum,
CountTopicsByForum.Amount as TopicsAmount,
CountPostsByForum.Amount as RepliesCount,
LastUserByDateInForum.LastPostDate as LastDate,
LastUserByDateInForum.LastUserName,
LastUserByDateInForum.LastTopicName
FROM
Categories
LEFT JOIN dbo.Forums on dbo.Forums.CategoryId = dbo.Categories.Id
LEFT JOIN CountTopicsByForum on CountTopicsByForum.ForumId = dbo.Forums.Id
LEFT JOIN CountPostsByForum on dbo.Forums.Id = CountPostsByForum.ForumId
LEFT JOIN LastUserByDateInForum on dbo.Forums.Id = LastUserByDateInForum.ForumId
GROUP BY
dbo.Categories.Id,
dbo.Forums.Id,
dbo.Forums.Name,
dbo.Categories.Name,
CountTopicsByForum.Amount,
CountPostsByForum.Amount,
LastUserByDateInForum.LastPostDate,
LastUserByDateInForum.LastUserName,
LastUserByDateInForum.LastTopicName
ORDER BY
LastUserByDateInForum.LastPostDate DESC
END
--
GO
Script that fill database with data:
DELETE FROM Posts;
DELETE FROM Topics;
DELETE FROM Forums;
DELETE FROM Categories;
SET IDENTITY_INSERT Categories ON;
declare @CategoryNumber int = 1;
while @CategoryNumber <= 10
begin
insert into Categories(Id,Name) values(@CategoryNumber,'Category ' + CONVERT(varchar(MAX), @CategoryNumber))
set @CategoryNumber = @CategoryNumber + 1
end
SET IDENTITY_INSERT Categories OFF;
SET IDENTITY_INSERT Forums ON;
declare @ForumNumber int = 1;
while @ForumNumber <= 50
begin
insert into Forums(Id,Name,CategoryId) values(@ForumNumber,'Test ' + CONVERT(varchar(MAX), @ForumNumber), ((RAND() * 9)+1))
set @ForumNumber = @ForumNumber + 1
end
SET IDENTITY_INSERT Forums OFF;
SET IDENTITY_INSERT Topics ON;
declare @TopicNumber int = 1;
declare @date datetime = null;
while @TopicNumber <= 10000
begin
SET @date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
insert into Topics(Id,Author,CreationDateTime,Text,ForumId) values(@TopicNumber,'Some author '+ CONVERT(varchar(MAX), @TopicNumber),@date,'Topic ' + CONVERT(varchar(MAX), @TopicNumber), ((RAND() * 49)+1))
set @TopicNumber = @TopicNumber + 1
end
SET IDENTITY_INSERT Topics OFF;
SET IDENTITY_INSERT Posts ON;
declare @PostNumber int = 1;
while @PostNumber <= 1000000
begin
SET @date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
insert into Posts(Id,Text,CreationDateTime,UserName,TopicId) values(@PostNumber,'Some author ' + CONVERT(varchar(MAX), @PostNumber),@date,'PostNumber ' + CONVERT(varchar(MAX), @PostNumber), ((RAND() * 9999)+1))
set @PostNumber = @PostNumber + 1
end
SET IDENTITY_INSERT Posts OFF;
Need advice how to improve structure and performance and keep the same output result.
I tried only CREATE NONCLUSTERED INDEX [TEST_INDEX] ON dbo.Posts (CreationDateTime) INCLUDE (TopicId,UserName) it gave some improvement but not much.
