Benchmark: TDS Endpoint vs QueryExpression vs FetchExpression

I read Mark Carrington's blog post about his latest findings on TDS Endpoint revisited, which can be found here. One of the key points that is interesting is his finding about the internal Dataverse message ExecutePowerBISql, which intersects with the function I created last time for similar purposes (TDS Endpointvia Plugin). Hence, I couldn't resist comparing my Custom API vs the internal message - ExecutePowerBISql. Also, I tested to compare FetchExpressionand QueryExpression just to know how these messages compare with the others!

TDS Endpoint Plugin

Here is my TDS Endpoint logic:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;

namespace BlogApi
{
    public class TdsEndpointApi : PluginBase
    {
        public readonly string SelectParam = "Select";
        public readonly string OffSetParam = "Offset";
        public readonly string PageParam = "Page";
        public readonly string RowParam = "Row";
        public readonly string AccessTokenParam = "AccessToken";
        public readonly string TdsEndpointParam = "TDSEndpoint";

        public readonly string TotalRowsParam = "TotalRows";
        public readonly string ResultParam = "Result";

        public TdsEndpointApi() : base(typeof(TdsEndpointApi))
        {
        }

        protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
        {
            var tdsEndPoint = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(TdsEndpointParam) ?
                localPluginContext.PluginExecutionContext.InputParameters[TdsEndpointParam]?.ToString() : "";

            var config = string.IsNullOrEmpty(tdsEndPoint) ? GetEnvironmentVariableData(localPluginContext.InitiatingUserService, "TDS Endpoint URL") : tdsEndPoint;
            if (string.IsNullOrEmpty(config)) throw new InvalidPluginExecutionException("TDS Endpoint URL is empty..");
            var offsetSql = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(OffSetParam) ?
                localPluginContext.PluginExecutionContext.InputParameters[OffSetParam]?.ToString() : "";
            var selectSql = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(SelectParam) ?
                localPluginContext.PluginExecutionContext.InputParameters[SelectParam]?.ToString() : "";
            var page = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(PageParam) ?
                int.Parse(localPluginContext.PluginExecutionContext.InputParameters[PageParam]?.ToString() ?? "0") : 0;
            var rows = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(RowParam) ?
                int.Parse(localPluginContext.PluginExecutionContext.InputParameters[RowParam]?.ToString() ?? "0") : 0;
            var accessToken = localPluginContext.PluginExecutionContext.InputParameters.ContainsKey(AccessTokenParam) ?
                localPluginContext.PluginExecutionContext.InputParameters[AccessTokenParam]?.ToString() : "";

            int totalRows = 0;
            var result = new EntityCollection();

            using (var conn = new SqlConnection(config))
            {
                conn.AccessToken = string.IsNullOrEmpty(accessToken) ? GetAccessToken(localPluginContext) : accessToken;
                conn.Open();

                var cmd = conn.CreateCommand();
                if (!string.IsNullOrEmpty(offsetSql))
                {
                    cmd.CommandText = offsetSql;
                    totalRows = (int)cmd.ExecuteScalar();
                }

                if (!string.IsNullOrEmpty(selectSql))
                {
                    var offsetString = string.IsNullOrEmpty(offsetSql) ? "" : GetOffsetString(totalRows, page, rows);
                    cmd.CommandText = $"{selectSql} {offsetString}";

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

                    var entities = ToEntityList(dataTable).ToArray();
                    result.Entities.AddRange(entities);
                }
            }

            localPluginContext.PluginExecutionContext.OutputParameters[TotalRowsParam] = totalRows;
            localPluginContext.PluginExecutionContext.OutputParameters[ResultParam] = result;
        }

        public static IEnumerable<Entity> ToEntityList(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                var item = new Entity();
                foreach (DataColumn column in table.Columns)
                {
                    var columnValue = row[column];
                    if (columnValue == null) continue;
                    if (columnValue == DBNull.Value) continue;

                    item[column.ColumnName] = columnValue;
                }

                yield return item;
            }
        }

        private string GetAccessToken(ILocalPluginContext context)
        {
            var configText = GetEnvironmentVariableData(context.InitiatingUserService, "TDS Endpoint Token");
            if (string.IsNullOrEmpty(configText)) throw new InvalidPluginExecutionException("TDS Endpoint Token is not found!");
            var config = JsonConvert.DeserializeObject<TdsEndpointConfigModel>(configText);
            var client = new HttpClient();
            var request = new HttpRequestMessage(HttpMethod.Post, $"https://login.microsoftonline.com/{config.TenantId}/oauth2/token");
            var collection = new Dictionary<string, string>
            {
                ["grant_type"] = "client_credentials",
                ["client_id"] = config.ClientId,
                ["client_secret"] = config.ClientSecret,
                ["resource"] = config.Resource
            };
            var content = new FormUrlEncodedContent(collection);
            request.Content = content;
            var response = client.SendAsync(request).Result;
            response.EnsureSuccessStatusCode();
            var result = response.Content.ReadAsStringAsync().Result;

            return JsonConvert.DeserializeObject<OAuthTokenResponse>(result).AccessToken;
        }

