Part 10 - Insert Performance for Complicated Entities

What can we learn about insert performance from complicated entities?

What makes a data entity complicated? What makes a data entity slow? Let's take a look at data entity performance when inserting records and see what we can learn. First, let's define a "complicated" entity. I would classify a complicated entity has having lots of data sources ( 5 or more ), having lots of code related to it and in the entity itself. Additionally, because these have lots of data sources and code, depending on what fields are specified, the performance of the entity will change based on the data being specified. As a for instance consider what an entity that accepts addressing info would do when an address is specified compared to what it would do when an address is not specified. 

The Tests

To draw out values and differences, we'll be testing with 2 basic test types: "Minimum" and "Maximum". What we're trying to draw out is the performance difference between creating a record with a small amount of attributes specified and quite a few attributes specified. Does the distinction matter or not so much? We'll be using the Sales Order Header V2 entity for both tests to see how data can change performance.

"Minimum" Test

In this test, we'll be specifying as few values as possible to create a sales order header. We have to specify 5 values when creating a sales order header: Invoice Account, Order Account, Legal Entity, Currency and language, like so.

            DataServiceCollection<SalesOrderHeaderV2> SalesOrderCollection = new DataServiceCollection<SalesOrderHeaderV2>(context);
            SalesOrderHeaderV2 salesOrderHeaderV2 = new SalesOrderHeaderV2();

            SalesOrderCollection.Add(salesOrderHeaderV2);

            // Required Fields
            salesOrderHeaderV2.OrderingCustomerAccountNumber = customerAccount;
            salesOrderHeaderV2.InvoiceCustomerAccountNumber = customerAccount;
            salesOrderHeaderV2.dataAreaId = DataAreaId;
            salesOrderHeaderV2.CurrencyCode = "USD";
            salesOrderHeaderV2.LanguageId = "en-us";

            context.SaveChanges(SaveChangesOptions.PostOnlySetProperties | SaveChangesOptions.BatchWithSingleChangeset);

"Maximum" Test

In this test, we'll be specifying as many values as possible (that don't have backing code/lookup tables) when creating a sales order header.

            DataServiceCollection<SalesOrderHeaderV2> SalesOrderCollection = new DataServiceCollection<SalesOrderHeaderV2>(context);
            SalesOrderHeaderV2 salesOrderHeaderV2 = new SalesOrderHeaderV2();

            SalesOrderCollection.Add(salesOrderHeaderV2);

            // Required Fields
            salesOrderHeaderV2.OrderingCustomerAccountNumber = customerAccount;
            salesOrderHeaderV2.InvoiceCustomerAccountNumber = customerAccount;
            salesOrderHeaderV2.dataAreaId = DataAreaId;
            salesOrderHeaderV2.CurrencyCode = "USD";
            salesOrderHeaderV2.LanguageId = "en-us";

            // Optional Fields
            salesOrderHeaderV2.SalesOrderPromisingMethod = SalesDeliveryDateControlType.CTP;
            salesOrderHeaderV2.DeliveryModeCode = "10";
            salesOrderHeaderV2.DeliveryTermsCode = "FOB";
            salesOrderHeaderV2.PaymentTermsName = "Net10";
            salesOrderHeaderV2.CustomerPaymentMethodName = "Check";
            salesOrderHeaderV2.Email = "Contoso.Retail.San.Diego@customer60.consolidatedmessenger.com";
            salesOrderHeaderV2.CustomerPostingProfileId = "GEN";
            salesOrderHeaderV2.SalesOrderName = "Test Sales Order Id";
            salesOrderHeaderV2.SalesOrderOriginCode = "PHONE";
            salesOrderHeaderV2.SalesOrderPoolId = "03";
            salesOrderHeaderV2.SalesTaxGroupCode = "CA";
            salesOrderHeaderV2.TaxExemptNumber = "35-12345";
            salesOrderHeaderV2.OrderResponsiblePersonnelNumber = "000002";
            salesOrderHeaderV2.OrderTakerPersonnelNumber = "000002";
            salesOrderHeaderV2.DefaultLedgerDimensionDisplayValue = "001--";
            salesOrderHeaderV2.DeliveryAddressCity = "Detroit";
            salesOrderHeaderV2.DeliveryAddressCountryRegionISOCode = "USA";
            salesOrderHeaderV2.DeliveryAddressDescription = "Delivery";
            salesOrderHeaderV2.DeliveryAddressName = "Delivery";
            salesOrderHeaderV2.DeliveryAddressStateId = "MI";
            salesOrderHeaderV2.DeliveryAddressStreet = "123 Main St";
            salesOrderHeaderV2.DeliveryAddressZipCode = "01234";

            context.SaveChanges(SaveChangesOptions.PostOnlySetProperties | SaveChangesOptions.BatchWithSingleChangeset);

The Results

Reviewing the graph below, we can see that the "minimum" test have an average of 909 ms across 100 test while the maximum test averaged 2291 ms.

TL;DR and Key Takeaways

  • Less data specified means better performance
  • Behavior (code execution) may be different when some attributes are specified

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

Blog Main Tag: