Is foreach with FindAsync is good for performance?

Question

I have a fairly short, but it seems to me, questionable query in terms of performance:

        List<Example> examples = await _dataContext.Examples.ToListAsync();

        foreach (Example example in examples)
        {
            User? user = await _dataContext.Users.FindAsync(example.UserId);

            if (user != null) user.Points += example.Worth * example.Multiplier;
        }

        await _dataContext.SaveChangesAsync();
        await _dataContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE\"Examples\"");

Is this option really bad for performance and are there any alternatives to it? Thank you!

Before thinking in performance, you should think about the logic. This code will update all `user.Points` every time it's called. — Poul Bak, Sep 09 '22 at 06:41
Does this answer your question? [Difference between Find and FindAsync](https://stackoverflow.com/questions/30650722/difference-between-find-and-findasync) — dani herrera, Sep 09 '22 at 06:45
@PoulBak Of course, I understand this, but note that the data from the example (Worth, Multiplier) can be different, which is why, based on my knowledge (which, of course, is not complete), a one-time request cannot be generated — scydev, Sep 09 '22 at 06:52
If your `Example` entity contains reference navigation property to `User` (e.g. `public User User { get; set; }`) as it should, then simply use [eager loading](https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager), e.g. `_dataContext.Examples.Include(e => e.User)...` and then inside the loop simply use `example.User` property instead of `Find{Async}`. Performance will be better due to single vs N+1 database queries for retrieving the data. — Ivan Stoev, Sep 09 '22 at 07:32

score 1 · Answer 1 · answered Sep 09 '22 at 07:39

To use contains to fetch multiple entities is much faster. If the entity list to load is large, it is best to split the contains to a fix size. This will ensure to hit the SQL-query cache on the server.

Your example makes a bit no sense. The points should be updated on "example" creation and the fastest possible in your example would be to write a UPDATE RAW-SQL to calc. and update the points server site.

Here is the better batch lookup method (assuming example-ids are coming from somewhere else then the database)

 List<Example> examples = await _dataContext.Examples.ToListAsync();

 var exampleIds = examples.Select(n => n.UserId).Chunk(10);

        foreach (var ids in exampleIds)
        {
            var userQuery = _dataContext.Users.Where(n => ids.Contains(n.UserId));

           await foreach(var user in userQuery.AsAsyncEnumerable())
           {
              user.Points += example.Worth * example.Multiplier;
           }
        }

        await _dataContext.SaveChangesAsync();
        await _dataContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE\"Examples\"");

As I understand it, I messed up the architecture very much, thanks for the right direction! — scydev, Sep 09 '22 at 07:42

Is foreach with FindAsync is good for performance?

1 Answers1