Performance Recommendations?

[ainsleybernard]

Hello,

Would you have any performance recommendations in regards to managing connections?

I have a Azure Durable Function, which opens several concurrent connections to the Dataverse using SQL4CDS to read data only.

I noticed After about 5 connections, the opening of subsequent connections are slower from milliseconds to seconds.

Would you know if there is max amount of connections allowed to be open.

Also I am curios would you know how long connection would stay open for, if I choose to pass it around?

Thanks

[MarkMpn]

There's no maximum number of connections allowed by SQL 4 CDS. There may be some limits from the underlying infrastructure.

I'd be interested to understand where the delay is coming from if you're able to investigate any further. For example, if you're opening a connection using a connection string like:

var con = new Sql4CdsConnection("AuthType=ClientSecret;url=https://contosotest.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}");

you could try breaking this down into establishing the underlying ServiceClient, then passing that through to the Sql4CdsConnection and measuring the time for both steps:

var svc = new ServiceClient("AuthType=ClientSecret;url=https://contosotest.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}");
var con = new Sql4CdsConnection(svc);

[ainsleybernard]

Sorry I was away, but thanks for this! I refactored the code as it was making way too many calls; at first glance, I thought it was my only solution. However, I still encounter underlying issues that I initially thought were related to the numerous calls. Now, I think it might be due to the SQL4CDS engine, but I'm not really sure. With Application Insights, I intermittently get these errors.

Note: 1 everything works as expected; the queries execute, and I get the output that I need.

Note 2: I am not getting any exceptions either. so these are silent or not within my domain at least.

WCF Service call = false

The first error is a 401, and the second is a 500 error. I do not get any extra details on the 500 error.

I am not sure about the logic behind the SQL4CDS engine, whether it attempts to connect and then reconnects for the 401 error. As for the 500 error, I am not sure if it is failing because the query is maybe too complex but again I am getting the results I need.

Do you think these errors originate from SQL4CDS?

[ainsleybernard]

Hey, I found the reason for the 401 error., now just need to figure out the 500 one :)

microsoft/PowerPlatform-DataverseServiceClient#283

[MarkMpn]

I guess you're hitting some other server-side error which the service client is automatically retrying, like a service protection limit or similar.

[jarvan-jiang]

@MarkMpn Hi Mark, I have same same question, can the connection stay open? I see the code, it's not implementing open and close method, does it mean it's not actually 'open' so does not need close?

I have this question because I found open the connection will bring some query initially take some seconds, so if I use it as regular sql connection, it bring latency because of frequently close/open connection.

So I wondering if we could use a singleton connection?