I have a LINQ to SQL query that generates the following SQL :
exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[SessionVisit] AS [t0]
WHERE ([t0].[VisitedStore] = @p0) AND (NOT ([t0].[Bot] = 1)) AND
([t0].[SessionDate] > @p1)',N'@p0 int,@p1 datetime',
@p0=1,@p1='2010-02-15 01:24:00'
(This is the actual SQL taken from SQL Profiler on SQL Server 2008.)
The query plan generated when I run this SQL from within Query Analyser is perfect.
It uses an index containing VisitedStore, Bot, SessionDate.
The query returns instantly.
However when I run this from C# (with LINQ) a different query plan is used that is so inefficient it doesn't even return in 60 seconds. This query plan is trying to do a key lookup on the clustered primary key which contains a couple million rows. It has no chance of returning.
What I just can't understand though is that the EXACT same SQL is being run - either from within LINQ or from within Query Analyser yet the query plan is different.
I've ran the two queries many many times and they're now running in isolation from any other queries. The date is DateTime.Now.AddDays(-7), but I've even hardcoded that date to eliminate caching problems.
Is there anything i can change in LINQ to SQL to affect the query plan or try to debug this further? I'm very very confused!