One of the most popular features of SQL 4 CDS has proven to be the ability to change data using INSERT, UPDATE and DELETE statements. To that end, I’ve been looking at how to improve the performance of these types of queries. Whilst I’ve been doing this with a view to improving SQL 4 CDS, the same techniques should also apply to other SDK applications you might create. (Not plugins I’m afraid – more details on that later)

Say we’ve got a list of 5,000 records we need to update – what’s the best way of updating them?

Simple approach: Single updates

The simplest code we could write is something like:

foreach (var entity in toUpdate)
{
  svc.Update(entity);
}

This works just fine, but isn’t the quickest. In my tests, running this in the UK against a UK (crm11) instance, this took over 10 minutes to complete. It feels like we can do better!

ExecuteMultipleRequest

At least some of the time taken for each request is waiting for the request to travel from my PC to the server and for the response to come back. In my case the network round-trip time to the server is about 20ms. If I could get rid of this for 5,000 requests that would save me 1:40.

I can do this by batching my requests with an ExecuteMultipleRequest:

var emr = new ExecuteMultipleRequest
{
  Requests = new OrganizationRequestCollection(),
  Settings = new ExecuteMultipleSettings
  {
    ContinueOnError = false,
    ReturnResponses = false
  }
};
const maxRequestsPerBatch = 100;

foreach (var entity in toUpdate)
{
  emr.Requests.Add(new UpdateRequest { Target = entity });

  if (emr.Requests.Count == maxRequestsPerBatch)
  {
    svc.Execute(emr);
    emr.Requests.Clear();
  }
}

if (emr.Requests.Count > 0)
{
  svc.Execute(emr);
}

This breaks the requests up into batches of 100 and sends each batch in one go to the server. It adds a bit of complexity to the code, but actually gets an even bigger speed-up than from the network time alone – my 5,000 requests are now executed in half the time at 5:19!

Multi-threading

Both the examples so far have run all the requests one at a time, even though the second example sent them in batches. What if we try to run multiple requests concurrently? There’s more complexity again here, but there’s a good sample available on Microsoft Docs which shows how to use the Task Parallel Library and CrmServiceClient to do this safely.

Applying the changes to my code from this sample gets me a slightly better speed improvement compared to ExecuteMultipleRequest – my 5,000 records get updated in 4:52.

ℹ Important
If you try this approach, take note of the “Optimize Connection settings” section of the sample. Without these setting changes you won’t get the expected performance improvement from the rest of the parallel code.

You’re much more likely to run into the service protection limits when using multi-threading. Using the CrmServiceClient takes away a lot of the complexity of working with these. If you’re not using CrmServiceClient, be sure to take note of the errors that could be produced when you hit these limits and retry accordingly.

Combining Approaches

What if I combine these two and use multi-threading with each thread building and executing ExecuteMultipleRequests?

To do this, in the Parallel.ForEach setup you’ll need to create a thread-local ExecuteMultipleRequest as well as a clone of the CrmServiceClient:

Parallel.ForEach(toUpdate,
  new ParallelOptions { MaxDegreeOfParallelism = 10 },
  () => new
  {
    Service = svc.Clone(),
    EMR = new ExecuteMultipleRequest
    {
      Requests = new OrganizationRequestCollection(),
      Settings = new ExecuteMultipleSettings
      {
        ContinueOnError = false,
        ReturnResponses = false
      }
    }
  },
  (entity, loopState, index, threadLocalState) =>
  {
    threadLocalState.EMR.Requests.Add(new UpdateRequest { Target = entity });

    if (threadLocalState.EMR.Requests.Count == maxRequestsPerBatch)
    {
      threadLocalState.Service.Execute(threadLocalState.EMR);
      threadLocalState.EMR.Requests.Clear();
    }
  },
  (threadLocalState) =>
  {
    if (threadLocalState.EMR.Requests.Count > 0)
    {
      threadLocalState.Service.Execute(threadLocalState.EMR);
    }

    threadLocalState.Service.Dispose();
  });

And the result? It’s obviously much more complex code than we started with, but if you’re updating a serious number of records it seems well worth doing. The original 10+ minutes to update 5,000 records has now become a much better 2:34 – about 4x faster!

Summary

There’s some good performance gains to be had with some relatively minor changes to your code. Even better, most of what you need is already in the sample code online!

⚠ Warning
This is for external SDK apps. Don’t try this in plugins! ExecuteMultipleRequest and parallel execution are both explicitly listed as things not to do, and will cause your plugin to fail the solution checker as well as potentially causing errors at runtime.

Testing!

Your mileage will vary. Anything from the number of attributes you’re updating, network latency, plugins, what other users are doing etc. can have a big impact. Some of these might be mitigated more by one approach than another. The only way to know what works best for your scenario is to test it yourself! You can also try tweaking the batch sizes and degree of parallelism to see where the sweet spot is for you.

3 thoughts on “Improving Insert/Update/Delete Performance in D365/Dataverse”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.