LINQ Query incredibly slow - why?

Before blaming LINQ first find out where the actual delay is taking place Sending the query Execution of the query Receiving the results Transforming the results into local types Binding/showing the result in the UI And any other events tied to this process Then start blaming LINQ ;).

Before blaming LINQ first find out where the actual delay is taking place. Sending the query Execution of the query Receiving the results Transforming the results into local types Binding/showing the result in the UI And any other events tied to this process Then start blaming LINQ ;).

There's happening too much on the server. – Dave Aug 4 at 11:58 @Dave, Use the SQL Profiler to see when and what goes to and from the SQL Server. Strip down the client and use the Stopwatch class to time the steps.

– Erno Aug 4 at 12:06 @Dave, how do you expect me to help you with this level of information? Did you connect to the correct database (are you using a local copy?) are you monitoring for the correct events? – Erno Aug 4 at 12:16 Alright, I found the query which LINQ sends.

SQL Profiler is showing me the same duration as the Stopwatch in my App. – Dave Aug 4 at 12:17 @Dave, could you add the query to your question? – Erno Aug 4 at 12:21.

If I had to guess, I would say "parameter sniffing" is likely, i.e. It has built and cached a query plan based on one set of parameters, which is very suboptimal for your current parameter values. You can tackle this with OPTION (OPTIMIZE FOR UNKNOWN) in regular TSQL, but there is no LINQ-to-SQL / EF way of expolsing this.My plan would be: use profiling to prove that the time is being lost in the query (as opposed to materialization etc) once confirmed, consider using direct TSQL methods to invoke For example, with LINQ-to-SQL, ctx.

ExecuteQuery(tsql, arg0, ...) can be used to throw raw TSQL at the server (with parameters as {0} etc, like string. Format). Personally, I'd lean towards "dapper" instead - very similar usage, but a faster materializer (but it doesn't support EntityRef etc for lazy-loading values - which is usually a bad thing anyway as it leads to N+1).

I.e. (with dapper) List list = ctx. Query(@" select * from table where test == @someString and date >= @dateStartInt and date = {1} and date.

Thanks. I tried the second one, but it takes the same time. – Dave Aug 4 at 12:09 @Dave and you are testing it against the same values?Ok... what are the full DB types of test and date?

For example, is test typed as nvarchar(100), char(20), etc? – Marc Gravell? Aug 4 at 12:27 Yes, it's exactly the same. I did a few runs and compared the duration with StopWatch.

Test is "nvarchar(12)" and date is "int". – Dave Aug 4 at 12:29 @Dave are they regular columns? Calculated values?

Persisted calculated values?...? – Marc Gravell? Aug 4 at 12:33 just regular columns. Actually the second one takes about twice as long to finish as the normal linq query.

I tested it with values, which return 4500 rows. – Dave Aug 4 at 12:34.

Before blaming LINQ first find out where the actual delay is taking place.

If I had to guess, I would say "parameter sniffing" is likely, i.e. It has built and cached a query plan based on one set of parameters, which is very suboptimal for your current parameter values. You can tackle this with OPTION (OPTIMIZE FOR UNKNOWN) in regular TSQL, but there is no LINQ-to-SQL / EF way of expolsing this.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.