Take the 2-minute tour ×

I have the following table


EVENT_ID: pk, int, not null
TYPEID: fk, int, not null
CATEGORYID: fk, int, null
SOURCE: varchar(255), null
DESCRIPTION: varchar(4000), null
CREATED: datetime, null

We've been creating a report, and found that performance sucks. There aren't any indexes aside from the clustered one. We could create them, but because this table is written to more than it is read from - there's a counter weighing performance concern. For the reporting, I'm inclined to put indexes on every column because the source & description columns need to be searched for substrings.

We wondered if an indexed view (AKA materialized view) would be an option, where the indexed view would contain all the columns from the EVENT_LOG table but have the appropriate indexes created on the view. Would this get us the performance for reporting, while not impacting writes to the EVENT_LOG table?

share|improve this question
I guess you'd be better off with a copy of the production table for reporting purposes, something like a replicated copy, which you'd fill with the changes from daily operations say once a night, and then you can have any number of indices on it to speed up reporting. –  marc_s Aug 27 '09 at 19:47
You may also improve your sub-string search performance by indexing the text columns in reverse too. –  Matthew Jan 7 '11 at 18:01
add comment

5 Answers

up vote 22 down vote accepted

An indexed view will cause the same issues as an index on the column, because indexed views require with schemabinding, which tie it to the table directly, disallowing you from changing/altering the schema of that table in any way, shape, or form. This includes resizing a column (e.g.-from varchar(50) to varchar(255)), changing a column's data type (e.g.-from double to decimal(18,5)), etc. I've seen them cause a lot of unexpected headaches due to this fact.

My suggestion is to set up a stored procedure or SSIS package that will create a reporting table for you that's run every hour or so. This way, you can index the ever-loving hell out of it and enjoy all the performance benefits that it produces. I shy against reporting from a live, in-progress system. I've actually yet to see the case where this is necessary. For reporting purposes, hour-old information is usually absolutely sufficient to get the job done.

share|improve this answer
Not that there's any immediate plans to alter the data type related info, but once done - wouldn't updating the statistics take care of things? I do agree with you and marc_s that replicating to another table altogether is probably the best option. –  OMG Ponies Aug 27 '09 at 19:53
see CQRS pattern, Udi Dahan does a good job going over it: udidahan.com/2009/12/09/clarified-cqrs –  BlackICE Mar 24 '10 at 19:28
add comment

I would think it would still impact performance, since the indexes on the materialized view need to be updated at some point - it probably doesn't need to be synchronous with the table writes though.

Personally I would put the indexes on the table and measure the write performance myself. You can guess about how much slower writes would be with the indexes on there, but until you actually measure it you're just speculating. It might not make a noticeable difference at all.

share|improve this answer
add comment

" The source & description columns need to be searched for substrings. "

When you Search for substrings on the varchar() columns, SQL Server is not going to use any Indexes.(even if you replicate the table and create Indexes) Indexes are not used , if a wild Character is used at the start of your Search string .

I guess , its better to create a Full-Text Index on 'Source ' and 'Description' , if you need to search for substrings in them.

So my suggestion would be to create a Full Text Index on varchar() columns and make the Change-Tracking as Manual and run it every hour or so when there are no DML ... which would decrease the Loads on the INSERT statements

share|improve this answer
add comment

Not if your are going to be writing to if often, as you'd have the performace cost of the index on your materialised view. Materialised Views are more for data that doesn;t change often.

share|improve this answer
add comment

I ran into a similar problem. Decided to add multi-column index against advice from DBA. On my development machine and the server (with DBA permission), performance for writing increased and reporting was significantly faster (17x) than creating indexes on individual columns. Why, I don't know since I am not a DBA, but I do know the basics, and sometimes that helps you to see through the forest/trees. Therefore, I agree with Eric Pertroelje, you must add indexes and measure the write performance and even measure read performance.

share|improve this answer
add comment

Your Answer


