Part 11 - Insert Performance for "Simple" Entities

What can we learn about insert performance from complicated entities?

Simlar to our last entry, what makes a data entity "simple"? Let's re-run the same tests from Insert Performance For Complicated Entities but with a simple entity. I would define a simple entity as an entity that has 1 or 2 tables, can perform it's tasks with a small amount of code, and is build to be light weight for a specific purpose. I would describe it as something that doesn't do much but the few things it does do, it does very quickly.

The Tests

To draw out the differences between specifying a minimalist set of attributes to create a Sales Order header and specifying lots of attributes, we'll be using a custom entity called AAXSalesOrderHeaderV2EntityReadOnly. This is a copy of SalesOrderHeaderV2 with all tables removed except SalesTable. As such, some tests have changed a little so we're not doing an apples-to-apples comparison.

"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<SalesOrderHeaderV2EntityOnlySalesTable> SalesOrderCollection = new DataServiceCollection<SalesOrderHeaderV2EntityOnlySalesTable>(context);

            SalesOrderHeaderV2EntityOnlySalesTable SalesOrderHeaderV2EntityOnlySalesTable = new SalesOrderHeaderV2EntityOnlySalesTable();

            SalesOrderCollection.Add(SalesOrderHeaderV2EntityOnlySalesTable);

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

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

"Maximum" Test

In this test, we'll be specifying as many values as possible when creating a sales order header.

            DataServiceCollection<SalesOrderHeaderV2EntityOnlySalesTable> SalesOrderCollection = new DataServiceCollection<SalesOrderHeaderV2EntityOnlySalesTable>(context);
            SalesOrderHeaderV2EntityOnlySalesTable SalesOrderHeaderV2EntityOnlySalesTable = new SalesOrderHeaderV2EntityOnlySalesTable();

            SalesOrderCollection.Add(SalesOrderHeaderV2EntityOnlySalesTable);

            // Required Fields
            SalesOrderHeaderV2EntityOnlySalesTable.OrderingCustomerAccountNumber = customerAccount;
            SalesOrderHeaderV2EntityOnlySalesTable.InvoiceCustomerAccountNumber = customerAccount;
            SalesOrderHeaderV2EntityOnlySalesTable.dataAreaId = DataAreaId;
            SalesOrderHeaderV2EntityOnlySalesTable.CurrencyCode = "USD";
            SalesOrderHeaderV2EntityOnlySalesTable.LanguageId = "en-us";
            
            // Optional Fields
            SalesOrderHeaderV2EntityOnlySalesTable.SalesOrderPromisingMethod = SalesDeliveryDateControlType.CTP;
            SalesOrderHeaderV2EntityOnlySalesTable.DeliveryModeCode = "10";
            SalesOrderHeaderV2EntityOnlySalesTable.DeliveryTermsCode = "FOB";
            SalesOrderHeaderV2EntityOnlySalesTable.PaymentTermsName = "Net10";
            SalesOrderHeaderV2EntityOnlySalesTable.CustomerPaymentMethodName = "Check";
            SalesOrderHeaderV2EntityOnlySalesTable.Email = "Contoso.Retail.San.Diego@customer60.consolidatedmessenger.com";
            SalesOrderHeaderV2EntityOnlySalesTable.CustomerPostingProfileId = "GEN";
            SalesOrderHeaderV2EntityOnlySalesTable.SalesOrderName = "Test Sales Order Id";
            SalesOrderHeaderV2EntityOnlySalesTable.SalesOrderOriginCode = "PHONE";
            SalesOrderHeaderV2EntityOnlySalesTable.SalesOrderPoolId = "03";
            SalesOrderHeaderV2EntityOnlySalesTable.SalesTaxGroupCode = "CA";
            SalesOrderHeaderV2EntityOnlySalesTable.TaxExemptNumber = "35-12345";

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

The Results

Reviewing the graph below, we can see that the "createSimpleMinimum" test is faster averaged from 100 tests at 155 ms. However, the maximum test, "createSimpleMaximum", isn't all that far behind it. Both perform much better than the items tested in part 10. 

TL;DR and Key Takeaways

  • Less complicated data entities, in general, perform better
  • Less complicated data entities for an insert workload perform better
  • Data entity data source performance should be reviewed for critical workloads
  • One part of a complex data entity may be slow rather than the entire entity
  • Side Note: Putting an entity in an entity is not supported unless you are MSFT who is doing it

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