Multiple includes are very slow

[NigeNigeNige]

Hi, We are using Effort to unit/integration test some EF6 code that usually depends on a SQL Server database. At one point the code runs a statement similar to:

v = context.Stuff
                    .Include("Things")
                    .Include("Things.Children")
                    .Include("Table1.Nav1")
                    .Include("Table1.Nav2")
                    .Include("Table2.Nav1")
                    .Include("Table2.Nav2")
                    .FirstOrDefault(x => x.ThingId == matchObject.ThingId);

When executing against SQL Server with thousands of rows this typically takes around 1 second to execute. Using Effort with only 2 rows in the Stuff collection and nothing in the Included tables it takes about 2 minutes to execute.

This is an issue for us. Is there anything I can do to speed it up?

[markjjordan]

I am discovering this same issue... I have the following statement that takes about 5 minutes:

        investment = db.Investment
            .Include(e => e.Positions.Select(p => p.Transactions))
            //.Include(e => e.Positions.Select(p => p.InvestmentCarry))
            .Include(e => e.RelatedInvestments)
            .Include(e => e.PrivateInvestment)
            .Include(e => e.CoInvestment)
            .FirstOrDefault(e => e.Id == id)
            ;

Anyone have any ideas?

[LauriListak]

I have the same issue regarding includes. Didn't find any other documentation regarding this problem either and it feels very strange, don't even know where to begin with the debugging effort. Did anyone manage to find a solution to this yet?

[robert-j-engdahl]

I'd like to confirm this issue. Replacing some of the Include()s with separate loads and then invoking ChangeTracker.DetectChanges() seem to be a doable work-around. Invocation time went from 1.6s to 200ms. However, when run against a SQL Server the orders of magnitude speed improvement between the two implementations was not observed.

As I see it, there are two work-arounds:

1. Dont use multiple Include()s in any code that is tested using Effort.
2. Upgrade to Entity Framework Core and use their built-in in-memory database.

Personally, I'd like a better reason to upgrade to Entity Framework Core, but on the other hand Effort shouldn't need a major rewrite during its obsolescence.

[JonathanMagnan]

Hello guys,

My developer tried to reproduce it but every with millions of entities, the performance was very reasonable.

I know it has been a long time since this issue has been created but we are currently working on every issue in this project to close them.

Is there anyone that could help us with a project/sample with this issue?

Best Regards,

Jonathan

[ElectricAegis]

See my example here

There is a test which the demonstrates the difference in execution time for a single item in the database:

[robert-j-engdahl]

Brilliant

We experience even worse test times. Perhaps we also have some entities inherit each other.

For comparison it takes about six seconds to spin up a localDb, which then have no performance issues.

/Robert Jørgensgaard Engdahl

Den 26. sep. 2018 kl. 17.31 skrev Hermanus Brummer [email protected]:

See my example here

There is a test which the demonstrates the difference in execution time for a single item in the database:

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[JonathanMagnan]

Hello @ElectricAegis,

Thank for the project, we can run it and see the huge performance difference.

We will look at what can be improved.

I guess @robert-j-engdahl that if we succeed to fix the project provided, it will fix at the same time your performance issue.

[robert-j-engdahl]

It just might.

As I recall it, the problem had to do with reflection generated lambdas that became subject to a lot more run-time security checking after a new .Net version a few years back. I might remember wrongly though.

If that is the case, the above project should test even slower if linked against more libraries (we are at about 100mb when compiling for release).

But enough wild guessing from me for tonight.

/Robert Jørgensgaard Engdahl

Den 26. sep. 2018 kl. 22.04 skrev Jonathan Magnan [email protected]:

Hello @ElectricAegis,

Thank for the project, we can run it and see the huge performance difference.

We will look at what can be improved.

I guess @robert-j-engdahl that if we succeed to fix the project provided, it will fix at the same time your performance issue.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[JonathanMagnan]

Hello,

Just to let you known that we are currently investigating it. That one will not be easy.

We started to investigate and find out that the issue is mainly caused because of the Expression Tree provided by Entity Framework is not optimized. Effort only translate the tree in LINQ Expression without optimizing it either

At the end, a lot of LINQ method is called that could be easily skipped.

The simple example provided by @ElectricAegis has a LINQ expression of 4681 lines. We will start to learn about how SQL Server handle it and try to check if we can do something similar for Effort

[ElectricAegis]

Hi @JonathanMagnan ,

Have you guys had any success with this issue?

[JonathanMagnan]

Hello @ElectricAegis ,

We made a lot of fix and improvement on Effort recently but we, unfortunately, had to stop for a few weeks to keep continuing to concentrate our time on our prime product.

That's for sure an issue we will look again in a few weeks. We will need to understand how we can simplify this LINQ expression that's generated by Entity Framework.

[cgreening]

I know it's almost Christmas, but did you make any progress with this issue?

[JonathanMagnan]

Hello @cgreening ,

We tried some alternative solution today but so far, none work.

Time is currently missing with all new projects here, so for now, nothing will be done but that's for sure something we will continue to look. With the project provided by @ElectricAegis , we can easily reproduce it.

[johansunden]

I also have this problem, been watching this issue for a while, curious to know if there's been any more attempts at solving this issue?

[JonathanMagnan]

Hello @johansunden ,

Honestly, I don't think we investigated further in this issue since our last try in December.

[tynorton]

This is a blocker for us as well. Any progress?