Open Closed

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


User avatar
0
alexander.nikonov created
  • 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<GeoPoint> geoPoints, int chunkSize)
    {
        var dbContext = await _geoPointRepository.GetDbContextAsync();
        dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
        for (var skip = 0; skip < 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)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    Unfortunately, this is an EF Core and Oracle issue.

    You might consider using SQL directly for bulk insertion

  • User Avatar
    0
    alexander.nikonov created

    @liangshiwei - ok, if you are sure that this is solely EF Core / Oracle issue, please close the ticket and restore the points. Thanks.

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    ok. have a good day.

Made with ❤️ on ABP v9.1.0-preview. Updated on December 05, 2024, 12:19