Contest Entry
/*----------------------------------------------------------------
Query #1 & #2
----------------------------------------------------------------*/
-- Assuming you have a unique ID for each article.....
CREATE UNIQUE NONCLUSTERED INDEX idx_AnalysisLog_ArticleID ON dbo.AnalysisLog (ArticleID) INCLUDE ([Count]);
GO
-- Wrap in a transaction to ensure multiple inserts don't fire a the same time if an article has never been viewed before
BEGIN TRANSACTION;
-- Create or update based on the ArticleID, numbers make for faster searching.
-- It doesn't mention that you want to know how many clicks per day, just how many clicks, assuming that....
DECLARE @ArticleID int,
@RowCount int;
SET @ArticleID = 7;
UPDATE dbo.AnalysisLog
SET [Count] = COALESCE([Count], 0) + 1 -- Assumes the worst and Count allows NULL, should be defaulted to 0 and the COALSECE removed
WHERE ArticleID = @ArticleID;
-- This way the index/table is only searched once for an insert or update whereas before an update required 2 searches
SET @RowCount = @@ROWCOUNT;
IF @RowCount = 0
BEGIN
INSERT INTO dbo.AnalysisLog (ArticleID, [Count], LastVisited)
VALUES (@ArticleID, 1, CURRENT_TIMESTAMP);
END
COMMIT TRANSACTION;
/*----------------------------------------------------------------
Query #3
----------------------------------------------------------------*/
CREATE TABLE #PageIndex (
IndexID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PostID int
);
-- Saves doing 2 searches, you may need to add a new index and re-order the columns in the where clause to put the most selective first
-- Again, you would be better passing in a Blog ID of some sort and filter on this rather than filter on characters (PostName)
-- It would be better if a temp table were never created, without seeing more information on how that table is used it's difficult to say
INSERT INTO #PageIndex (PostID)
SELECT PostID
FROM cs_Posts WITH (NOLOCK)
WHERE SectionID = @SectionID
AND PostName = @PostName
AND IsApproved = 1
AND PostLevel > 1
AND ApplicationPostType <> 16
ORDER BY PostDate;
/*----------------------------------------------------------------
Query #4
----------------------------------------------------------------*/
-- Create this index to improve performance on this table, depending on the number of existing indexes and size of the table (it could be 3 rows for all I know!)
CREATE NONCLUSTERED INDEX idx_cs_weblog_Weblogs_SectionID ON dbo.cs_weblog_Weblogs (SectionID);
GO
DECLARE @SettingsID int;
SET @SettingsID = 1000;
-- If this query is used to give information about blog authors it appears to have a vast amount of irrelevant data returned
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.*, -- Should specify the exact columns required
NULL AS LastUserActivity
FROM cs_Sections F
INNER JOIN cs_weblog_Weblogs W ON F.SectionID = W.SectionID -- Use JOINS!
WHERE F.SettingsID = @SettingsID
AND F.ApplicationType = 1;
My 2 cents!