How to improve performance of an EF Core query which uses several Includes

Question

I've got this query, which I'll simplify for brevity:

public IQueryable<User> GetByIdAsync(Guid userId)
{
    return MyContext
        .Users
        //Bunch of Includes
            //Most of which have a ThenInclude
                //Followed by another ThenInclude
        .FirstOrDefaultAsync(u => u.Id == userId)
}

When run for around 100 users, it takes over 15 seconds (running locally on my machine). Not great.

I've tried using AsNoTracking(), as well as changing it to use a compiled query like so:

private static Func<MyContext, Guid, Task<User>> _getByIdAsync =
            EF.CompileAsyncQuery((MyContext context, Guid userId) =>
                context
                .Users
                //Same Includes as above
                .Where(u => u.Id == userId)
                .FirstOrDefault());

public IQueryable<User> GetByIdAsync(Guid userId)
{
    return await _getByIdAsync(userId);
}

Still no difference.

I've had a look at this answer for a relevant thread, which suggests using plain old SQL:

https://stackoverflow.com/a/16977218/9778248

And I've had a look at this answer, which mentions clustered indexes:

https://stackoverflow.com/a/55557237/9778248

I certainly can't exclude any of the Includes as the client depends on all this info. Redesigning is also not an option at this stage.

Questions

1. Are there any other options that can improve performance?
2. I can't see any CLUSTERED or NONCLUSTERED tags in any of my child table indexes. Is this worth looking into and if so, can I be pointed to any documentation that explains how I can go about updating using EF (or without)?

Answer 1

You have many ways but it all depends.

1. You have .FirstOrDefaultAsync(u => u.Id == userId) which means that for 100 users you will go to database 100 times so in total 15 000 / 100 == equals 150 ms per request. To improve it try to get all 100 user at once using in clause like .Where(u=> userIds.contains(u.Id))

Example.

private static Func<MyContext, Guid, Task<List<User>>> _getByIdAsync =
            EF.CompileAsyncQuery((MyContext context, List<Guid> userIds) =>
                context
                .Users
                //Same Includes as above
                .Where(u => userIds.Contains(u.Id))).ToListAsync();

1. I know nothing about your data structure but if you can write linq using joins it could be faster, because for many to many within one request EF can go to database each time per dependency.

Example how you can query using joins

var query = (from users in context.Users
join otherTable in context.OtherTable  on users.Id equals otherTable.UserId).ToList();

2. Ef try to fit general purpose but sometimes as you know your data only you can do it better, I used to have similar problem to yours when I have repository methods to get data 1 by one, but then I wrote new method to fetch data using array and that method took care about joined data, via EF it was basically impossible to do it fast. So what I am saying in one request load all one to one, then read from db and using another query go and grab many to many you need.
3. Also you can get sql query

You can get sql using this sample

public IQueryable<User> GetByIdAsync(Guid userId)
{
    var = query = MyContext
        .Users
        //Bunch of Includes
            //Most of which have a ThenInclude
                //Followed by another ThenInclude
     var sql = query.ToSql(); // <--------------------------- sql query
     return query.FirstOrDefaultAsync(u => u.Id == userId)
}

and use sql query to profile and see if its using indexes.

And lastly I really hate methods like this public IQueryable GetByIdAsync(Guid userId) problem is that most of the time you dont need all that includes, but you start using them more and more and become depend on them... This is why I would recommend use EF without repository pattern, EF itself is repository get from database only data you need.