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