Trying to identify the slowness of repository.InsertManyAsync or dbContext.AddRange #6659

alexander.nikonov created 2 months ago

ABP Framework version: v7.0.1.
UI Type: Angular
Database System: EF Core (Oracle)
Auth Server Separated

I'm trying to insert about 400 entries to the table, the table is simple. The operation is very slow (maybe it's due to the communication between localhost where the app server is and Azure DB server, but I just want to find out before jumping to the conclusions).

Here is the code (chunkSize is 100):

    private async Task TryCreateChunkAsync(List&lt;GeoPoint&gt; geoPoints, int chunkSize)
    {
        var dbContext = await _geoPointRepository.GetDbContextAsync();
        dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
        for (var skip = 0; skip &lt; geoPoints.Count; skip += chunkSize)
        {
            using (var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: false))
            {
                await _geoPointRepository.InsertManyAsync(geoPoints.Skip(skip).Take(chunkSize), autoSave: false);
                await uow.CompleteAsync();
            }
        }
        dbContext.ChangeTracker.AutoDetectChangesEnabled = true;
    }

I don't know if this approach makes sense - I also tried a different approach, using DbContext, prior to that set BatchSize to 100 for our database:

var dbContext = await _geoPointRepository.GetDbContextAsync();
((MyDbContext)dbContext).AddRange(geoPoints);
await dbContext.SaveChangesAsync();

No difference. It is still very slow. There is no third-party tools for bulk operations for Oracle, so this is not an option in any case. Besides, according to some article online, the AddRange approach on 100 entries per DB round-trip is only 1.5 times slower than using bulk insert on such amount of data.

Another thing - I am not sure that ABP logging makes the insertion much slower, but I was unable to turn off these things:

despite applying [DisableAuditing] attribute to Controller or AppService method. Please note, that I don't want to use IgnoreUrls, since the Url might change in future - only the method body is relevant. I also don't want to apply [DisableAuditing] to the entity, because in some other methods I DO want the logging as usual.

What puzzles me is: >

Executed DbCommand (XXXms)

all the DbCommand take more or less appropriate amount of time (each DbCommand takes less than 1 second).

But look at this:

Executed action YYY.Controllers.GeoPoints.GeoPointController.UploadAsync (YYY.HttpApi) in 53197.6736ms

I can't explain this. I turned off the auditing by putting the attribute [DisableAuditing] on the entity class.The time for all the operations does not sum up. Why the total time is so huge, what most of this total time is spent for?? It is somehow related to the fact I'm inserting many records. Because when I do some elementary DB operation - the API request time is normal.

3 Answer(s)