Performance problem using C# to retrieve records

[AnttiKurenniemi]

Hi all.

I have a project where I need to generate a rather complex export file; multiple joins to data using sub-queries and the likes. We're using an Azure Function with .NET / C#, and are having serious performance issues.

I'm using the IOrganizationService interface and connecting by using Managed Identity. I can run this also on my local dev machine by setting the account to use in Visual Studio settings. It works nicely, the connection takes a few seconds but works.

But when I query data using this pattern:

QueryExpression query = new("custome_table") { TopCount = 5 };
query.ColumnSet.AddColumns("column 1", "another column", "third", "fourth");
query.AddOrder("primary name column here", OrderType.Ascending);
EntityCollection results = Service.RetrieveMultiple(query);

It can take anywhere up to 20 minutes to complete! That's bonkers. The table in question has less than 1000 records, which should be milliseconds to fetch, not seconds and certainly not minutes.

Eventually it always completes and I get the correct data, but just developing and testing like this takes so long that I will be long since retired before I am anywhere near completion of this project. 

We have a Canvas app that uses the same data and it's snappy enough, loading a gallery is in the one second ballpark, so the Dataverse database itself is probably ok.

Any ideas? Something throttling somewhere? I don't even know where to start looking; I'm very used to SQL Server and other "normal" databases, but with Dataverse I don't know what to do.

[shashankbhide]

I didn't understand how you're using managed identity with Dataverse, because it's a preview feature and secondly it's going to be available to plugins only. So, are you sure that "data retrieval" from Dataverse is taking time and not something else.

Other things look good, I was thinking if you've custom plugins written for Retrieve requests but since you've a Canvas app working lightening fast, that also sounds like improbable. 

[AnttiKurenniemi]

Thanks for the reply. No, we don't have any custom plugins.

I'm logging all calls with timestamps. I'm doing this to connect, and get the Service Client:

IOrganizationService Service = new ServiceClient(new Uri(EnvironmentUrl), tokenProviderFunction: async (par) =>
{
var managedIdentity = new DefaultAzureCredential();
return (await managedIdentity.GetTokenAsync(
new Azure.Core.TokenRequestContext(new[] { $"{EnvironmentUrl}/.default" }))).Token;
});

It seems to work ok, connecting takes about 2 seconds or thereabouts. I'm thinking the connection is ok because it returns data, no errors.

It takes several minutes to return from RetrieveMultiple, or Retrieve. Also subsequent requests take similar long time, so it's not just some handshake for the first request. Even a simple WhoAmI takes minutes to execute. Everything else is fast. There also doesn't seem to be any difference in how long it takes, regardless if I ask for 5 rows or 1000.

I'm stumped. This should be such a simple thing, connect to a database and retrieve records.

[a33ik]

Hello,

I have never tried managed identity but I used AppUsers instead and it never took that long to perform simple operations. Are you willing to change how your app is getting authenticated with Dataverse?

https://powermaverick.dev/2020/08/10/create-application-user-in-dataflex-pro-cds/

[shashankbhide]

I suggest you change your approach regarding ServiceClient , there are other overloads which you could use using OAuth2 client ID and client secret. I've honestly never seen the way you're connecting to connect to dataverse, managed identity needs to be established between two azure components in the azure first and then you use the DefaultCredentials class to start generate tokens , but dataverse isn't an azure component, so I'm not sure where the trust has been established.

[shashankbhide]

• @a33ik I'm also wondering how is that code connecting with Dataverse 😯😯. It'll be interesting to learn that way.

[shashankbhide]

@AnttiKurenniemi since you're using C# , it should be easy for you to detect what's causing the delay using some Stopwatch class. Can you measure how long the token generation is taking?

[AnttiKurenniemi]

I did measure, it takes 1-2 seconds. This approach that I am using has the benefit that I can use it from my local Visual Studio environment when debugging (in Visual Studio settings, you can define what account to use for Azure services), and when I deploy to an Azure Function it will use the Managed Identity of that function.

But I will try other approaches to at least compare and benchmark this method out.

[AnttiKurenniemi]

Ok, turns out my way of instantiating the ServiceClient was wrong - it works when I use a "Confidential Client" method, a connection string with a ClientSecret (app registration)!

I was thrown off because I eventually got data, just with a long delay -> this made me think the connection was ok and something else was wrong.

But it now works just fine with this type of approach:

private ServiceClient GetServiceClient(string EnvironmentUrl)
{
    string connectionString = $@"
        AuthType = ClientSecret;
        Url = {EnvironmentUrl};
        ClientId = xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;
        Secret = yyyyy~yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy";
    return new ServiceClient(connectionString);
}

I would still prefer Managed Identity, to be able to do away with the secrets management, but at least I can now move on with this solution.

[shashankbhide]

Glad it's solved.