Contest Entry
--------------------------------------------------------------
Recommendation For Query#1 and @Query#2
--------------------------------------------------------------
Understanding: It seems that AnalysisLog table used to keep count of clicks on each artical(data) date wise.
Logical Change Recommended : first execute Update query to increase count (query#1).
if not row affected by Update Query then execute insert query to make first record. This will eliminate execution of query#2
Solution:
UPDATE dbo.AnalysisLog SET [Count] = [Count] + 1 WHERE [Time] = @RoundedTime AND [Data] = @data
IF @@ROWCOUNT = 0
**Insert query**
--------------------------------------------------------------
Recommendation For Query#3
--------------------------------------------------------------
Recommendation#1. use nolock with first select query, which is used to get ThreadID and PostID
Recommendation#2. create index on [cs_Posts].[PostDate], this saves sorting time. Because in execution plan of Insert into #PageIndex query sort cost is 36%.
Solution:
CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Posts] ([PostDate])
GO
Select
@ThreadID = p.ThreadID, @PostID = p.PostID
From
cs_Posts p with (nolock)
Where
p.SectionID = @SectionID and p.PostName = @PostName
GO
--------------------------------------------------------------
Recommendation For Query #4
--------------------------------------------------------------
1. We can use inner join between cs_Sections F, cs_weblog_Weblogs W on F.SectionID = W.SectionID
2. We can use nolock with both tables
3. Make non-cluster index on F.SectionID and W.SectionID
Solution:
CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Sections] ([SectionID])
GO
CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_weblog_Weblogs] ([SectionID])
GO
SELECT
F.[SectionID], F.[SettingsID], F.[IsActive], F.[ParentID], F.[GroupID], F.[Name],
F.[NewsgroupName], F.[Description], F.[DateCreated], F.[Url], F.[IsModerated],
F.[DaysToView], F.[SortOrder], F.[TotalPosts], F.[TotalThreads], F.[DisplayMask],
F.[EnablePostStatistics], F.[EnablePostPoints], F.[EnableAutoDelete],
F.[EnableAnonymousPosting], F.[AutoDeleteThreshold], F.[MostRecentThreadID],
F.[MostRecentThreadReplies], F.[PostsToModerate], F.[ForumType], F.[IsSearchable],
F.[ApplicationType], F.[ApplicationKey], F.PropertyNames as SectionPropertyNames,
F.PropertyValues as SectionPropertyValues, F.[DefaultLanguage], F.[DiskUsage], W.*,
null as LastUserActivity
FROM
cs_Sections F with (nolock)
inner join cs_weblog_Weblogs W with (nolock) on F.SectionID = W.SectionID
WHERE
F.SettingsID = @SettingsID AND
ApplicationType = 1
GO
--------------------------------------------------------------
Pradeep Chaurasia
P. I. Softek Ltd
Sec-62, Noida, UP
Pradeep Chaurasia
P.I. Softek Ltd
Sec-62, Noida, UP