title: SQL Server Query time out depending on Where Clause
body: <p>I have a query that uses 3 functions and a few different views beneath it, which are too complex to post here. The odd thing I am experiencing is when running the top level query, having more than 1 search key is causing the query to take around an hour to run, where splitting the query in two takes about 5 seconds per query.</p>
  
  <p>Here is the top level query:</p>
  
  <pre><code>Select * 
  from  dbo.vwSimpleInvoice i 
  inner join dbo.vwRPTInvoiceLineItemDetail d on i.InvoiceID = d.InvoiceID 
  </code></pre>
  
  <p>When I add this where clause:</p>
  
  <pre><code>Where i.InvoiceID = 109581
  </code></pre>
  
  <p>The query takes about 3 seconds to run. Similarly when I add this where clause:</p>
  
  <pre><code>Where i.InvoiceID = 109582
  </code></pre>
  
  <p>it takes about 3 seconds.</p>
  
  <p>When I add this where clause though:</p>
  
  <pre><code>Where i.InvoiceID in (109581, 109582)
  </code></pre>
  
  <p>I have had to kill the query after about 50 minutes, and it never returns any results.</p>
  
  <p>This is occurring on a remote client's server running SQL Server 2008 R2 Express. When I run it locally (also on SQL Server 2008 R2 Express), I don't get the massive delay, the last where clause takes about 30 seconds to return. The client has a lot more data than me though.</p>
  
  <p>Any idea where to start troubleshooting this?</p>
  
  <p>Edit:</p>
  
  <p>After the comments below I rebuilt indexes and stats, which improved performance of the first 2 where clauses, but had no effect on the third. I then played around with the query, and discovered that if I rewrote it as:</p>
  
  <pre><code>Select * 
  from  dbo.vwSimpleInvoice i 
  inner join  
      (Select * from dbo.vwRPTInvoiceLineItemDetail) d on i.InvoiceID = d.InvoiceID 
  Where i.InvoiceID in (109581, 109582)
  </code></pre>
  
  <p>Performance returns to expected levels, around 200 ms. I am now more mystified than ever as to what is occurring...</p>
  
  <p>Edit 2:</p>
  
  <p>Actually, I am wrong. It wasn't rewriting the query like that, I accidentally changed the Where Clause during the rewrite to:</p>
  
  <pre><code>Where d.InvoiceID in (109581, 109582)
  </code></pre>
  
  <p>(Changed <code>i</code> to <code>d</code>).</p>
  
  <p>Still at a bit of a loss as to why this makes such as massive difference on an Inner Join?</p>
  
  <hr>
  
  <p>Further edit:</p>
  
  <p>Playing around with this even further, I still cannot understand it.</p>
  
  <pre><code>Select InvoiceId from tblInvoice Where CustomerID = 2000
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389
  
  Select * from dbo.vwRPTInvoiceLineItemDetail
  Where InvoiceID in 
  (80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389)
  </code></pre>
  
  <p>Runs: 31 Rows returned 110 ms</p>
  
  <pre><code>Select * from dbo.vwRPTInvoiceLineItemDetail
  Where InvoiceID in 
  (Select InvoiceId from tblInvoice Where CustomerID = 2000)
  </code></pre>
  
  <p>Runs: 31 rows returned 65 minutes</p>