You’re offline. This is a read only version of the page.
54
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:
Share
ReportQueryExpression 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.
Hello sir i am also facing the same issue i have less then 10k records in 5 of my tables in dataverse. Its around 5-8 secs or even more sometimes more or less in order to establish connection for the first operation after that subsequest operations are faster i.e i get response in less then 2-3 sec. Can u please help me to reduce the connection time with dataverse with my dotnet webapi app as I am using dataverse for first time and mostly worked with sql and mongodb which are way faster in terms of speed. Below I have share the code for my dataverse operations which i have used acrossed my webapi app.
using Microsoft.Extensions.Configuration;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Rest;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Security;
using System.Threading.Tasks;
namespace Tm.Services.Comman
{
public static class ConfigurationHelper
{
public static IConfiguration config;
public static void Initialize(IConfiguration Configuration)
{
config = Configuration;
}
}
public class DataverseOperations
{
private static ServiceClient serviceClient;
private static readonly Uri uri;
private static readonly string clientId;
private static readonly string sec_pass;
private static readonly bool useUniqueInstance;
static DataverseOperations()
{
serviceClient = null;
uri = new Uri(ConfigurationHelper.config.GetSection("CdsClient:Uri").Value);
clientId = ConfigurationHelper.config.GetSection("CdsClient:ClientId").Value;
sec_pass = ConfigurationHelper.config.GetSection("CdsClient:ClientSecret").Value;
useUniqueInstance = Convert.ToBoolean(ConfigurationHelper.config.GetSection("CdsClient:UseUniqueInstance").Value);
}
public static void InitializeServiceClient()
{
if (serviceClient == null)
{
SecureString clientSecret = new SecureString();
Array.ForEach(sec_pass.ToArray(), clientSecret.AppendChar);
clientSecret.MakeReadOnly();
string connectionString = $@"AuthType=ClientSecret;SkipDiscovery=true;url={uri};Secret={sec_pass};ClientId={clientId};RequireNewInstance={useUniqueInstance}";
serviceClient = new ServiceClient(connectionString);
}
}
public async static Task<string> UpsertRequest(Entity entityModel, bool isAdd)
{
InitializeServiceClient();
if (serviceClient.IsReady)
{
if (isAdd)
{
var id = await serviceClient.CreateAsync(entityModel);
return id.ToString();
}
else
{
serviceClient.Update(entityModel);
}
}
return string.Empty;
}
public async static Task<EntityCollection> RetrieveMultipleAsync(QueryExpression query)
{
InitializeServiceClient();
EntityCollection entityCollection = new EntityCollection();
if (serviceClient.IsReady)
{
entityCollection = await serviceClient.RetrieveMultipleAsync(query);
}
return entityCollection;
}
public async static Task<Entity> RetrieveAsync(string entityName, Guid id, ColumnSet columnSet)
{
InitializeServiceClient();
Entity entity = new Entity();
if (serviceClient.IsReady)
{
entity = await serviceClient.RetrieveAsync(entityName, id, columnSet);
}
return entity;
}
public static Guid[] CreateRecordsInParallel(List<Entity> entityList)
{
InitializeServiceClient();
ConcurrentBag<Guid> ids = new ConcurrentBag<Guid>();
if (serviceClient.IsReady)
{
Parallel.ForEach(entityList, new ParallelOptions { MaxDegreeOfParallelism = serviceClient.RecommendedDegreesOfParallelism }, entity =>
{
var id = serviceClient.Create(entity);
ids.Add(id);
});
}
return ids.ToArray();
}
public static T GetRecordsInParallel<T>(List<QueryExpression> queryList)
{
Stack results = new Stack();
InitializeServiceClient();
if (serviceClient.IsReady)
{
Parallel.ForEach(queryList,
new ParallelOptions { MaxDegreeOfParallelism = serviceClient.RecommendedDegreesOfParallelism },
query =>
{
var result = serviceClient.RetrieveMultiple(query);
lock (results) // Ensure thread safety when pushing to the stack
{
results.Push(result);
}
});
}
return (T)(object)results;
}
}
}
your inputs and suggestion are really appreciated. Thank you
Glad it's solved.
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.
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 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?
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.
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/
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.
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.
#1
mmbr1606
22
Super User 2025 Season 1
#2
stampcoin
17
#3
ankit_singhal
11
Super User 2025 Season 1
All responses (
Answers (
