Dataverse: Comparing Create vs ExecuteMultipleRequest vs Azure Service Bus – ServiceBusTrigger

Today we will learn how to write code for bulk create records (e.g for custom data migrating purposes). I will give two methods (plus with different settings to see if they have positive differences) and compare the time to see the best results. Without further a do, let’s go!

For the code itself, this is the main code:

using Azure.Messaging.ServiceBus;
using DataverseClient;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Newtonsoft.Json;

var builder = Helper.CreateHostBuilder().Build();
var serviceProvider = builder.Services;

var service = serviceProvider.GetRequiredService<IOrganizationServiceAsync2>();

await Log(async () =>
{
    for (int i = 1; i <= 10000; i++)
    {
        var contact = new Entity("contact");
        contact["firstname"] = "Temmy";
        contact["lastname"] = "Normal " + i;
        // As we don't log anything, we can set ConfigureAwait(false) to make it faster
        await service.CreateAsync(contact).ConfigureAwait(false);
    }
}, "Create 10k Contact");

await Log(async () =>
{
    // Prepare data (locally)
    var list = new List<Entity>();
    for (int i = 0; i < 10000; i++)
    {
        var contact = new Entity("contact");
        contact["firstname"] = "Temmy";
        contact["lastname"] = "Multithreading " + i;

        list.Add(contact);
    }

    // Split + Execute per 100 data
    var data = list.ToArray().Split(100).ToArray();

    var threads = new List<Task>();
    foreach (var group in data)
    {
        var task = Task.Run(() =>
        {
            var tempService = serviceProvider.GetRequiredService<IOrganizationServiceAsync2>();

            var req = new ExecuteMultipleRequest
            {
                Settings = new ExecuteMultipleSettings { ReturnResponses = true, ContinueOnError = true },
                Requests = new OrganizationRequestCollection()
            };
            req.Requests.AddRange(group.Select(entity => new CreateRequest { Target = entity }));

            tempService.ExecuteAsync(req);
        });

        threads.Add(task);
    }

    // Wait all the tasks finished
    Task.WaitAll(threads.ToArray());
}, "Multithreading + ExecuteMultipleRequest per 100");

await Log(async () =>
{
    // Prepare data (locally)
    var list = new List<Entity>();
    for (int i = 0; i < 10000; i++)
    {
        var contact = new Entity("contact");
        contact["firstname"] = "Temmy";
        contact["lastname"] = "Multithreading2 " + i;

        list.Add(contact);
    }

    // Split + Execute per 100 data
    var data = list.ToArray().Split(200).ToArray();

    var threads = new List<Task>();
    foreach (var group in data)
    {
        var task = Task.Run(() =>
        {
            var tempService = serviceProvider.GetRequiredService<IOrganizationServiceAsync2>();

            var req = new ExecuteMultipleRequest
            {
                Settings = new ExecuteMultipleSettings { ReturnResponses = true, ContinueOnError = true },
                Requests = new OrganizationRequestCollection()
            };
            req.Requests.AddRange(group.Select(entity => new CreateRequest { Target = entity }));

            tempService.ExecuteAsync(req);
        });

        threads.Add(task);
    }

    // Wait all the tasks finished
    Task.WaitAll(threads.ToArray());
}, "Multithreading + ExecuteMultipleRequest per 200");


var serviceBusClient = serviceProvider.GetRequiredService<ServiceBusClient>();
var sender = serviceBusClient.CreateSender("crm-in");

await Log(async () =>
{
    for (int i = 1; i <= 10000; i++)
    {
        var message = new Contact { FirstName = "Temmy", LastName = "Service Bus " + i };
        // Run and forget
        await sender.SendMessageAsync(
            new ServiceBusMessage(JsonConvert.SerializeObject(message)))
            .ConfigureAwait(false);
    }
}, "ServiceBusClient");

Console.WriteLine("Ended..");

static async Task Log(Func<Task> action, string information)
{
    var start = DateTime.Now;
    await action.Invoke();
    var end = DateTime.Now;
    Console.WriteLine($"Run '{information}'. Start at {start}. End at {end}. " +
                      $"Total {(end - start).TotalMinutes}..");
}

public static class Extensions
{
    public static IEnumerable<IEnumerable<T>> Split<T>(this T[] arr, int size)
    {
        for (var i = 0; i < arr.Length / size + 1; i++)
        {
            yield return arr.Skip(i * size).Take(size);
        }
    }
}

public class Contact
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Baseline (14-24)

