Why does adding an unnecessary ToList() drastically speed this LINQ query up?
c#, linq, performance, sql, sql-server-2012
Solution
So, the optimizer chooses a bad way to run the query.
Since you can't add optimizer hints to the SQL to force the optimizer to choose a better plan I see two options.
Add a covering index/indexed view on all the columns that are retrieved/included in the select Pretty ludicrous, but I think it will work, because that index will make it easy peasy for the optimizer to choose a better plan.
Always prematurely materialize queries that include First or Last or Take. Dangerous because as the data gets larger the break even point between pulling all the data locally and doing the First() and doing the query with Top on the server is going to change.
http://geekswithblogs.net/Martinez/archive/2013/01/30/why-sql-top-may-slow-down-your-query-and-how.aspx
https://groups.google.com/forum/m/#!topic/microsoft.public.sqlserver.server/L2USxkyV1uw
http://connect.microsoft.com/SQLServer/feedback/details/781990/top-1-is-not-considered-as-a-factor-for-query-optimization
TOP slows down query
Why does TOP or SET ROWCOUNT make my query so slow?
Problem
Why does forcing materialization using `ToList()` make my query orders of magnitude faster when, if anything, it should do the exact opposite? 1) Calling `First()` immediately ``` // "Context" is an Entity Framework DB-first model var query = from x in Context.Users where x.Username.ToLower().Equals(User.Identity.Name.ToLower()) select x; var User = query.First(); // ** The above takes 30+ seconds to run ** ``` 2) Calling `First()` after calling `ToList()`: ``` var query = from x in Context.Users where x.Username.ToLower().Equals(User.Identity.Name.ToLower()) select x; var User = query.ToList().First(); // Added ToList() before First() // ** Now it takes < 1 second to run! ** ``` Update and Resolution After getting the generated SQL, the only difference is, as expected, the addition of `TOP (1)` in the first query. As Andyz Smith says in his answer below, the root cause is that the SQL Server optimizer, in this particular case, chooses a worse execution plan when `TOP (1)` is added. Thus the problem has nothing to do with LINQ (which did the right thing by adding `TOP (1)`) and everything to do with the idiosyncrasies of SQL Server.