title: How can i speed up this Indexed View?
body: <p>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.  </p>
  
  <h3>Update: Proof of Solution at the bottom of this post.</h3>
  
  <h3>Schema</h3>
  
  <p>This is what it looks like :-</p>
  
  <pre><code>CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING AS
      SELECT PostId, PostTypeId, 
          [dbo].[ToUriCleanText]([Subject]) AS CleanedSubject
      FROM [dbo].[Posts]
  </code></pre>
  
  <p>My udf <code>ToUriCleanText</code> just replaces various characters with an empty character. Eg. replaces all '#' chars with ''.</p>
  
  <p>Then i've added two indexes on this :-</p>
  
  <h3>Indexes</h3>
  
  <p>Primary Key Index (ie. Clustered Index)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And a Non-Clustered Index</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now, this has around 25K rows. Nothing big at all.</p>
  
  <p>When i do the following queries, they both take around 4 odd seconds. WTF? This should be.. basically instant!</p>
  
  <h3>Query 1</h3>
  
  <pre><code>SELECT a.PostId
  FROM PostsCleanSubjectView a 
  WHERE a.CleanedSubject = 'Just-out-of-town'
  </code></pre>
  
  <h3>Query 2 (added another where clause item)</h3>
  
  <pre><code>SELECT a.PostId
  FROM PostsCleanSubjectView a 
  WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1
  </code></pre>
  
  <p>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.</p>
  
  <p>Here's a screenie of the query plan, if this helps :-
  <img src="https://i.stack.imgur.com/dwuPB.png" alt="alt text"></p>
  
  <p>Also, notice the index it's using? Why is it using that index?</p>
  
  <p>That index is...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So yeah, any ideas folks?</p>
  
  <h3>Update 1: Added schema for the udf.</h3>
  
  <pre><code>CREATE FUNCTION [dbo].[ToUriCleanText]
  (
      @Subject NVARCHAR(300)
  )
  RETURNS NVARCHAR(350) WITH SCHEMABINDING
  AS 
  BEGIN
     &lt;snip&gt;
     // Nothing insteresting in here. 
     //Just lots of SET @foo = REPLACE(@foo, '$', ''), etc.
  END
  </code></pre>
  
  <h3>Update 2: Solution</h3>
  
  <p>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, <code>WITH (NOEXPAND)</code>
  <img src="https://i.stack.imgur.com/NXUi5.png" alt="alt text"></p>
  
  <p>Thank you all for helping me solve this problem :)</p>