        private string GetEnvironmentVariableData(IOrganizationService service, string environmentVariableName)
        {
            var fetchXml = string.Format(@"<fetch xmlns:generator='MarkMpn.SQL4CDS'>
              <entity name='environmentvariabledefinition'>
                <attribute name='defaultvalue' />
                <attribute name='environmentvariabledefinitionid' />
                <link-entity name='environmentvariablevalue' to='environmentvariabledefinitionid' from='environmentvariabledefinitionid' alias='environmentvariablevalue' link-type='outer'>
                  <attribute name='value' />
                  <attribute name='environmentvariablevalueid' />
                  <order attribute='environmentvariablevalueid' />
                </link-entity>
                <filter>
                  <condition attribute='displayname' operator='eq' value='{0}' />
                </filter>
                <order attribute='environmentvariabledefinitionid' />
              </entity>
            </fetch>", environmentVariableName);
            var result = service.RetrieveMultiple(new FetchExpression { Query = fetchXml });

            var row = result.Entities.Count > 0 ? result.Entities[0] : null;
            if (row == null) throw new InvalidPluginExecutionException($"Environment variable '{environmentVariableName}' is not found.");

            return row.GetAttributeValue<AliasedValue>("environmentvariablevalue.value")?.Value?.ToString() ?? row.GetAttributeValue<string>("defaultvalue");
        }

        public class TdsEndpointConfigModel
        {
            public string ClientId { get; set; }
            public string ClientSecret { get; set; }
            public string Resource { get; set; }
            public string TenantId { get; set; }
        }

        public class OAuthTokenResponse
        {
            [JsonProperty("access_token")]
            public string AccessToken { get; set; }
        }

        private string GetOffsetString(int totalRecord, int page, int row)
        {
            var currentRows = (page - 1) * row;
            if (currentRows >= totalRecord) return "";
            return $" OFFSET {currentRows} ROWS FETCH NEXT {row} ROWS ONLY";
        }

    }
}

The above Custom API code has pagination logic if we pass the correct argument. But, for today's benchmark, we will just use the TDS capability only. If you check further, the code above, basically, we need to get the access token if not passed. Then, we also need to build the SQLConnection and execute the query. As the return parameter, we will convert the data that we retrieved to the EntityCollection and pass the TotalRows if pagination is needed.

Next, I register the Custom API using the settings below:

Custom API settings and the parameters

Benchmark

Here is the full list of the benchmark code:

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Configs;
using DataverseBenchmarkProject.Connections;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Data;

namespace DataverseBenchmarkProject;

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

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

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

    public readonly string SqlQuery = "SELECT TOP 5 fullname FullName FROM contact";

    [Benchmark]
    public async Task ExecutePowerBISql()
    {

        var req = new OrganizationRequest("ExecutePowerBISql")
        {
            Parameters = new ParameterCollection
            {
                ["QueryText"] = SqlQuery
            }
        };

        var result = await _xrmConnection1.GetServiceClient().ExecuteAsync(req);
        var records = result["Records"] as DataSet;
        Console.WriteLine("ExecutePowerBISql " + records!.Tables[0].Rows.Count);
    }

    [Benchmark]
    public async Task TdsEndPointCustom()
    {
        var req = new OrganizationRequest("new_TdsEndpointApi")
        {
            Parameters = new ParameterCollection
            {
                ["Select"] = SqlQuery
            }
        };
        var result = await _xrmConnection2.GetServiceClient().ExecuteAsync(req);
        var records = result["Result"] as EntityCollection;
        Console.WriteLine("TdsEndPointCustom " + records!.Entities.Count);
    }

    [Benchmark]
    public async Task TdsEndPointCustomPassAccessToken()
    {
        var req = new OrganizationRequest("new_TdsEndpointApi")
        {
            Parameters = new ParameterCollection
            {
                ["Select"] = SqlQuery,
                ["AccessToken"] = _xrmConnection3.GetServiceClient().CurrentAccessToken
            }
        };
        var result = await _xrmConnection3.GetServiceClient().ExecuteAsync(req);
        var records = result["Result"] as EntityCollection;
        Console.WriteLine("TdsEndPointCustomPassAccessToken " + records!.Entities.Count);
    }

    [Benchmark]
    public async Task QueryExpression()
    {
        var query = new QueryExpression("contact")
        {
            ColumnSet = new ColumnSet("fullname"),
            TopCount = 5
        };
        var result = await _xrmConnection1.GetServiceClient().RetrieveMultipleAsync(query);
        Console.WriteLine("QueryExpression " + result.Entities.Count);
    }

    [Benchmark]
    public async Task FetchExpression()
    {
        var fetchXml = @"<fetch xmlns:generator='MarkMpn.SQL4CDS' top='5'>
          <entity name='contact'>
            <attribute name='fullname' />
          </entity>
        </fetch>";
        var result = await _xrmConnection4.GetServiceClient().RetrieveMultipleAsync(new FetchExpression { Query = fetchXml });
        Console.WriteLine("FetchExpression " + result.Entities.Count);
    }
}

Last! Here is the result:

BenchmarkDotNet v0.15.6, Windows 11 (10.0.26100.7171/24H2/2024Update/HudsonValley)
AMD Ryzen 5 5600G with Radeon Graphics 3.90GHz, 1 CPU, 12 logical and 6 physical cores
.NET SDK 9.0.302
  [Host]     : .NET 8.0.22 (8.0.22, 8.0.2225.52707), X64 RyuJIT x86-64-v3
  Job-MFNUOV : .NET 8.0.22 (8.0.22, 8.0.2225.52707), X64 RyuJIT x86-64-v3

LaunchCount=2  WarmupCount=1  

The performance for ExecutePowerBISql looks promising! Although ExecutePowerBISqlis unsupported at this point in time. I hope Microsoft will release this message so developers can start using the message (but you can't get the elastic table using this method).

Happy CRM-ing 🚀!