How can i speed up this Indexed View?
indexed-view, sql, sql-server, sql-server-2008, t-sql
Solution
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.
Problem
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 :)