How can i speed up this Indexed View?

Question

I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie.

Update: Proof of Solution at the bottom of this post.

Schema

This is what it looks like :-

CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING AS
    SELECT PostId, PostTypeId, 
        [dbo].[ToUriCleanText]([Subject]) AS CleanedSubject
    FROM [dbo].[Posts]

My udf ToUriCleanText just replaces various characters with an empty character. Eg. replaces all '#' chars with ''.

Then i've added two indexes on this :-

Indexes

Primary Key Index (ie. Clustered Index)

CREATE UNIQUE CLUSTERED INDEX [PK_PostCleanSubjectView] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

And a Non-Clustered Index

CREATE NONCLUSTERED INDEX [IX_PostCleanSubjectView_PostTypeId_Subject] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [CleanedSubject] ASC,
    [PostTypeId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Now, this has around 25K rows. Nothing big at all.

When i do the following queries, they both take around 4 odd seconds. WTF? This should be.. basically instant!

Query 1

SELECT a.PostId
FROM PostsCleanSubjectView a 
WHERE a.CleanedSubject = 'Just-out-of-town'

Query 2 (added another where clause item)

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

What have I done wrong? Is the UDF screwing things up? I thought that, because i have index'd this view, it would be materialised. As such, it would not have to calculate that string column.

Here's a screenie of the query plan, if this helps :-

Also, notice the index it's using? Why is it using that index?

That index is...

CREATE NONCLUSTERED INDEX [IX_Posts_PostTypeId_Subject] ON [dbo].[Posts] 
(
    [PostTypeId] ASC,
    [Subject] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

So yeah, any ideas folks?

Update 1: Added schema for the udf.

CREATE FUNCTION [dbo].[ToUriCleanText]
(
    @Subject NVARCHAR(300)
)
RETURNS NVARCHAR(350) WITH SCHEMABINDING
AS 
BEGIN
   <snip>
   // Nothing insteresting in here. 
   //Just lots of SET @foo = REPLACE(@foo, '$', ''), etc.
END

Update 2: Solution

Yep, it was because i wasn't using the index on the view and had to manually make sure i didn't expand the view. The server is Sql Server 2008 Standard Edition. The full answer is below. Here's the proof, WITH (NOEXPAND)

Thank you all for helping me solve this problem :)

Hefty

What is the exact return type of ToUriCleanText. Is it varchar(max) or nvarchar(max) ? – Contention

Update original post with the UDF schema. Returns an NVARCHAR(350). – Hefty

Why not just use REPLACE(REPLACE(REPLACE ....)))? It will probably be substantially faster, and certainly no slower. – Kush

Is it really that much faster? Currently i have .. er.. 15 or so SET lines. – Hefty

Answer 1

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.

Millionaire

I'm using Sql Server Standard Edition. Ok, lets try this ... ##### HOLY MOLLY. #### Instant!!!! What the hell does (NOEXPAND) do? This is what i expected! :) – Hefty

@Pure.Krome, a view can be (and usually is) expanded, which means that SQL Server will query the original views (in your case a base table) for the data. This is somewhat similar to the WITH-statement. However, this also means that any indexes placed on your view will not be used. The query hint NOEXPAND tells you that it should not expand the query into any base views, which will use indexes on it. – Millionaire

Cheers mate - make sence. Totally awesome. Thanks so much. I'm humbled by everyone giving this a good go and you finding the problem. Cheers! – Hefty

WITH (NOEXPAND) : From craptastic to fantastic in a few keystrokes. Thanks for the insight. – Mendelevium

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.

Contention

yep, seen this often. Many folk don't appreciate the effects of data type precedence – Sinuate

interesting. i didn't know about that. I've made sure the datatypes are of the same type. – Hefty

Great edit there David. That's very very helpful and yep -> that was the problem :) Cheers mate ! – Hefty

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?

Sokol

Indexed views are available in all editions, however, I believe that only Enterprise/Developer will automatically use the indexes. You'll need to use query hints (noexpand) if you want to use an indexed view in other editions. – Millionaire

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...

Prorogue

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.

Kush

I agree with the added complexity, but the value of this view is used in logic. As such, i didn't want to add this (non used) data in the original Posts table. I use this view logic to determine a different, final value, which goes into the Posts table. By making it an indexed view, i was hoping to remove the use of temp tables or variable tables AND this view is materialised. – Hefty

Could we see more of your view? My experience is that there are usually less radical, ligher-weight strategies for factoring out temp and variable tables. And for 25K rows, this is awful. – Kush

Answer 6

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

Dari

Answer 7

For some data access layers, such as EF Core it's difficult to add NOEXPAND - so instead you can create an additional view (not schema bound) and add it there. You can call it what you want, but I like to explicitly put NOEXPAND in the name as a reminder.

CREATE VIEW [dbo].[DailySummary_NOEXPAND]
AS
SELECT Col1, Col2, Col3 FROM [dbo].[DailySummary] WITH (NOEXPAND)
END

Also tested on Azure SQL (standard tier)

Important: You can use SELECT * but if the underlying view changes then the column indexes can get out of sync and you can actually get wrong data back. Much safer to specify columns by name.

Hilarity