Part 14 - Delete Performance

What can we learn about delete performance when using multiple types of entities?

When working with OData, performing a delete is more than likely the operation that is performed the least, at least inside Finance and Operations. However, there may be a few tricks we can use to make the operation as fast as possible so we don't waste resources. First, we simply want simply identify the thing we are deleting, scope it, then delete it with business logic and referential integrity rules also being executed. To contextualize this, let's say I want to delete a Sales Order header. So I need the ship address for the sales order? or the customer group? or even the customer? Not specifically, as long as I can identify the one thing I want to delete. This makes a delete operation potentially a unique case.

The Tests

Let's run some tests to find the fastest way to delete a record. We'll run a test deleting a Sales Order Header with the OOTB SalesOrderHeaderV2 entity. This will be our "maximum" test meaning its the most complicated entity as it supports all CRUD operations. Another test will be using a custom data entity that contains only SalesTable related fields but is otherwise very similar to SalesOrderHeaderV2. This will be our minimum test as it supports all CRUD operations for a single table. Finally, the last test will be another custom entity that only has table SalesTable in it and only exposed 1 field; SalesId. This will be a single unit test as it supports a delete operation and nothing else. This last entity is the smallest unit entity we could produce to be able to manipulate a Sales Order Header.

"Maximum" Test

In this test, we'll just be using out of the box entity SalesOrderHeaderV2. We'll be finding a record on a known deletable state, scoping it then deleting it.

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

            salesOrderHeaderV2 = context.SalesOrderHeadersV2.Where(x => x.SalesOrderNumber == SalesOrderNumber && x.dataAreaId == DataAreaId).First();

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

"Minimum" Test

In this test, we'll be using the same process as above but only using a different entity. The entity for this test is only contains the table SalesTable. The code for this is nearly identical to the code above.

                    var SalesOrderHeadersV2EntityOnlySalesTable = context.SalesOrderHeadersV2EntityOnlySalesTable.Where(x => x.SalesOrderNumber == SalesOrderNumber && x.dataAreaId == DataAreaId).First();

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

"Single Unit" Test

For this test, we're using an entity that only has 1 column exposed on the data entity; the primary key for the Sales Orders - SalesId. The data entity we're using can only be used to delete records and has the same delete related logic as the out of the box entity SalesOrderHeaderV2. We're really trying to determine what part of a fully monitored delete operation is the acquisition of the buffer or the actual deletion. The code looks like this:

                    var AAXSalesOrderHeaderV2EntityOnlySalesOrderNumbers = context.AAXSalesOrderHeaderV2EntityOnlySalesOrderNumbers.Where(x => x.SalesOrderNumber == SalesOrderNumber && x.dataAreaId == DataAreaId).First();

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

 

The Results

After running 100 tests and recording the results, we can review the results below.

Our slowest workload is that using the standard OOTB entity of SalesOrderHeaderV2. If you think about it, it has the additional overhead of getting everything related to a Sales Order Header like address plus all the related fields from various data sources. The second place for performance was using a custom data entity that only exposed SalesTable fields. Lastly, our number one spot goes to the data entity purpose written to support a delete workload (and only a delete workload). You'll note that all tests include the time it took to get a record buffer for an entity as well as delete it. Depending on your scenario, this may not be a valid test but my assumption is that this will be an interactive operation so the backend will have to re-scope the buffer and not use whatever may be displayed on screen for the user.

TL;DR and Key Takeaways

  • A less complicated entity performs better
  • a single unit type entity is the fastest operation possible but that data entity can only do that one thing
  • more simple single table data entities for simple or specific workloads may be beneficial for your scenario

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

Blog Main Tag: