LinkedIn is never short on performance tips on .NET - And this time we drag the whole database towards the client, twice!
The Code
The introduction in the post states
[...] Recently optimized a service that was slowing down due to large dataset processing so many records. Swapping ToList() with AsEnumerable() saved over 1.2 seconds per request.
Okay nice. There are several pitfalls with big databases. I am currently working with a >3.5 TB so small changes can have very large effects on the execution time. So let's see the code:
// Slower
var users = context.Users.ToList().Where(u => u.IsActive);
// Faster - stream data, filters later
var users = context.Users.AsEnumerable().Where(u => u.IsActive);
There are many things wrong with both of the codes. Let alone I never saw someone using the "Slower" version in real life. Not that it doesn't exist, but it really smells like a made-up story. Anyhow. What are those two snippets doing?
Snippet 1
Indeed, Snippet 1 is dragging the whole database to the client and then executing the Where
function as part of your everday LINQ in-memory collection. So yes, that is not great at all. Again, never saw that in the wild.
Snippet 2
Snippet 2 does literally the same except - or at least 99%. So, if you call AsEnumerable
on a DbContext
you tell Entity Framework that you want to evaluate, whatever comes after the AsEnumerable
call, on the client. And with client I mean your application (and your database is the server). So, you would drag the whole thing still towards the client.
So why do I say 99%. Well there return type of Snippet 1 is List<User>
where as Snippet 2 returns IEnumerable<User>
. As IEnumerable
is a forward-collection and no one immediately calls ToList
it might be faster. Let's have an ASP.NET Core example:
Imagine you this code:
public IEnumerable<User> GetUsers(...)
{
var stopwatch = Stopwatch.StartNew();
// Snippet 2
IEnumerable<User> users = Snippet2();
stopwatch.Stop(); // Doesn't take much time
return users;
}
The Stopwatch
for snippet 2 would be faster than snippet 1- but as ASP.NET would try to materialize your IEnumerable
the overall time would be the same. You still would drag your database into memory and execute the Where
afterwards.
The solution
Well, for the off-chance that I confused more people than I help:
var users = context.Users.Where(u => u.IsActive).ToList();
I can only repeat myself over and over again: Check the stuff you are reading online (at least try the comments as often times someone points stuff like this out). Furthermore, don't get fooled by the amount of likes or engagement. Neither makes it correct.