Tutorial EF Query Optimizations

Tutorial EF Query Optimizations - Printable Version

+- Sinisterly (https://sinister.li)
+-- Forum: Coding (https://sinister.li/Forum-Coding)
+--- Forum: Visual Basic & .NET Framework (https://sinister.li/Forum-Visual-Basic-NET-Framework)
+--- Thread: Tutorial EF Query Optimizations (/Thread-Tutorial-EF-Query-Optimizations)

EF Query Optimizations - phyrrus9 - 12-21-2020

A lot of people shy away from using EF (Entity Framework) citing lack of performance, but really they're just using it wrong. In today's short tutorial, we're going to go over a couple of performance saving features that you probably didn't know about.

1. Read only queries
Many times (like with an API), you just need the data, but don't actually need to write to it. I know too many people that have written ADO wrappers around stored procedures or views to get around the EF overhead for this.
But, where does that overhead actually come from?
The simple answer, it comes from the ability to write data back to the database.
Whenever you make a query with EF, it will add it to a change tracker, as well as do some logic to ensure that only a single instance of it exists. If you don't need this, you can actually turn it off as part of your query, just add the following to the end (before any other .As methods):

Code:
.AsNoTracking()

This will disable almost all of the EF overhead, however this has 2 main side effects:
1. If you have a data set that contains the same row a few or more times, memory usage increases
2. You cannot make changes and write them back to the database.
This will, however, dramatically speed up your query times.

2. Async datasets
It's tempting to write a query like this:

Code:
db.Users
                .Include(u => u.Permissions)
                .ToList()

But, if you intend on looping over this, there is a glaring performance issue: your loop doesn't start until the ENTIRE query finishes.
You could, however, run your loop while it's still executing by using async enumerable:

Code:
IAsyncEnumerable<DAL.Models.Users.User> usersTask = db.Users
    .Include(u => u.Permissions)
    .AsAsyncEnumerable();

but then, how do you loop over that? Easy: use a foreach, like so:

Code:
await foreach (DAL.Models.Users.User user in usersTask)
{
    // code here
}

This is particularly useful if your query takes several seconds to finish running, and your loop also takes a few seconds.

Well, that wraps it up for today's short tutorial. Let me know what you think of this format and if you'd like to see more of it.