Power Apps — Troubleshooting Performance Issues — Part 3
In this blog, we will explore performance troubleshooting related to SQL, FetchXml, OData, and SDK. We will discuss issues related to each of these technologies, and provide a few tips and recommendations for optimizing performance.
SQL Related issues
Microsoft recommends that you don’t execute operations that require concurrent database transactions. Also, you don't execute operations that require intensive database transactions during normal business hours, when users are most likely to access the system.
Example operations that require intensive database transactions include:
- Enable one or more language packs
- Solution import, upgrade, delete, or export
- Install or upgrade apps from Microsoft AppSource or the Dynamics 365 admin center
- Publish customizations
- Large bulk record operations, such as business unit changes. For example, when a business unit has a very large number of records associated with it
You also need to check if the issue is happening for all entities across the organization or selected entities.
Your organization might be running into a transient connectivity issue with SQL Server. When such an event happens, your application won’t load/work as usual. There could be several reasons why this could occur, but the root cause might be different case by case.
Microsoft proactively monitors SQL performance-related activities and recovers connectivity issues.
Please note that the Power Platform has a built-in process to tune slow queries; the slowness might disappear by this process after some time.
FetchXML Slowness
Several factors can cause slowness in Microsoft Dataverse when using FetchXml. Some common causes include inefficient query structure, incorrect use of query options, and excessive database load.
To optimize performance, it is important to follow guidelines for improving query performance and avoid using incorrect query hints
General recommendations
- Using object type code instead of object type code name will result in faster query execution. For example, using activitytypecode instead of activitytypecodename will be faster.
- Leading wildcards and computed columns are known to be slow. Also, searching on columns with large lengths, such as Descriptions in Emails, is known to be slow. Please avoid using them unless they are necessary.
- Please make sure to only select the columns that are required.
- Field-level security-enabled fields are known to have performance issues.
Odata and SDK performance
When retrieving data using OData and SDK in Microsoft Dynamics 365, there can be several performance issues to consider. Some of these issues include:
- Network Latency: The performance of data retrieval can be affected by the latency of the network connection between the client and the Dynamics 365 server. This can result in slower response times for data retrieval operations
- Query Optimization: Inefficient queries can lead to poor performance when retrieving data. It is important to optimize the queries by specifying only the necessary fields and applying appropriate filters to reduce the amount of data returned.
- Large Data Sets: If the retrieved data set is large, it can impact the performance of the retrieval operation. It is recommended to paginate the results and retrieve data in smaller chunks to improve performance.
- Server Load: The performance of data retrieval can be affected by the overall load on the Dynamics 365 server. If the server is experiencing high usage or heavy processing, it can result in slower response times for data retrieval operations.
- Caching: Enabling caching mechanisms can improve the performance of data retrieval by reducing the need to fetch data from the server repeatedly. Caching can be implemented at various levels, such as client-side caching or server-side caching.
Please check the below links for the blog series.
