SQL Server Query time out depending on Where Clause

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

Aikoail

What does the execution plan look like? – Offal

Have you tried changing that "IN" operator with an OR? Say "Where d.InvoiceID=109581 OR d.InvoiceID=109582". May change the execution plan! But the real way to get help is to post the execution plan of the single ID vs. 2 IDs in the WHERE, so others can analyze the difference and understand why it's that way. – Pharmacology

I had to rewrite the query from scratch. I was not able to isolate anything that was causing the behaviour noted above. The original query still behaves like this, I rewrite the entire tree to avoid the join in the end. Thanks for help. – Aikoail

Is InvoiceID a varchar or an INT on both tables? – Raylenerayless

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)

Footlambert

Ok, rebuilt all indexes and statistics, checked both the estimated execution plan and the actual plan for a single InvoiceID and adjusted one index as it suggested. The individual InvoiceID executions are now down to around 48 ms each, which is a vast improvement. But the last where clause is still running until I click stop, left it around 20 minutes that time. Thanks for your help so far, any further suggestion? – Aikoail

Not sure if you get notified, but just edited my question after trying your suggestions. Any ideas? Thanks again. – Aikoail