EntityFramework-Plus: IncludeOptimized slows down queries

I noticed an interesting performance issue while trying to take advantage of IncludeOptimized. Contrary to my expectation, IncludeOptimized slows down my queries instead of speeding them up.

My use case is a report generator in a real world system that pulls out data for a ~70.000 row .csv file. The code performs 8 Load calls, each with a bunch of Include calls attached to them. According to a profiling, these Load calls take 63 seconds in the current implementation, with hotspots as follows:

37 seconds: What appears to be the underlying database queries: System.Data.Common.DbCommand.ExecuteReader(CommandBehavior)
23 seconds: What appears to be EF materialization: System.Data.Entity.Core.Common.Internal.Materialization.Shaper+ObjectQueryNestedEnumerator`1.MoveNext()
3 seconds: What appears to be EF materialization: System.Data.Entity.Core.Common.Internal.Materialization.Shaper+SimpleEnumerator`1.MoveNext()

Replacing the Include calls with IncludeOptimized increases the total time to 87 seconds, with these hotspots:

14 seconds: The underlying database queries. Now 23 seconds faster: System.Data.Common.DbCommand.ExecuteReader(CommandBehavior)
20 seconds: The first materialization path from before, taking approximately the same time: System.Data.Entity.Core.Common.Internal.Materialization.Shaper+ObjectQueryNestedEnumerator`1.MoveNext()
25 seconds: The other materialization path from before, now significantly slower: System.Data.Entity.Core.Common.Internal.Materialization.Shaper+SimpleEnumerator`1.MoveNext()
27 seconds: A lazy loading related hotspot that was not present in the original test. Note that lazy loading was disabled for both tests! Z.EntityFramework.Plus.QueryIncludeOptimizedLazyLoading.SetLazyLoaded(Object, List)

In other words: The 23 second improvement in query time entailed an additional 19 seconds of materialization time and 27 seconds of SetLazyLoaded.

Is this a performance issue with Entity Framework Plus, or is my use case just not appropriate for the use of IncludeOptimized?

About this issue

Original URL
State: open
Created 6 years ago
Comments: 22 (11 by maintainers)

Most upvoted comments

Yup,

This was a good try, however 😉

The good thing is we will re-visit this feature during the year through the project: https://entityframework-classic.net/

So some part will be optimized and we might find out how to better handle this or have our own optimized entity materialization

JonathanMagnan on Apr 12, 2019

Hi @JonathanMagnan & @Zero3,

I’m having a similar problem where IncludeOptimized is unexpectedly slower than Include. Given the history of this issue it seemed I should have a go at reproducing in a simple little sample project, so I’ve done that and attached. It’s a Console application with output like:

Clear DB: 00:00:02.7041862
Generate test data: 00:00:00.0343448
Insert test data: 00:00:53.2880158
Warm up: 00:00:09.0352818
>> Include: 00:00:05.3542852 total, 00:00:00.3041450 DB command execution.
>> IncludeOptimized: 00:00:06.0759931 total, 00:00:00.0003531 DB command execution.
Press any key to continue . . .

ConsoleApplication1.zip

cduff on Apr 6, 2019

Hello @Zero3 ,

As said, I don’t mind to reopen it, I just cannot pass more time at this issue anymore unless someone provides us with more information. While this project is free on your side, that’s not the same on my side, I need to pay developers to look and try to reproduce it.

So I will reopen the issue and hope someone could give us some additional hint.

Best Regards,

Jonathan

JonathanMagnan on Oct 22, 2018