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:
There is nothing more we can do to optimise the code.
The question is:
- Can MARS be enabled on Connection Strings utilised with Azure Function Apps safely, and if not, why not?
- 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.