Poor performance on data serialization

f-camera commented 3 years ago

We are facing serialization performance issue, especially if compared to OData-less Json serialization. Our issue is highlighted but a compared execution we are trying: the comparison is between an OData query and an equivalent query + serialization performed directly on our Data Access Layer (note: the same layer is also feeding OData Controllers). The focus is on the serialization of the response.

Assemblies affected

We are working with Microsoft.AspNet.OData 7.1.0 on a .NET Framework 4.7.2 stack.

Reproduce steps

Example of OData query string: service/odata/WorkOrder? $orderby=Sequence& $filter=NId%20eq%20%27VW_S3000_1%27& $expand=ProducedMaterialItems($select=Id;$expand=DM_MaterialTrackingUnit($select=DM_MaterialId_Id;$expand=MaterialTrackingUnit($select=Id))), FinalMaterial($select=Id;$expand=Material($select=Id;)), ProductionType($select=NId), WorkOrderOperations($select=Id;$expand=ActualConsumedMaterials($select=Id;$expand=DM_MaterialTrackingUnit($select=Id;$expand=MaterialTrackingUnit($select=Id))))& $select=Id

(it is a complex but real query)

Expected result

The construction of the response payload should not take too much time.

Actual result

After the data retrieval, the serialization spend 17.2 seconds producing a payload of 509kB. The same amount of data retrieved from the data access layer, serialized with Newtonsoft.Json spends 17 ms.

Additional detail

In both cases the SQL Server executed statement take between 180-200 ms. The issue could be similar to other I found in the issue list. E.g. #2444 or #1243

habbes commented 3 years ago

Hi @f-camera. There have been some performance improvements made to the WebAPI serialization and ODataWriter in more recent versions. Could you try the latest 7.x version of WebApi and see if performance improves? Could you also share more information about what your controller is doing? It would be great to see code snippets of the two requests being compared.

f-camera commented 3 years ago

Hi @habbes. Thank you for the answer and the hint.

Upgrade attempts results

We tried upgrading to latest releases and indeed the performance in the specific case we were looking at greatly improved. In a reduced case, we passed from 3.5 seconds to 250 milliseconds (average timings) as overall response time. Unluckily though, trying and testing with some more cases, we stumbled on a crash, that is not happening with the currently adopted releases; here a fragment of the callstack:

System.NotSupportedException: Cannot compare elements of type 'Company.DataModel.QuantityType'. Only primitive types, enumeration types and entity types are supported. at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.VerifyTypeSupportedForComparison(Type clrType, TypeUsage edmType, Stack`1 memberPath) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.CreateIsNullExpression(DbExpression operand, Type operandClrType) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) ...

We obtain this every time the OData query uses $select on a property which happens to be (as per Edm model) a ComplexType. We tried then various combinations of versions moving from latest to older ones:

Microsoft.AspNet.OData	Microsoft.OData.Core	Comment
7.5.9	7.9.0	Fast, but crash.
7.4.0	7.6.1	Fast, but crash. This is the oldest combination that gives fast response time.
7.1.0	7.5.2	Slow, but no crash. This is the current stage part of our product.

If I got it correctly, 7.4.0 is the first version with "fixed" performances, I suppose because of PR #2014 .

Some more details about the context

The context here is a bit complex, but it can be summarized as:

we are based on ASP.NET (not Core)
we have a small set of generic Controllers (as in MyController< T > ) that are specialized and mapped at initialization time based on a previous modeling phase
In the controllers we implement some Actions (with EnableQuery attribute set and HandleNullPropagation=HandleNullPropagationOption.False) and in their body we retrieve an IQueryable< T > from our custom data access layer, returned then from the Action
our custom data access layer has many duties and ultimately here involves EF 6.1.3
we are using SQL Server DataProvider in this case (as a product we support also Oracle Database)

Do you have any other suggestions or hints? They would be very welcome. Thanks.

Edit: by reading through Change Logs and issues, I suppose our crash is related to this code https://github.com/OData/WebApi/blob/67f82a668a558de6dcf7221279fca22072547c4d/src/Microsoft.AspNet.OData.Shared/Query/HandleNullPropagationOptionHelper.cs#L71 (and connected) for which I've see some evolution starting from #2142 . Our IQueryable is a custom one that "wraps" an EF6 one, so I assume it is categorized as DataSourceProviderKind.Unknown since the namespace is of course not "well-known". Any suggestion?

Thanks again.

habbes commented 2 years ago

@f-camera if I understand correctly, your code works correctly when there are no null-checks, or rather, when null-checks are handled in the same way they are handled for EF classic?

habbes commented 2 years ago

@f-camera you can manually disable null propagation by setting the following setting in [EnableQuery]:

[EnableQuery(HandleNullPropagation = HandleNullPropagationOption.False)]

Could you try this and let us know if it solves the issue?

KenitoInc commented 1 year ago

@f-camera Was your issue resolved?

OData / WebApi