linq vs sql (or .NET app vs SQL Server management studio)

Question

I have a linq query that retrieves rows from a View based on an id column (where id=@id)

This query takes 4 seconds to run. I have used SQL Server Profiler to inspect the query that is executed by linq, and if i copy that query directly to management studio and execute, the query only takes 56ms.

This exponential time increase is consistent across all linq queries to views in my application. What could be causing this extended execution time in my (WPF) application when the same queries execute < 100ms ?

== EDIT ==

I've managed to isolate further, comments show profiler duration;

/* 3953ms, 111487 reads */
context.SkuView.Where(p => p.TermId == 35 && !p.IsDeleted).ToList(); 

/* 90ms, 173 reads */
context.SkuView.Where(p => p.TermId == 35).ToList(); 

If i paste the (sql rendered) linq queries directly into ssms i get;

/* 250ms, 173 reads */
SELECT * FROM SkuView WHERE TermId == 35 AND IsDeleted = 0

/* 250ms, 173 reads */
SELECT * FROM SkuView WHERE TermId == 35

So the problem has something to do with the read count through linq when using !p.IsDeleted...

Answer 1

Possible culprits are:

• contention. When run from Linq, other application activities are locking rows and thuse causing the query to stall waiting for locks. When run from SSMS, there is no other activity and thus the query finishes fast.
• difference in parameter types. Passing a NVARCHAR parameter for a comparing against a VARCHAR column results in a full scan (index cannot be used due to Data Type Precedence rules). This is caused by wrong LINQ ColumnAttribute. When run from SSMS the query is usualy copied incorectly and the parameter type is changed to VARCHAR.
• cold run vs. warm run. Query is run by LINq first and this warms up the cache (fetches the data from disk to memory). When run again from SSMS there is no wait for IO.

In any case, the tools to investigate are all at your disposal.

• compare the number of Reads from the two queries (RPC:Complete, TSQL:BatchComplete events in Profiler)
• compare the plans. Use Showplan XML event.
• look at what is the LINq query doing: sys.dm_exec_requests wait_type, wait_time and wait_resource columns
• compare the query stats for the two cases: sys.dm_exec_query_stats. things to look for are large diferences between the two cases in logical_reads and physical_reads, indicative of wildly different plans (scan vs. seek), or wild differences in elapsed_time but similar worker_time (indicative of blocking, locks likely).

Answer 2

updating statistics on the db fixed this issue.

exec sp_updatestats

Much thanks to Remus for the learning ;)