CODEWITHSUNDEEP
.netsqlsql-server
athabaska
athabaska

Reputation: 455

Where condition gives timeout

I have a query in sql server 2008 r2 db, which is executed from C# using SqlCommand:

SELECT ob.*, ob2.*
FROM Orderbook ob 
LEFT JOIN Instruments i ON ob.InstrumentId = i.id AND i.TypeId = 4 
LEFT JOIN Instruments i2 ON i.UnderlyingId = i2.id 
LEFT JOIN Orderbook ob2 ON ob2.InstrumentId = i2.id AND ob.Moment = ob2.Moment 
WHERE /*ob.Moment > @sinceDateTime AND*/ i.CodeId = 9 AND DATEPART(minute, ob.Moment) = 0 
ORDER BY ob.Moment

It runs ok with a commented condition in WHERE clause. But when I uncomment it, I get a timeout from server. This confuses me - the query to get records from the beginning of times runs ok, but when I want to get records for today only I get timeout. Any suggestons where to look?

Orderbook table has a clustered index on Moment field and regular index on InstrumentId. Instruments table has indexes for Id and UnderlyingId fields. And I can run both queries ok from management studio, so can't experiment much from there.

Upvotes: 0

Views: 300

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10600

This sounds like a classic case of "parameter sniffing". Try adding OPTION(RECOMPILE) at the end of the query and see if it works.

Here is more information about the issue (if it is the issue): http://www.sommarskog.se/query-plan-mysteries.html

The gist is that SQL server compiles and caches a plan that's good for the first parameter used, but may be bad for later ones. Coming back and running it in SSMS, the connection usually has different options set, so it won't use that cached plan.

Upvotes: 2

Related Questions