SQL Server Performance Forum – Threads Archive

Indexed View for aggregates

I have an Indexed View question. Imagine a forums application, running off a SQL Server database – a bit like this one <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> You have a Forums, Topics, and Posts table* When you display the forums list, you also display information on how many topics, and how many posts there in each forum. The way I have done this before is use sub queries in the stored procedure that gets the forums data. One sub query will count the topics, and one will count the posts. The performance is ok, but not fast – I am looking for a smarter alternative. I can think of a few ideas to boost performance. 1) Maintain a TopicCount and PostCount fields in the Forums table (possibly with a trigger). This would be alot quicker for querying, but it may be replicating a solution that can already be done with indexed views. It is a little crude… 2) Create an indexed view – this seems the most suitable option as it really provides the same performance as option 1 but with probably better insert performance. However, I cannot seem to create a view that returns an aggregate for BOTH the number of posts, and the number of topics in the same record. Any ideas? Andrew * Sometimes Topics and Posts might be the same table.

Something like … SELECT COUNT(DISTINCT Topic) TopicCount, COUNT(*) PostCount FROM dbo.tblPosts perhaps?

quote:Originally posted by Adriaan Something like … SELECT COUNT(DISTINCT Topic) TopicCount, COUNT(*) PostCount FROM dbo.tblPosts perhaps?

That was the first thing I tried. However, you are not allowed a count(distinct) in an indexed view. The more I look into it the more I think it would be better to write a trigger on the table, and maintain the counts myself.

The idea was to avoid the subqueries you mentioned, getting the results in a single query. Why would you need the view to be indexed? If the underlying table is properly indexed, that should be enough.

quote:Originally posted by Adriaan The idea was to avoid the subqueries you mentioned, getting the results in a single query. Why would you need the view to be indexed? If the underlying table is properly indexed, that should be enough.

The end goal is to be able to query the forums table, without the aggregates for topicCount and postCount having to be calculated for each query.

Yes, but wouldn’t an unindexed view already give you a performance boost?

quote:Originally posted by Adriaan Yes, but wouldn’t an unindexed view already give you a performance boost?

There is no boost, count(distinct…) offers no performance boost over sub queries – in this instance. The forums system gets well and truly hammered from a viewing perspective, in comparison to how many people actually post. I would really like to find a way of storing these values so they don’t have to be calculated on the fly. Which means I either find a way to get an indexed view so that aggregates are not calculated for every single hit on the forums page OR I keep a running total myself by using triggers.

I tried adding an indexed view, and running the aggregates against the view, but the execution plan still shows an index scan against the underlying table, not against the view. Seems like you need to keep track of those counters in a separate, and triggers seem to be a good option.

]]>