Performance issues with queries after the project is upgraded from ABP 6.0 to 8.1

[Huangzc88]

Is there an existing issue for this?

• [X] I have searched the existing issues

Description

When I upgraded the project from abp6.0 to abp8.1, I found that many data queries became very slow, even if the amount of data was only a few hundred, is there something wrong with it?

I've tried manually turning off the default entity tracking behavior in ABP 8.0 and it's as fast as it was in ABP6.0, but we're pretty sure that EF Core has entity tracking enabled by default, so I'm not quite sure why it was fast when entity tracking was enabled by default in ABP6.0 and is now quite slow in ABP8.0.

At the moment we can only solve the problem of slow queries by disabling the DisableEntityChangeTracking feature or by disabling entity tracking in the repository, but it is not clear why upgrading from 6.0 to 8.0 will cause such a big difference in query efficiency.

Reproduction Steps

No response

Expected behavior

No response

Actual behavior

No response

Regression?

No response

Known Workarounds

No response

Version

8.1

User Interface

MVC

Database Provider

EF Core (Default)

Tiered or separate authentication server

None (Default)

Operation System

Windows (Default)

Other information

Database : postgresql v 10.1

[Huangzc88]

In addition, we compared the difference between the SQL parsed in version 6.0 and version 8.1, and found that the parsed SQL has not changed, but the query has become much slower, only even if there are only dozens of data queries out and put in memory, it takes more than 10 seconds, and in version 6.0, when the code has not changed, it does not take 1 second to query out.

We manually turned off EF's entity tracking queries were just as fast as they were in 6.0, but 6.0 was also the default entity tracking, so we never found the real reason for the performance gap.

[maliming]

hi

Can you use 8.1 template project with some data to show the performance issues?

[Huangzc88]

hi

Can you use 8.1 template project with some data to show the performance issues?

Thanks for the reply, okay, we'll be testing the performance with the ABP 8.1 version of the template project.

[developerMCSI]

If it helps, please read https://support.abp.io/QA/Questions/7028/With-Abp-810-InsertMany-became-very-slow

[Huangzc88]

If it helps, please read https://support.abp.io/QA/Questions/7028/With-Abp-810-InsertMany-became-very-slow

Thank you very much for your answer, I believe you are experiencing a similar problem as me, I guess there is a problem with the entity tracking of version 8.1, in the case of about 1000 pieces of data, when I manually turn off the entity tracking, the query speed is more than 10 times faster.

According to the recommendations of Microsoft's official documentation on batch insertion of large amounts of data, you can set the AutoDetectChangesEnable of entity tracking to false, because the new entity database does not exist and does not need to be tracked, you can test whether the performance of batch inserts is improved after disabling.

I saw that ABP has just released version 8.1.1, and one of them is to fix the entity tracking performance problem, and I will test whether the performance returns to the original 6.0 version

[Huangzc88]

hi

Can you use 8.1 template project with some data to show the performance issues?

Hi,

Based on your suggestion, I created 2 MVC projects based on the PosgreSQL database using ABP's scaffolding commands, one with version 6.0 and one with version 8.1, then completely simulating my actual business requirements code, both projects created the same entity and the same mapping configuration, use the same database table data, and finally used the same warehouse query code for query testing.

The following screenshot is a performance comparison:

v8.1

v6.0

980 pieces of data, it only takes 1 second with entity tracking enabled by default in 6.0, 12 seconds in 8.1, 1 second after manually disabling entity tracking in 8.1, and 0.8 seconds after manually disabling entity tracking in 6.0.

In order to rule out whether it is EF Core or PGSQL driver package issue of version 8.1, I also deliberately simulated a console program based on the native writing of EF Core 8.0, and simulated the same query with the native EF Core writing method, which only takes less than 2 seconds.

Therefore, I speculate that it is most likely an issue with ABP 8.1, not EF Core or PG package with 8.0.

[maliming]

Yes, 8.1.1 will fix this.

[developerMCSI]

@maliming, just out of curiosity, if it's a bug why did you remove the bug label?

[maliming]

@maliming, just out of curiosity, if it's a bug why did you remove the bug label?

Thanks, I added the problem label.

[maliming]

hi all

It solves the domain event problem, but there's still some performance loss if EF Core tracks a large number of entities, and I'll continue to work on that.

[maliming]

I found access to the IsModified of EF Core CollectionEntry will lose performance.

But our feature logic needs it, I will try to find a suitable solution.

[Huangzc88]

I found access to the IsModified of EF Core CollectionEntry will lose performance.

But our feature logic needs it, I will try to find a suitable solution.

Hi,

Thank you very much for your efforts, and the v.8.1.1 release came out so quickly to fix a serious performance issue caused by a sex entity tracking bug.

