Indexed View vs Indexes on Table

Question

I have the following table

EVENT_LOG:

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?

Solution 1

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.

Solution 2

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.

Solution 3

" 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

Solution 4

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.

Solution 5

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.

View more solutions