score:0

Accepted answer

I added some more indexes to the database and it sped things up considerably. Execution time is now about 20 seconds (!!). I'll admit that a lot of the added indexes were guesswork (or just adding them randomly).

Read More

score:0

Do you really need to aggregate twice? Would this query give you the same results?

SELECT 
T2.ForumId, 
Forums.Title, 
T2.ForumThreads, 
T2.ForumPosts, 
T2.ForumStart, 
T2.ForumStop  
FROM 
    Forums 
INNER JOIN (  
    SELECT
         Min(ThreadStart) As ForumStart,
         Max(ThreadStop) As ForumStop,     
         Count(*) As ForumThreads,     
         Sum(ThreadPosts) As ForumPosts,     
         Threads.ForumId 
    FROM     
        Threads     
    INNER JOIN (          
                SELECT             
                    Posts.DateTime As ThreadStart,             
                    Posts.DateTime As ThreadStop,             
                    Count(*) As ThreadPosts,             
                    Posts.ThreadId         
                FROM             
                    Posts         
                 ) As P2 ON Threads.ThreadId = P2.ThreadId  
    GROUP BY     
        Threads.ForumId  
    ) AS T2 ON T2.ForumId = Forums.ForumId 

score:0

If you denormalize by adding ForumId to the Posts table, you would be able to query all of the stats straight out of the Posts table. With the right index, this would probably perform pretty well. Of course, that will require a small change to your code to include the ForumId when inserting into the Posts table...

Similar Question and Answer

score:1

Have you tried putting those 2 derived table in a #temp table? SQL Server will get statistics (single column) from them and you can lay indexes on them as well.

Also, at first sight indexed views could help here as you have lots of aggregates.

score:1

How about something like this? Anyway, you get the idea...

SELECT f.ForumID,
f.Title,
MIN(p.[DateTime]) as ForumStart,
MAX(p.[DateTime]) as ForumStop,
COUNT(DISTINCT f.ForumID) as ForumPosts,
COUNT(DISTINCT t.ThreadID) as ForumThreads
FROM Forums f
INNER JOIN Threads t
ON f.ForumID = t.ForumID
INNER JOIN Posts p
ON p.ThreadID = p.ThreadID
GROUP BY f.ForumID, f.Title

score:1

Indexes may work when you do SELECT FROM, but the results of the sub-queries are not indexed. The join to them is probably killing the performance.

As buckley suggested, I'd try storing the intermediate results in a #temp table and adding an index before doing the final query.

But the outer SELECT doesn't include thread-specific information. It looks like the query is just selecting the min/max date by forum. If so, you can just get the min/max/count posts grouped by forum.


More questions with similar tag