Benchmark: Comparing Pooling Setting in TDS Endpoints

We must construct the SQL Connection String when connecting to Dataverse TDS Endpointsvia .NET code. The structure of it by default is just "Server=.crm.dynamics.com;Database=;Persist Security Info=False;". When we talk about SQL connection strings, there are lots of properties that we can explore and today, I want to see if changing the "pooling" related properties can enhance the performance of the retrieval process (as TDS endpoints only allowed read-only data).

Benchmark

For the benchmark condition, I want to compare 4 scenarios of retrieval of TOP 5 Contacts in 10 threads:

1. FetchXml retrieval
2. Without Pooling
3. Default Pooling - I guess the default pooling setting is (Pool = true, Min Pool Size = 0, Max Pool Size = 100, Connection Timeout=15 seconds, and Connection Lifetime = 0 - unlimited).
4. WithPooling: set the Min Pool Size = 1 (retain the connection), and Connection Lifetime = 1800 Seconds.

Here is the code for the benchmarking:

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Configs;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Xrm.Sdk.Query;
using System.Data;
using System.Reflection;

namespace DataverseBenchmarkProject;

[MemoryDiagnoser]
[Config(typeof(Config))]
[Orderer(BenchmarkDotNet.Order.SummaryOrderPolicy.FastestToSlowest)]
[SimpleJob(launchCount: 10, warmupCount: 2)]
public class TdsEndpointBenchmark
{
    private XrmConnection _xrmConnection1;
    private XrmConnection _xrmConnection2;
    private XrmConnection _xrmConnection3;

    private class Config : ManualConfig
    {
        public Config()
        {
            SummaryStyle = DefaultConfig.Instance.SummaryStyle
                .WithTimeUnit(Perfolizer.Horology.TimeUnit.Millisecond);
        }
    }

    public TdsEndpointBenchmark()
    {
        var connectionStrings = Startup.GetApplicationHost().Services.GetService<ConnectionString>()!;
        _xrmConnection1 = new XrmConnection(connectionStrings);
        _xrmConnection2 = new XrmConnection(connectionStrings);
        _xrmConnection3 = new XrmConnection(connectionStrings);
    }

    [Benchmark]
    public async Task WithoutPooling()
    {
        var sqlQuery = "SELECT TOP 5 fullname FROM contact";

        var listTask = new List<Task>();
        for (int i = 0; i < 10; i++)
        {
            listTask.Add(Task.Run(() =>
            {
                var serviceClient = _xrmConnection1.GetServiceClient();

                using var sqlConnection = new SqlConnection("Server={orgname}.crm.dynamics.com;Database={orgname};Persist Security Info=False;Pooling=False;Application Name=BenchmarkWithoutPooling");
                sqlConnection.AccessToken = serviceClient.CurrentAccessToken;
                sqlConnection.Open();

                var command = sqlConnection.CreateCommand();
                command.CommandText = sqlQuery;

                var reader = command.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);

                var contacts = dataTable.ToModels<Contact>().ToArray();
                if (contacts.Length != 5)
                {
                    throw new Exception("Rows not equal to 5");
                }
            }));
        }

