HttpClient formulas performance

[schebotar]

Hello
I have custom REST API client Excel function for some specific database search.
I faced with some performance issues.
For example this is simple Google Sheets App script demo of doing this database request

msedge_D5Fbdwqkdu.mp4

App script code

function RHSOLUTIONS(query) {
  var url = < API PATH >;
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  var result = data[0].id + " " + data[0].name;
  Logger.log(result);
  return result;
}

This is demonstration of Excel function for same thing.

explorer_TL2D7lEUub.mp4

It takes 1 minute longer for just ~80 cells

How I implement this

public sealed class RhSolutionsAddIn : IExcelAddIn
{
    public static ServiceProvider ServiceProvider { get; private set; }

    public void AutoOpen()
    {
        IServiceCollection Services = new ServiceCollection();
        // Add HttpClient, MemoryCache and DB client
        Services.AddHttpClient()
            .AddMemoryCache()
            .AddSingleton<IDatabaseClient, DatabaseClient>();
        ServiceProvider = Services.BuildServiceProvider();
        ServicePointManager.SecurityProtocol =
            SecurityProtocolType.Tls12;
    }

    public void AutoClose()
    {
    }
}

using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;

namespace RhSolutions.Services;

public class DatabaseClient : IDatabaseClient
{
    private readonly HttpClient _httpClient;
    private readonly IMemoryCache _memoryCache;
    public HttpStatusCode StatusCode { get; private set; }

    public DatabaseClient(HttpClient httpClient, IMemoryCache memoryCache)
    {
        _httpClient = httpClient;
        _memoryCache = memoryCache;
    }

    public async Task<IEnumerable<Product>> GetProducts(string query)
    {
            UriBuilder builder = new(<API placeholder>)
            {
                Query = $"query={query}"
            };
            string request = builder.Uri.AbsoluteUri;
            
            if (!_memoryCache.TryGetValue(line, out IEnumerable<Product> products))
            {
                var response = await _httpClient.GetAsync(request);
                try
                {
                    response.EnsureSuccessStatusCode();
                    string json = await response.Content.ReadAsStringAsync();
                    products = JsonConvert.DeserializeObject<IEnumerable<Product>>(json) ?? Enumerable.Empty<Product>();
                }
                catch
                {
                    StatusCode = response.StatusCode;
                    return Enumerable.Empty<Product>();
                }
                var cacheEntryOptions = new MemoryCacheEntryOptions()
                    .SetSlidingExpiration(TimeSpan.FromHours(1));
                _memoryCache.Set(query, products, cacheEntryOptions);
                if (products.Any())
                {
                    _memoryCache.Set(products.First(), products, cacheEntryOptions);
                }
                return products;
            }
            else
            {
                return products;
            }
        }     
}

    [ExcelFunction]
    public static object RHSOLUTIONS(string query)
    {
        if (ExcelAsyncUtil.Run(nameof(RHSOLUTIONS), query, delegate
        {
            return databaseClient.GetProducts(line)
                .GetAwaiter()
                .GetResult();
        }) is not IEnumerable<Product> requestResult)
        {
            return "Загрузка...";
        }

        else
        {
            if (!requestResult.Any())
            {
                return ExcelError.ExcelErrorNA;
            }
            else
            {
                var firstProduct = requestResult.First();
                return $"{firstProduct.ProductSku} {firstProduct.Name}";
            }
        }
    }

Any tips how to improve this?

[govert]

There are a few things you can do to improve the performance in the Excel / Excel-DNA side. But I suspect the biggest problem is in the number of concurrent calls made through the HttpClient.

So, the first thing to try is to make sure you are setting the maxConcurrentConnections for the HttpClient. This depends on whether you are targeting .NET Framework 4.x or .NET 6. You should also Google about this a bit.

I presume you are targeting .NET Framework 4.x - I think the default value is 2 and you would set it like this, maybe in your AutoOpen?

        ServicePointManager.SecurityProtocol =
            SecurityProtocolType.Tls12;
        // Set the connection limit for all new ServicePoints
        ServicePointManager.DefaultConnectionLimit = 50;

There's also a way to set it per url:

private void SetMaxConcurrency(string url, int maxConcurrentRequests)
{
	ServicePointManager.FindServicePoint(new Uri(url)).ConnectionLimit = maxConcurrentRequests;
}

Under .NET 6 I think the code might be different:

var socketsHttpHandler = new SocketsHttpHandler()
{
	MaxConnectionsPerServer = 16
};
var httpClient = new HttpClient(socketsHttpHandler);

In any case, that would be the first focus - determine what the connection limit is and try to log or figure out how many concurrent connections are being made. This might also be limited by the server, but your Google example indicates this is not the bottleneck.

Some more ideas for optimization (each of these are independent, and might make a small difference:

• Move the code around so that the cache is checked before the call to ExcelAsyncUtil.Run.
• Replace ExcelAsyncUtil.Run with AsyncTaskUtil.RunTask using the instructions from here: https://excel-dna.net/docs/guides-basic/asynchronous-functions . Instead of calling your task-based function inside another Task.Run, which means a ThreadPool worker thread might be tied up during outstanding calls, this mechanism will use the await .... HttpClient.GetAsync calls properly without using up ThreadPool threads. The default ThreadPool size and growth rate are not a good match for Excel calls.

This should get you to a function with similar performance to the Google sheet function.

Now there are some more improvement ideas:

• Can you change the back-end API to make concurrent calls? Then make a function that will take a whole list of query strings, and return a list of results.
  • Under Dynamic Arrays version of Excel (Excel 2021+ or recent versions of Office 365) this will work as is, and take only one back-end call to return the whole result set.
  • Under old non-DA versions of Excel, your async function cannot return an array safely. But you can still make an async batch function that is called individually from every cell, but batches up the back-end call. There is some sample code for this here: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

Please write back to say what you try and how it works out :-)

[schebotar]

@govert, thank you!
Just tried DefaultConnectionLimit setting with no effect at all.
But AsyncTaskUtil.RunTask works like bliss!
I got the same experience like in Google Sheets.