title: Table Scan and Eager Spools on IN Predicate
body: <p>I'm running what I think is a fairly simple query, and I'm having getting absolutely atrocious return times on the query. When running the query, a sub query as a filter in an <code>IN</code> predicate is performing an Index scan and an Index Spool when used as part of the query, but runs an Index Seek when used outside of the <code>IN</code> predicate. I have no idea why, but the query is taking nearly 30 seconds to return zero records...</p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT DISTINCT
      C.County
      , S.State
      , C.County_ID
  FROM
      Leads L 
      INNER JOIN Inventory I ON L.Deleted = 0 AND L.Inv_ID = I.Inv_ID
      INNER JOIN County C ON C.County_ID = I.County_ID
      INNER JOIN State S ON C.State_ID = S.State_ID AND S.Active = 1
      INNER JOIN Contacts ON L.Contact_ID = Contacts.ID AND Contacts.Deleted = 0
  WHERE
      L.Acct_ID = 204940
      OR L.Acct_ID IN (
          SELECT Accounts.Acct_ID FROM Accounts (NOLOCK) WHERE Accounts.Parent_Acct_ID = 204940
      )
  ORDER BY
      S.State
      , C.County
  </code></pre>
  
  <p>Here's the relevant part of the execution plan of that query. It's running a scan on the Accounts table in the <code>WHERE</code> clause, with an estimated number of rows to be returned of over 260,000. The subselect actually returns 0 records.</p>
  
  <p><img src="https://i.stack.imgur.com/vjDEX.png" alt="enter image description here"></p>
  
  <p>When I run that simple select outside the above query, I get a straight index seek, against the exact same Index. The subselect returns 0 records. If I run the query without the <code>OR</code> statement I get ms response times, if I run the query with the <code>OR</code> statement, the query takes nearly 30 seconds to complete.</p>
  
  <p>Here's the index that's being used for both the Scan and the Seek on the Accounts table (I know, it's a dta index, not my original database...)</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [_dta_index_Accounts_5_144719568__K32_50] ON [dbo].[Accounts] 
  (
      [parent_Acct_ID] ASC
  )
  INCLUDE ( [CompanyState]) 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>How do I get the subselect to run an Index Seek instead of a Scan, and return in any sort of reasonable amount of time?</p>