Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel