How can i speed up this Indexed View?

Answer 1

Did you update the statistics after creating the indexes?

Answer 2

I see an @ sign in the query code in your execution plan. There's a string variable involved.

Sql Server has a NASTY behavior if the string variable's type does not match the type of the string column in the index. Sql Server will ... convert the whole column to that type, perform the fast lookup, and then throw away the converted index so it can do the whole thing again next query.

---

Simon figured it out - but here's more helpful detail: http://msdn.microsoft.com/en-us/library/ms187373.aspx

If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. This function is called indexed view matching, and is supported only in the SQL Server Enterprise and Developer editions.

However, for the optimizer to consider indexed views for matching or use an indexed view that is referenced with the NOEXPAND hint, the following SET options must be set to ON:

So, what's happening here is that indexed view matching is not working. Make sure you're using Enterprise or Developer editions of Sql Server (pretty likely). Then check your SET options according to the article.

Answer 3

I recently built a large database containing hundreds of millions of call detail records and there are some functions I was using in queries and views that I turned into persisted computed columns. This worked out much better because I could index on the computed column.

I wasn't using SQL Enterprise though so I didn't get the opportunity to use indexed views. Is the indexed view supposed to be able to index the deterministic results of the UDF?

Answer 4

I suspect it has to call that function for every row before it can do the comparison in your where clause. I'd expose subject, run the query checking against that directly and see how the times work out. I've generally seen a lot of slowness whenever I modify a value using a function and then use it in the where clause...

Answer 5

What benefit are you looking for by using an indexed view? Is it not possible to properly index the table(s) themselves? Without good justification, you're adding complexity and asking the optimizer to deal with more database objects with less flexibility.

Have you evaluated the same query logic with standard indexes?

Mixing in UDF logic muddies things even more.

Answer 6

What edition of SQL Server? I believe that only Enterprise and Developer Edition will use indexed views automatically, while the others support it using query hints.

SELECT a.PostId
FROM PostsCleanSubjectView a WITH (NOEXPAND)
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

From Query Hints (Transact SQL) on MSDN:

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

Answer 7

If all you want is to persist the return value of a UDF, consider a persisted computed column rather than an indexed view.

Answer 8

Its using that index maybe because the view is using your function. If I recall correcty indexed views had restrictions on which function could be used.

Try this, make a tmp table with the view results, without the indexes and try the query and see how long it takes, do the same thing with indexes and see how long it takes. Do the same queries on the original table using your function and see how long it takes. You'll have a better picture of index vs no index with your function and without. Maybe just try the indexed queries if you need the answer fast.

If its significantly faster it probably means that your function is not compatible with view indexing, try to change it or use a tmp-table or auxiliary table with post-id, processed_subject and use that as a filter to get the post-id, it might not be the most elegant solution, but usually diskspace is a non-issue and query execution time critical.