3

We have a v1 Azure Function App. It has to be v1 because our client insists on .NET Framework as their organisation has familiarity with Framework and has not migrated to .NET Core.

Our architect has stipulated that MARS (Multiple Active Record Sets) should be set to false on the connectionString as Azure Function Apps are multi-threaded and MARS is not thread safe, as per: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/enable-multiple-active-result-sets?view=sql-server-ver15 -.

Initially it was implemented with DbDataReader and we have also implemented it with DbDataAdapter. The SP in question returns two result sets, and executes sub 1 second. The Function App and the Azure SQL Server instance are in the same Azure Region.

We have done deeper profile tracing, and the request takes between 3-7 seconds (and longer at times) to fill the DataSet (and before you say go back to reader, the same performance occurs). This is for a resultset of ZERO rows. Similarly, the SP executes in sub 1 second. Network performance from another part of the world (SQL in UK, development in ZA) and it still traverses in sub 1 second from Europe to Africa (where we are executing).

On deeper inspection, the fill is taking a long time:

Tracing

There is nothing more we can do to optimise the code.

The question is:

  1. Can MARS be enabled on Connection Strings utilised with Azure Function Apps safely, and if not, why not?
  2. How can we enable DataFills to be more performant within a .NET Framework Azure Function app?

The function app in question has other methods, etc. If we can't use MARS or have another performance tweak/tune then our current thinking is we'll have to split out this particular method call into some sort of v2 .NET Core function app or some other means for performant data population.

The client is very cost conscious (which client isn't?) and they are quite cautious around additional resources in Azure - think a deep bureaucracy - so tuning or using MARS (or another ConnString tweak) is preferred.

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
Andrew La Grange
  • 312
  • 2
  • 12
  • "There is nothing more we can do to optimise the code." This is never true. The fact that said code is using ancient Framework 1.1 ADO.NET `DataSet`s and `DbDataAdapter`s means it's especially untrue. Seems to me that your client's insistence on Framework is actually insistence on never updating code, and that sort of "strategy" ultimately ends up with the exact situation you're currently in. Tell the client that this is performance issue is the end result of their "cost-consciousness" and that there's nothing you can do to improve this unless you move to Core. – Ian Kemp Feb 26 '21 at 11:28
  • @IanKemp - I appreciate that point. That being said, the essence of the question isn't a move to Core, I accept that is one solution. I'm trying to confirm a particular point around multi-threading in Azure Function Apps in v1 - is that true/blocking for MARS is not possible. "Newer is always better" is certainly a valid jumping off point - however that, in this particular case, is not going to fly - we are a 50 person dev shop. They are a 40k employee corporate. We are concluding our work with them because of the challenges that asymmetry causes. But this remains a technical question. – Andrew La Grange Feb 26 '21 at 13:09

0 Answers0