CONTEST ENTRY

This took a bit longer than I thought...

Queries 1 and 2:

“Query #2 runs first, looking to see if that particular article has ever been viewed before or not.” If you simply want to know whether the article has “ever” been run before, why do you need to check the [Time] column? If there is a record in the table, wouldn’t that be enough to tell you if it has ever been viewed? If this is the case, I would remove the “[Time]=@RoundedTime” criteria.

If the [Time] criteria is necessary, I would apply the first listed nonclustered index recommendation:

CREATE NONCLUSTERED INDEX [IX_AnalysisLog_TimeData]

ON [dbo].[AnalysisLog] ([Time],[Data])

You don’t show the structure of the AnalysisLog table. I assume that there is no identity column. If you were to add an identity column to the AnalysisLog table, eg. called AnalysisLogID, you could do this:

DECLARE @AnalysisLogID int

SELECT @AnalysisLogID=AnalysisLogID

FROM dbo.AnalysisLog

WHERE [Data] = @data

--AND [Time] = @RoundedTime **still required?**

IF @AnalysisLogID IS NOT NULL

UPDATE dbo.AnalysisLog

SET [Count] = [Count] + 1

WHERE AnalysisLogID=@AnalysisLogID

ELSE

--execute undocumented query to create a new record

Query 3:

With this one, it appears that you are passing @SectionID and @PostName parameters, using them to lookup @ThreadID and @PostID, but only use the @ThreadID in the WHERE clause of the INSERT query. I don’t know if you need the @PostID at all, or if @ThreadID is sufficient. This should be more efficient:

INSERT INTO #PageIndex (PostID)

SELECT PostID

FROM Posts

WHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16

AND SectionID=@SectionID AND PostName=@PostName

ORDER BY PostDate

You also don’t show how you use the #PageIndex temp table after you create it. If you just run a simple SELECT statement afterwards, I would change the code to not use a temp table at all, especially as about 27% (31% of 91%) of the execution time is taken up by inserting into the temp table.

If you need to use the temp table, rather than create a clustered index, why not use:

INSERT INTO #PageIndex(IndexID, PostID)

SELECT ROW_NUMBER() OVER (ORDER BY PostID) as IndexID, PostID

FROM Posts

WHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16

AND SectionID=@SectionID AND PostName=@PostName

ORDER BY PostDate

And if you can make do without the temp table, simply drop the INSERT line.

Query 4:

I can see that there is a clustered index on both tables, but we don’t know which columns they are on. We can also see a large number of lob logical reads, but don’t know which columns are causing this as we don’t know the table definition. I can’t see anything obvious to improve on, but I would:

Consider adding an index on the SectionID column of the cs_weblog_Weblogs table.

Consider adding a covering index on cs_weblog_Weblogs

It won’t improve efficiency, but I would revise the query to replace W.* with the actual column names and for consistency with the other queries, change the way that the tables are joined:

FROM cs_Sections F INNER JOIN cs_weblog_Weblogs W ON F.SectionID=W.SectionID

WHERE F.ApplicationType=1

AND F.SettingsID=@SettingsID