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! **
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.
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.
https://groups.google.com/forum/m/#!topic/microsoft.public.sqlserver.server/L2USxkyV1uw
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments