Alex Rivera | Logout

How can i speed up this Indexed View?

Asked 2009-06-17T03:19:27.947
13

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-tow
Edit
Report

1 Answer

20

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.

answered 2009-06-17T04:14:10.160

Your Answer