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!