Power Automate Prime Sieve

As noticed by Daniel Laskewitz Microsoft have quietly added the ability to add custom C# code to a power automate connector. Let’s see if it’s possible to speed up our prime number calculation using this new functionality and if so what kind of performance improvement is possible.

Scenario

In the previous blog we attempted to count the prime numbers up to a certain number using pure Power Fx. We found that calculating all the primes up to 1,000,000 is just about possible, but it takes nearly two minutes.

Microsoft are rolling out the ability to add code to custom connectors. I could not find the functionality in my European (crm4) tenant, but creating a new trial in Canada (Crm3) presented the new functionality.

How to add code

A new ‘Code’ tab is available in the custom connector editor.

1. After switching to the code tab
2. Enable custom code
3. Select the action to which the code will apply.
4. Paste the code into the text box
5. Update the connector

Primes Code

The key requirement is to implement the ExecuteAsync method of a class named Script that implements ScriptBase. The documention provides a number of examples to alter and transform a request or response payload, and this seems to be the intended purpose of this new functionality. However, let’s see if it is possible to count prime numbers.

public class Script : ScriptBase
{
    [...snip ...]

    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        var request = JsonConvert.DeserializeObject<CountPrimesRequest>(await this.Context.Request.Content.ReadAsStringAsync());
        var stopWatch = Stopwatch.StartNew();

        var sieve = new PrimeSieve(request.UpTo);
        sieve.RunSieve();
        var numPrimes = sieve.CountPrimes();

        stopWatch.Stop();

        var response = new CountPrimesResponse
        {
            NumPrimes = numPrimes,
            ScriptDuration = Convert.ToInt32(stopWatch.ElapsedMilliseconds)
        };

        return new HttpResponseMessage(HttpStatusCode.OK)
        {
            Content = CreateJsonContent(JsonConvert.SerializeObject(response))
        };
    }
}

In the above we are:

1. Deserialising a request
2. Starting a timer
3. Building and filtering the prime sieve and then counting the primes
4. Stopping the timer
5. Serialising the response and returning it via an HttpResponseMessage

The request and response classes are very simple:

    public class CountPrimesRequest
    {
        public Int32 UpTo { get; set; }
    }

    public class CountPrimesResponse
    {
        public Int32 NumPrimes { get; set; }
        public Int32 ScriptDuration { get; set; }
    }

Finally we have the prime sieve calculation itself, which is completely borrowed from github

    public class PrimeSieve
    {
        private readonly int SieveSize = 0;
        private readonly BitArray bitArray; //making it readonly so we tell the compiler that the variable reference cant change. around 5% increase in performance

        public PrimeSieve(int size)
        {
            SieveSize = size;
            bitArray = new BitArray(((this.SieveSize + 1) / 2), true);
        }

        public int CountPrimes()
        {
            int count = 0;
            for (int i = 0; i < this.bitArray.Count; i++)
                if (bitArray[i])
                    count++;
            return count;
        }

        private bool GetBit(int index)
        {
            System.Diagnostics.Debug.Assert(index % 2 == 1);

            return bitArray[index >> 1];
        }

        private void ClearBit(int index)
        {
            System.Diagnostics.Debug.Assert(index % 2 == 1);

            bitArray[index / 2] = false;
        }

        // primeSieve
        //
        // Calculate the primes up to the specified limit
        public void RunSieve()
        {
            int factor = 3;
            int q = (int)Math.Sqrt(SieveSize);

            while (factor <= q)
            {
                for (int num = factor; num <= q; num += 2)
                {
                    if (GetBit(num))
                    {
                        factor = num;
                        break;
                    }
                }

                int increment = factor * 2;

                for (int num = factor * factor; num <= SieveSize; num += increment)
                    ClearBit(num);

                factor += 2;
            }
        }
    }

Limitations

Whilst Microsoft provides good documentation and there’s a nice blog by Matt Beard, I still encountered a couple of issues:

