SQL Server Query time out depending on Where Claus

Question

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.

Here is the top level query:

Select * 
from  dbo.vwSimpleInvoice i 
inner join dbo.vwRPTInvoiceLineItemDetail d on i.InvoiceID = d.InvoiceID 

When I add this where clause:

Where i.InvoiceID = 109581

The query takes about 3 seconds to run. Similarly when I add this where clause:

Where i.InvoiceID = 109582

it takes about 3 seconds.

When I add this where clause though:

Where i.InvoiceID in (109581, 109582)

I have had to kill the query after about 50 minutes, and it never returns any results.

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.

Any idea where to start troubleshooting this?

Edit:

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:

Select * 
from  dbo.vwSimpleInvoice i 
inner join  
    (Select * from dbo.vwRPTInvoiceLineItemDetail) d on i.InvoiceID = d.InvoiceID 
Where i.InvoiceID in (109581, 109582)

Performance returns to expected levels, around 200 ms. I am now more mystified than ever as to what is occurring...

Edit 2:

Actually, I am wrong. It wasn't rewriting the query like that, I accidentally changed the Where Clause during the rewrite to:

Where d.InvoiceID in (109581, 109582)

(Changed i to d).

Still at a bit of a loss as to why this makes such as massive difference on an Inner Join?

---

Further edit:

Playing around with this even further, I still cannot understand it.

Select InvoiceId from tblInvoice Where CustomerID = 2000

returns:

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)

Runs: 31 Rows returned 110 ms

Select * from dbo.vwRPTInvoiceLineItemDetail
Where InvoiceID in 
(Select InvoiceId from tblInvoice Where CustomerID = 2000)

Runs: 31 rows returned 65 minutes

Answer 1

The Problem you are experiencing is (almost certainly) due to a cached query plan, which is appropriate for some version of parameters passed to the query, but not for others (aka Parameter Sniffing).

This is a common occurance, and is often made worse by out of date statistics and/or badly fragmented indexes.

First step: ensure you have rebuilt all your indexes and that statistics on non-indexed columns are up to date. (Also, make sure your client has a regularly scheduled index maintenance job)

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

This is the canonical reference: Slow in the Application, Fast in SSMS?

If the problem still exists after rebuilding indexes and updating statistics, then you have a few options:

1. Use dynamic SQL (but read this first: The Curse and Blessings of Dynamic SQL)

2. Use OPTIMIZE FOR

3. Use WITH(RECOMPILE)