C# awaited entity framework core in memory operation is very slow when performed within loop

Question

I have this kind of code that I am trying to unit test with an in memory ef core database.

public async Task<int> UpdateHistoricalWeatherDataAllLocationsAsync()
    {
        var regions = DalContext.Region.ToList();

        var allInsertAmounts = new List<int>();

        foreach (var region in regions)
        {
            var startDate = DateTime.Now.AddYears(-3);
            var endDate = DateTime.Now;

            while (startDate < endDate)
            {
                // Hit Api
                HttpResponseMessage res= await HitApi(region, batchStartDate, batchEndDate);
                var parsedResult = await ParseResultToStrongType(apixuResponse);

                var recoredsInserted = await WriteToDb(parsedResult, region, batchStartDate, batchEndDate);

                allInsertAmounts.Add(recoredsInserted);

                startDate = startDate.AddDays(30);
            }
        }

        return allInsertAmounts.Count();
    }

So i loop some regions and foreach loop some date ranges.

I hit an api, parse the result, send that to a function that saves it. It is incredibly slow to execute this code.

Here are the the methods in use

private async Task<HttpResponseMessage>HitApi(Region region, DateTime startDate, DateTime endDate) {
    return await Client
                .GetAsync(somestring);
}

private async Task<ApiResponse> ParseResultToStrongType(HttpResponseMessage httpResponse) {
    var responseBody = await httpResponse.Content.ReadAsStringAsync();
    return JsonConvert.DeserializeObject<ApiResponse>(responseBody);
}

private async Task<int> WriteToDb(ApiResponse apiData, Region region, DateTime batchStartDate, DateTime batchEndDate) {
    var batch = new List<Thing>();
    foreach (var data in apiData.list) {
        var thing= new Thing {
             // pull the properties out
        };

        batch.Add(newHistoricalWeather);
    }
    DalContext.Thing.AddRange(batch);
    DalContext.SaveChanges();
}

for some reason awaiting SaveChangesAsync was incredibly slow so I changed to the non async which seems like it has improved matters. but I am not sure why it was slow to begin with...

For 89 regions, with roughly 3 years of monthly batches per region, with an average of 5 regions per country I am (89 * 36) = 3204 times running savechanges which doesnt seem that much. Each of those involves inserting 30 records which is 96,000 records total.

Yes it is in memory, yes it is in a loop inside an async method that calls and awaits 2 async methods before we can even do the write, but still this is taking over 20 minutes now to run this test.

Is it because it is an in memory database?

Answer 1

In the end I still dont know what awaiting the ef core save method was slower than the sync version. But I was able to determine that the issue was the in memory database. I no longer use it since my tests involve large result sets - i use a real test database..