1. Namespaces are not required and if used cause the script to break.
2. Using classes outside of the ScriptBase class did not appear to be supported. I had to use Nested Types.
3. There’s a limited number of supported namespaces that can be used.

Unit Testing

We can test the code relatively simply with a unit test project

using Xunit;

namespace PrimeCount.Tests
{
    public class ValidatePrimeCounts
    {
        // Historical data for validating our results - the number of primes
        // to be found under some limit, such as 168 primes under 1000
        [Theory]
        [InlineData(10, 4)]
        [InlineData(100, 25)]
        [InlineData(1000, 168)]
        [InlineData(10000, 1229)]
        [InlineData(100000, 9592)]
        [InlineData(1000000, 78498)]
        [InlineData(10000000, 664579)]
        [InlineData(100000000, 5761455)]
        public void Number_of_primes_upto_given_number_should_be_as_expected(
            int upTo, int expected)
        {
            var sieve = new Script.PrimeSieve(upTo);
            sieve.RunSieve();
            Assert.Equal(expected, sieve.CountPrimes());
        }
    }
}

We can also test the HTTP result is as expected in another unit test, see TestResponse.cs for detail.

Testing the connector

As normal, we can test the connector using the connection editor and we see the output is returned as expected.

I did receive several instances of the connector giving Operation failed (500) errors, but these disappeared after a few retries and the connector provided correct output Status (200) as above. I suspect the code snippets in the connector are applied to Azure API Management policies as described here. Perhaps there is a ‘cold start’ issue similar to serverless Azure Functions.

In practice, within a Power Automate Cloud Flow or Canvas App this might be hidden by the default built-in retry policies. Further investigation is required.

Updating our Canvas App

Using ‘Save As’ we can copy the canvas app from the last blog and modify it. It’s then a simple case of adding the custom connector into the app and updating the OnReset function in the canvas component.

Adding custom code into a canvas app is straight forward. An initial test shows the canvas app in combination with the custom connector is now able to count all the prime numbers up to one million in 393 milliseconds. A huge improvement.

Comparison - Performance and Licensing

After executing the same tests as the previous blog we can compare the performance of pure ‘Power Fx’ and our new custom connector method.

The key takeaways:

• Code within Power Automate is massively quicker than Power Fx when searching for primes above 5000.
• Only above 1,000,000 do we start to detect the script duration, the remainder of the time is consumed with network effects, e.g. Travelling to Canada, transport time through Azure API Management and then back again.
• Due to the network effects, pure Power Fx is faster for counting primes up to 5,000 (on my computer).
• Pure Power Fx does not require a network connection and hence will work offline. A custom connector always requires a network connection.
• Pure Power Fx performance is dependent on the client device. Performance will vary between a canvas app running on a PC or on (an older) mobile phone. The custom connector performance is primarly determined by network connectivity.
• A custom connector requires a premium license. Any user or flow using the canvas app will require a premium license.
• Power Fx has no usage or throughput limits, whereas Custom Connectors Limits apply, currently:
  • 50 custom connectors per user, and,
  • 500 requests per minute per connection.

Summary

• It’s great that Microsoft have provided this method to add snippets of code to Power Automate since it provides a really nice way to manipulate requests, or even generate completely programatic responses (as with this example).
• As with all things, I suspect it is best to use it in the spirit it’s intended, and the use case to calculate prime numbers doesn’t really fit in the transformation remit. From the Microsoft docs:

“Custom code transforms request and response payloads beyond the scope of existing policy templates. Transformations include sending external requests to fetch additional data. When code is used, it will take precedence over the codeless definition. This means the code will execute, and we will not send the request to the backend.”

• Using a compute based resource, such as Azure Functions, provides multiple benefits over the experiment shown above. Benefits such as: multiple functions/actions, deployment slots, improved ALM, and better integration with OpenAPI.

References

1. Microsoft Docs:
   • Write code in a custom connector
   • Use custom code support
2. Extending Power Automate Custom Connectors with C# by Matt Beard
3. The code and solutions included in this blog are on my github: filcole/PowerAutomatePrimes.