When we do comparing, of course we need to know the baseline. For this demonstration, we will create 10k contact records. For the code itself there’s nothing special here. We just need to create entity with attribute firstname and lastname. Then we need to call IOrganizationServiceAsync2.CreateAsync to create the data inside our Dataverse. Because we are not logging anything, we can set ConfigureAwait as false (fire and forget method).

ExecuteMultipleRequest with 100 data (26-64) vs ExecuteMultipleRequest with 200 data (66-104)

The standard method to create bulk data from outside of Dataverse is to use ExecuteMultipleRequest. ExecuteMultipleRequest is a message that lets the developer execute all messages that inherit OrganizationRequest. But the common messages are CreateRequest, UpdateRequest, or DeleteRequest.

For this section, we have two functions with different settings: split data per 100 and 200. For the code itself, we just need to create ExecuteMultipleRequest with the ExecuteMultipleSettings that we want. Then I created extensions to split array data to the size that we want (133-142).

Azure Service Bus + ServiceBusTrigger WebJob

We can make use of the pub-sub pattern using Azure Service Bus and ServiceBusTrigger. In the main code (line number 107-120), we will send the message that we want to the Azure Service Bus Queue named “crm-in”. Once the message exists, the WebJob automatically processes the data to Dataverse.

Diagram Architecture

For the web job, we need to create another exe and this is the main code:

using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Newtonsoft.Json;

namespace DataverseMessageProcessor;

internal class Program
{
    static async Task Main()
    {
        var builder = new HostBuilder();
        builder
            .ConfigureAppConfiguration((builderContext, cb) =>
            {
                var env = builderContext.HostingEnvironment;
                cb.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true, reloadOnChange: true);
            })
            .ConfigureWebJobs(b =>
            {
                b.AddAzureStorageCoreServices();
                b.AddServiceBus();
            }).ConfigureServices((context, services) =>
            {
                var dataverseConnectionString = context.Configuration
                    .GetValue<string>("DataverseConnectionString");

                services.AddSingleton<IOrganizationServiceAsync2>(_ => 
                    new ServiceClient(dataverseConnectionString));
            });

        var host = builder.Build();
        using (host)
        {
            await host.RunAsync();
        }
    }
}

public class Functions
{
    private readonly IOrganizationServiceAsync2 _crmService;

    public Functions(IOrganizationServiceAsync2 crmService)
    {
        _crmService = crmService;
    }

    public async Task ProcessContact([ServiceBusTrigger("crm-in",
                Connection = "ServiceBusConnectionString")] string message)
    {
        try
        {
            var contact = JsonConvert.DeserializeObject<Contact>(message);
            if (contact == null) return;

            var entity = new Entity("contact");
            entity["firstname"] = contact.FirstName;
            entity["lastname"] = contact.LastName;

            var id = await _crmService.CreateAsync(entity);
        }
        catch (Exception e)
        {
            Console.WriteLine($"ERROR: {e}");
            throw;
        }
    }
}

public class Contact
{
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
}

In the above code, we just need to define ServiceBusTrigger to the crm-in queue. Once the message is processed by the web job, we can create the contact and push it to the Dataverse. For the setting itself, I’m using a local file (appsettings.json) that contains ServiceBusConnectionString and DataverseConnectionString.

One thing to take note, we need to use AddSingleton when creating the DataverseServiceClient to avoid creating too many connections.

To create the Service Bus, you need to go to portal.azure.com:

Create Service Bus

Once created, you need to go to the Service Bus > Queues blade > create queue name “crm-in”:

Create queue

For the connection string, you can go to Shared access policies blade > select the default policy (RootManageSharedAccessKey) and get the connection string + paste it to your appsettings.json:

Get connection string

Deploy The Exe(s)

The last step we need to do is to deploy all those exes. From the Visual Studio > select the project > right click > publish > create the profile to deploy it to azure (the first time, you need to create App Service):

Create App Service

Once the App Service is created, you can select the App Service to host your application:

Select the App Service

For the DataverseClient, you can use WebJobType as Triggered:

DataverseClient as Triggered

Then for the DataverseMessageProcessor, I set it as ‘Continous’ as it will be based on ServiceBusTrigger:

DataverseMessageProcessor set as Continuous

Results

Here is the screenshot of the result (I’m using SQL 4 CDS by Mark Carrington):

The result

As you can see the longest result is the baseline. For the Pub-Sub pattern can finished 10k data in almost 10.5 minutes. The interesting result in here are regarding with ExecuteMultipleRequest with 100 vs 200. The 200 resulting in longer time (5.3 minutes), while the fastest one is 100 record with only 43 seconds!

Happy CRM-ing!

Advertisement

One thought on “Dataverse: Comparing Create vs ExecuteMultipleRequest vs Azure Service Bus – ServiceBusTrigger

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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