After I updated to version 8.1.1, the speed was indeed much faster than that of 8.1, but the same 1000 data queries still had a speed difference of about 3 times compared to the previous version 6.0.

So I'm also sure that version 8.1.1 should still have some performance issues, just not as serious as version 8.1.

I am very much looking forward to the resolution of this issue, thanks again.

[maliming]

I'm getting close to the final solution.

[maliming]

hi all

This https://github.com/abpframework/abp/pull/19559 will fix the problem at all.

@Huangzc88, if you have time, you can test it via source code references.

Test results:

[Fact]
public async Task Test()
{
    var entities = new List<AppEntityWithNavigations>();
    for (var i = 0; i < 10000; i++)
    {
        entities.Add(new AppEntityWithNavigations(Guid.NewGuid(), $"TestEntity{i}"));
    }

    var stopWatch = new Stopwatch();
    stopWatch.Start();

    await AppEntityWithNavigationsRepository.InsertManyAsync(entities, true);

    stopWatch.Stop();
    var ts = stopWatch.Elapsed.TotalSeconds;

    //10000 entities in 14s without AbpEfCoreNavigationHelper
    //10000 entities in 16s with AbpEfCoreNavigationHelper

}

[Huangzc88]

Hi @malimi，

The current version of my project is 8.1.1, and since the ABP version hasn't released a new version with the latest fixes yet, what temporary solution should I use to address the issue?

Should I create my own corresponding helper class and rewrite the corresponding method based on your latest helper source code, using service replacement?

[maliming]

hi @Huangzc88

Can you share a simple test app? I will test it locally.

[Huangzc88]

Hi @maliming ,

Details can be found in the attached document.

Acme.BookStore.zip

The current version of my test project is 8.1.1, and the test results show that it takes over 3 seconds to retrieve the data. In version 8.1, it took around 12 seconds to retrieve the data, while in version 6.0, it only took about 1 second.

You can use ABP CLI to create 3 corresponding versions of the template project, and then use the same code and data to execute it, and the speed difference is very clear in comparison.

Ps: The database I tested is postgresql, the client version is 10.0, I tested the performance impact of different high and low versions, and found that the query speed is almost the same, so you can choose a postgresql version that is not too old to test.

[Huangzc88]

Hi @maliming

Regarding all test data being packed in the project's database file, you can create a database, then directly execute the sql file to create the corresponding tables. Afterward, adjust the database configuration, and you can run the project directly for testing.

[maliming]

The results are very well.

8.1.0:

TotalSeconds: 4.191923

8.1.1:

TotalSeconds: 0.3844697

https://github.com/abpframework/abp/pull/19559 :

TotalSeconds: 0.0280314

[Huangzc88]

Hi @maliming,

Thank you very much for your efforts, the performance improvement is quite obvious, and it seems that your change is very effective.

I think this is a serious performance issue, and I recommend releasing a new version asap.

If it takes a while for a new version to be released, is there any temporary solution for my project to synchronize this change without upgrading the version?

[developerMCSI]

@maliming Can you also add the times obtained with the abp 8.1.0 rc.4 version and, if possible, with the abp6.0 version? Thanks

[maliming]

Sure, I will test it this weekend.

[maliming]

8.1.0:

TotalSeconds: 3.7121267

8.1.0 rc4:

TotalSeconds: 0.0258139

8.1.1:

TotalSeconds: 0.3825125

https://github.com/abpframework/abp/pull/19559 :

TotalSeconds: 0.0280867

[AbpFan]

Indeed, querying 340000 rows of data on 8.0.5 takes about 5 seconds, and upgrading to 8.1.0 takes an immeasurable amount of time. Currently, no improvement has been found in version 8.1.1.

[Huangzc88]

Hi @maliming

I found that the milestone associated with this issue has been changed to 8.0-patch, and the fix code for this issue has been merged into the rel-8.0 branch.

What's the next stable version that includes this fix, will it be 8.1.2?

[maliming]

hi @Huangzc88

8.0.6 and 8.1.2

[JanneHarju]

How soon they are coming? Any estimate? Because our options are wait or downgrade to 8.0.5. And if we downgrade to 8.0.5 I wonder might there be problem with that new NormalizedName field in Tenant table which one was added in 8.1.0.

[Huangzc88]

I'm also waiting, I hope it will be released soon, after all, the performance problem is a serious problem and should be fixed asap, it has been almost 1 month since the 8.1.1 version was released, but the new version has not been released yet.

[maliming]

I guess 8.0.6 may be available next week.

[OlliPasanen-Git]

Since the 8.1.1 version is also not working, when will this be fixed to 8.1.x versions?

[maliming]

The new patch version will be released within 1-2 weeks.

[Huangzc88]

I found out that version 8.1.2 has been released, and according to the release notes, it includes a fix for this issue. Tomorrow, I will test the performance.