Sync data from remote api and save it to my localdb

Question

I want to sync data from remote api! something like 1M record! but the whole process talks about 5Mins. as a user experience, that's very bad thing to do! I want the whole process takes less than 1S! I mainly use .net core web api 6.0 with SQLite, EF Core!

I search a lot and I used BulkInsert! and BlukSaveChangesAsync and same it talks a long time! Same it's very bad user experience. I tried the following commented solutions and same problem! I want to make it very fast! as the user! does not feel that there is sync in background or thow.

Note: also I stopped all indexes while inserting the data, to make the process faster! and same problem.

Note: My app is Monolithic. I know I can use something like Azure function but that would be considered as over engineering. I want the simpliest way to solve this! I searched a lot in YouTube, GitHub and Stack overflow and I found nothing that would help me as I wish.

Note: I'm writing the data in two tables! first table: contains only 5 rows. second table: contains 3 rows.

`

 public async Task<IEnumerable<DatumEntity>> SyncCities()
    {
        var httpClient = _httpClientFactory.CreateClient("Cities");
        var httpResponseMessage = await httpClient.GetAsync(
            "API_KEY_WITH_SOME_CREDS");

        if (httpResponseMessage.IsSuccessStatusCode)
        {
            using var contentStream =
                await httpResponseMessage.Content.ReadAsStreamAsync();

            var result = await JsonSerializer.DeserializeAsync<Result>(contentStream);

            var datums = result!.Data;

            if (datums.Any())
            {
                //First solution
                //_context.Datums.AddRange(datums);
                //await _context.SaveChangesAsync();

                //second solution
                //await _context.BulkInsertAsync(datums);
                //await _context.BulkSaveChangesAsync();

                //Thread solution
                //ThreadPool.QueueUserWorkItem(async delegate
                //{
                //    _context.Datums.AddRange(datums);
                //    await _context.BulkSaveChangesAsync();
                //});
            }

            return datums;
        }

        return Enumerable.Empty<DatumEntity>();
    }

Tried: I tried bulkInsert! tried ThreadPool!stopped all indexes! I tried a lot of things. and nothing helped me as I tought! I want the whole process takes less than 1S as the user does not move away from the application! because the bad user experience.

Answer 1

This ThreadPool solved the issue for me:

 if (datums.Any())
        {
            ThreadPool.QueueUserWorkItem(async _ =>
            {
                using (var scope = _serviceScopeFactory.CreateScope())
                {
                    var context = scope.ServiceProvider
                        .GetRequiredService<CitiesDbContext>();

                    context.Datums.AddRange(datums);
                    await context.SaveChangesAsync();
                };
            });
        }