Part 8 - Reading with Expand()

Is using Expand() on an entity faster or better?

Let's take a closer look at Expand(). We'll talk about what it is, how to use it, some of its drawbacks as well as some performance numbers for various scenarios when using it.

What Is Expand()?

Expand() offers functionality for querying an additional, related data entity on an entity. When using OData Connected Services to create your OData clients in Finance and Operations, this will let you traverse the relationships declared on a data entity. Each data entity must have a defined key for this functionality to work correctly. This is great for child to parent lookups. If you are looking at a Sales Order Line, you can quickly jump from the Sales Order Line to the Sales Order Header to get something that is on the header only. However, when using OData Connected Services, this will only enumerate a maximum of 1 in the collection for the relationship for consumption in the client. So from child to parent works well because any child typically only has 1 parent. However, any one parent can have 0 to n children and this only will get you 0 or 1 of those children. This may work for some fringe cases but not for most parent-child relationships in Finance and Operations.

How To Use It?

Below is an example of how to use expand.

SalesOrderLine SalesOrderLine = context.SalesOrderLines.Expand(x => x.SalesOrderHeaderV2).Where(x => x.SalesOrderNumber == SalesOrderNumber && x.dataAreaId == DataAreaId).First();

Console.WriteLine(SalesOrderLine.SalesOrderHeaderV2.OrderCreationDateTime);

Performance Implications

What happens when we use expand?

The Tests

We're going to compare 3 things. The first is just reading a Sales Order Line at random 100 times. Next, we're going to read another 100 Sales Order Lines at random but with the Sales Order Header expanded, like the example above. The third scenario we'll test is getting a Sales Order Line as one request then another request to get the Sales Order Header for that Sales Order Line we just fetched. We'll also be running these tests against a VHD based development environment and a UAT environment with the same data.

Results

VHD

We are looking at the average duration of each test by type. We're seeing that the slowest is using a read with an Expand() operation. Next is read combined where we're getting a Sales Order Line as one request and the header as another request. Next, the fastest is just getting a Sales Order Line. At face value, this sort of makes sense. However, let's look at the next chart for a surprise.

UAT

We're looking at the same set of tests and results that were ran against the VHD environment. However, we're getting a very different result for one set of tests. First, let's review average duration in MS by test type compared with the Local VHD. Everything in a UAT environment is faster so that's initially good news. Compared to our last diagram, our slowest set of tests is when we execute 2 requests ( 1 for a line and 1 for its header ). This is more than likely just simple network lag from the tests that are running on my home network and UAT being in the cloud. 

TL;DR and Key Takeaways

  • Differences between Tier 1 and Tier 2+ performance
  • Tier 2+ performance is closer to "real world"

The code can be found at https://github.com/NathanClouseAX/AAXDataEntityPerfTest/tree/main/Projec....

The visuals can be found at https://github.com/NathanClouseAX/AAXDataEntityPerfTest/tree/main/Projec....