        await Task.WhenAll(listTask);
    }

    [Benchmark]
    public async Task DefaultConnection()
    {
        var sqlQuery = "SELECT TOP 5 fullname FROM contact";

        var listTask = new List<Task>();
        for (int i = 0; i < 10; i++)
        {
            listTask.Add(Task.Run(() =>
            {
                var serviceClient = _xrmConnection1.GetServiceClient();

                using var sqlConnection = new SqlConnection("Server={orgname}.crm.dynamics.com;Database={orgname};Persist Security Info=False;Application Name=BenchmarkDefaultConnection");
                sqlConnection.AccessToken = serviceClient.CurrentAccessToken;
                sqlConnection.Open();

                var command = sqlConnection.CreateCommand();
                command.CommandText = sqlQuery;

                var reader = command.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);

                var contacts = dataTable.ToModels<Contact>().ToArray();
                if (contacts.Length != 5)
                {
                    throw new Exception("Rows not equal to 5");
                }
            }));
        }

        await Task.WhenAll(listTask);
    }

    [Benchmark]
    public async Task WithPooling()
    {
        var sqlQuery = "SELECT TOP 5 fullname FROM contact";

        var listTask = new List<Task>();
        for (int i = 0; i < 10; i++)
        {
            listTask.Add(Task.Run(() =>
            {
                var serviceClient = _xrmConnection2.GetServiceClient();
                using var sqlConnection = new SqlConnection("Server={orgname}.crm.dynamics.com;Database={orgname};Persist Security Info=False;Pooling=True;Min Pool Size=1;Connection Lifetime=1800;Application Name=BenchmarkWithPooling");
                sqlConnection.AccessToken = serviceClient.CurrentAccessToken;
                sqlConnection.Open();

                var command = sqlConnection.CreateCommand();
                command.CommandText = sqlQuery;

                var reader = command.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);

                var contacts = dataTable.ToModels<Contact>().ToArray();
                if (contacts.Length != 5)
                {
                    throw new Exception("Rows not equal to 5");
                }
            }));
        }

        await Task.WhenAll(listTask);
    }

    [Benchmark]
    public async Task DataverseFetchXml()
    {
        var fetchXml = "<fetch top='5' no-lock='true'><entity name='contact'><attribute name='fullname'/></entity></fetch>";

        var listTask = new List<Task>();
        for (int i = 0; i < 10; i++)
        {
            var serviceClient = _xrmConnection3.GetServiceClient();

            listTask.Add(Task.Run(() =>
            {
                var result = serviceClient.RetrieveMultiple(new FetchExpression(fetchXml));

                if (result.Entities.Count != 5)
                {
                    throw new Exception("Rows not equal to 5");
                }
            }));
        }

        await Task.WhenAll(listTask);
    }
}

public class Contact
{
    public string Fullname { get; set; }
}

public static class DataTableExtensions
{
    public static IEnumerable<T> ToModels<T>(this DataTable table) where T : new()
    {
        foreach (DataRow row in table.Rows)
        {
            T item = new T();
            foreach (DataColumn column in table.Columns)
            {
                PropertyInfo? prop = typeof(T).GetProperty(column.ColumnName);
                if (prop != null && row[column] != DBNull.Value)
                {
                    var value = ChangeType(row[column], prop.PropertyType);
                    if (value == null) continue;

                    prop.SetValue(item, value);
                }
            }
            yield return item;
        }
    }

    public static object? ChangeType(object value, Type conversion)
    {
        if (conversion == typeof(string)) return value?.ToString();
        var t = conversion;

        if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
        {
            if (value == null)
            {
                return null;
            }

            t = Nullable.GetUnderlyingType(t);
        }

        return t != null ? Convert.ChangeType(value, t) : null;
    }
}

As you can see above, I also provided a simple extension to convert DataTable to POCO class to make it easier for reading purposes (as we compared using FetchXML - Entity as well).

Result

Here is the result:

BenchmarkDotNet v0.14.0, Windows 11 (10.0.26100.2454)
AMD Ryzen 5 5600G with Radeon Graphics, 1 CPU, 12 logical and 6 physical cores
.NET SDK 9.0.100
  [Host]     : .NET 8.0.11 (8.0.1124.51707), X64 RyuJIT AVX2
  Job-RAVYKB : .NET 8.0.11 (8.0.1124.51707), X64 RyuJIT AVX2

LaunchCount=10  WarmupCount=2  

I tried several configurations to set higher on the "Min Pool Size". But, based on my understanding reading this documentation, seems like SQL will only keep based on the connection string. Hence, 1 is the correct setting for this. The interesting part is the FetchXmlmuch slower in the multithreading scenario compared to the TDS Endpoint (the memory allocation is also higher).

So, the summary for this testing (and to be safe as the improvement is not significant), just stick to the default connection format and you can bump the "Max Pool Size" if necessary without the need to set any other things.

Hope you learn something and happy CRM-ing! 🚀