title: Query plan caching and performance
body: <p>You can read the backstory if you want, after changing my nonclustered index to a clustered index things started running a lot faster. The problem with the initial query plan taking 2-3 seconds still remain though. However, the keep plan query hint improved that quite a bit.</p>
  
  <h1>BACKSTORY</h1>
  
  <p>I have an inverted index where I store things I wish to lookup in a simple search implementation (though there is nothing simple about search).</p>
  
  <p>When I enter a query like this "ma br" it will create this SQL</p>
  
  <pre><code>SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p0
  INTERSECT
  SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p1
  --@p0 String --'ma%'
  --@p1 String --'br%'
  </code></pre>
  
  <p>For every search term, there's another <code>INTERSECT</code> and <code>SELECT</code></p>
  
  <p>And for the most part this works very well. I've indexed the <code>Term</code> column properly and checked execution plans for potential bottlenecks. Right now the index contains about 150,000 rows and searches happen instantaneously, as expected.</p>
  
  <p>What's a bit irritating though, is that typically the first query of a certain rank takes a lot longer to execute. I bet this is because the query optimizer is reevaluating the execution plan. But how should I deal with this? The more queries I run against the server, the less it stalls, but every other query takes about 2-3 seconds longer. This is not a big deal, but sometimes it's a lot longer and I just fail to see where this is coming from or how to deal with it. It should be lightning fast.</p>
  
  <h1>EDIT</h1>
  
  <p>Schema looks like this:</p>
  
  <pre><code>CREATE TABLE InvertedIndex (
      Term varchar(255) NOT NULL,
      Ordinal tinyint NOT NULL,
      EntityType tinyint NOT NULL,
      EntityID int NOT NULL
  )
  </code></pre>
  
  <p>The two indexes are:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_InvertedIndex ON InvertedIndex (Term) 
  INCLUDE (Ordinal, EntityType, EntityID)
  
  CREATE NONCLUSTERED INDEX IX_InvertedIndex_Reverse ON InvertedIndex (EntityType, EntityID) 
  </code></pre>
  
  <p>This stuff stays, what happens are insert and delete operations when index (InvertedIndex) needs to be updated and evertually a complete rebuild, will this affect the use of a QUERY PLAN?</p>
  
  <p>Here's an example of the complete query it's really slow atm, 3-5 seconds and I can't work out why... The <code>ORDER BY</code> clause is meant to give words that match a certain position higher sort order (occur first in the result set) this however has become exponentially slower for each search term.</p>
  
  <pre><code>WITH Search AS (
      SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p0
      INTERSECT
      SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p1
      INTERSECT
      SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p2
  )
  SELECT p.PersonID
  , p.FullName
  , p.Email
  , p.MobilePhone
  , p.HomeAddress
  , p.HomeCity
  FROM Search AS s
  INNER JOIN Person AS p ON p.PersonID = s.EntityID AND s.EntityType = @pPersonEntityType
  ORDER BY (CASE WHEN @p3 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p0 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END) + (CASE WHEN @p4 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p1 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END) + (CASE WHEN @p5 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p2 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END)
  @p0 String --'ma%'
  @p1 String --'br%'
  @p2 String --'mi%'
  @p3 Int32 --1
  @p4 Int32 --2
  @p5 Int32 --3
  </code></pre>
  
  <p>The point of the above query is to look up all terms in the <code>InvertedIndex</code>, then, for each search term there's an intersect, that's the logical conjuction that I wish to use to constrain the search. Ordinal represents the original position of the word when it was indexed. Each entry in the InvertedIndex represents a tuple and if the search term matches some item of this N-tuple it's considered a better match. That's why I need to do this funky order by with subqueries. But it's really slow.</p>
  
  <h1>ANSWER</h1>
  
  <p>If I change <code>IX_InvertedIndex</code> to a clustered index it improves the query speed by an order of magnitude (I don't know why though):</p>
  
  <pre><code>CREATE CLUSTERED INDEX IX_InvertedIndex ON InvertedIndex (Term) 
  </code></pre>