Microsoft Dynamics 365 F&O query optimization: Essential tips for enhancing performance

Performance issues are a common concern for Microsoft Dynamics 365 solutions. They often arise when performance considerations are not adequately addressed during system implementation, upgrades, and modifications.
To ensure your Dynamics 365 Finance and Operations environment operates at its best, it is important to conduct regular audits, develop optimization plans, and identify and resolve performance bottlenecks.
SQL query optimization plays a key role in Dynamics 365 performance optimization. It enhances database query performance by refining the query’s execution plan while preserving its result.

This blog will cover the concept of query optimization in Dynamics 365 Finance and Operations, explore common challenges, and provide techniques and best practices for effective SQL query optimization.

What is query optimization?

Query optimization is the process of choosing the most efficient means of executing SQL queries. The goal is to reduce the time and resources required to retrieve data from a database while maintaining the accuracy and integrity of the results.
SQL is a non-procedural language, so the optimizer is free to merge, reorganize, and process in any order. The database optimizes each SQL statement based on statistics collected about the accessed data.

Query optimization in Dynamics 365 Finance and Operations (F&O) refers to the process of enhancing the efficiency and speed of SQL queries executed within the system. This process involves various techniques and best practices to improve data retrieval performance, reduce resource consumption, and ensure the system runs smoothly. Effective query optimization can significantly impact the overall performance of Dynamics 365 F&O, leading to faster response times and a more efficient use of system resources.

Common challenges in Query Optimization

Optimizing queries can be complex due to various factors that affect performance. Here are some of the common issues in Dynamics 365 F&O query optimization:

• Complex data structures: Intricate relationships between tables can complicate query design and optimization, leading to inefficient data retrieval.
• Large data volumes: Handling extensive datasets can result in slow query performance and high resource consumption.
• Suboptimal indexing: Ineffective or missing indexes can degrade query performance, slowing data retrieval and increasing database load.
• Poor query design: Inefficient query writing, such as using SELECT * or unnecessary joins, can lead to excessive data retrieval and slow response times.
• High concurrency: Concurrent access can cause locking and contention issues, impacting overall query performance and system responsiveness.

How to optimize queries in Dynamics 365 Finance and Operations?

Optimizing SQL query performance in Dynamics 365 Finance and Operations (D365 F&O) is essential for maintaining efficient and responsive systems. Here are some Microsoft Dynamics 365 F&O query optimization techniques:

• Understand the context: When optimizing queries in D365 F&O, ensure that you focus on queries within the D365 F&O environment to avoid optimizing the wrong elements.
• Add missing indexes: Improve query performance by adding appropriate indexes. Create indexes on columns that are frequently used in WHERE clauses to speed up query performance.
• Avoid multiple OR conditions: Simplify queries to reduce complexity and improve execution time. Use UNION or other alternatives to avoid multiple OR conditions, which can slow down queries.
• Use wildcards judiciously: Use wildcards only when necessary to prevent slow searches. Place wildcards at the end of search patterns rather than the beginning to improve search efficiency.
• Minimize JOINs: Reduce the number of JOINs in queries to decrease complexity and execution time.
• Avoid SELECT DISTINCT: Use more specific query conditions to achieve unique results without SELECT DISTINCT.
• Specify SELECT fields: Specify only the necessary fields in SELECT statements instead of using SELECT * to reduce data retrieval time.
• Use TOP to sample query results: The TOP clause limits the number of rows returned, which can speed up query execution.
• Run queries during off-peak hours: Schedule heavy queries during off-peak hours to minimize impact on system performance.
• Minimize query hints: Avoid excessive use of query hints, as they can override the query optimizer’s decisions and degrade performance.
• Reduce large write operations: Break down large write operations into smaller batches to avoid locking and improve performance.

Examples of query optimization for Dynamics 365 Finance and Operations

1: Non-optimized x++ query

This involves improving the performance of X++ queries by refining how data is accessed and processed within the application layer.

2: Non-optimized query with multiple tables

A non-optimized query with multiple tables in Dynamics 365 refers to a query that retrieves data from several tables.

static void NonOptimizedSelectQuery(Args _args)
{
// Create a query object
Query query = new Query();
// Add the CustTable as the primary data source
QueryBuildDataSource custTableQBDS = query.addDataSource(tableNum(CustTable));
// Add the SalesTable as a related data source
QueryBuildDataSource salesTableQBDS = custTableQBDS.addDataSource(tableNum(SalesTable));
// Add the LogisticsPostalAddress as a related data source
QueryBuildDataSource addressQBDS = custTableQBDS.addDataSource(tableNum(LogisticsPostalAddress));
// Add a range to filter customers by a specific name
QueryBuildRange custNameRange = custTableQBDS.addRange(fieldNum(CustTable, Name));
custNameRange.value(‘Contoso’);
// Add a range to filter sales orders by a specific date
QueryBuildRange salesDateRange = salesTableQBDS.addRange(fieldNum(SalesTable, SalesDate));
salesDateRange.value(date2str(today()));

// Add a range to filter addresses by a specific city
QueryBuildRange cityRange = addressQBDS.addRange(fieldNum(LogisticsPostalAddress, City));
cityRange.value('Seattle'); 

// Create a query run object
QueryRun queryRun = new QueryRun(query);
// Iterate over the query results
while (queryRun.next())
{
CustTable custTable = queryRun.get(tableNum(CustTable));
SalesTable salesTable = queryRun.get(tableNum(SalesTable));
LogisticsPostalAddress address = queryRun.get(tableNum(LogisticsPostalAddress));
// Process the data from multiple tables
info(strFmt(“Customer: %1, Sales Order: %2, Address: %3”, custTable.Name, salesTable.SalesId, address.City));
}
}

3: Optimized x++ query

Using insert_recordset is a common optimization technique in X++ for handling bulk data inserts efficiently. It is a key part of optimizing queries to improve performance, especially in scenarios involving large datasets or complex data manipulations in Dynamics 365 F&O.

The insert_recordset statement is efficient because it generates a single SQL statement that is sent to the server once rather than looping through the data and inserting records individually.

Here is an example of using the insert_recordset statement in X++ within Microsoft Dynamics 365 Finance and Operations:

NameValuePair nameValuePair;
CustTable custTable;
int id_var = 1;
str name_var = ‘Name1’;
int value_var = 1;
insert_recordset nameValuePair (Id, Name, Value)
select firstonly id_var, name_var, value_var from custTable;

Best practices and techniques for improving SQL query performance

Optimizing SQL queries can significantly reduce execution times, enhance system performance, and improve overall user experience. Here are some best practices and techniques for improving SQL query performance, helping you to maintain a high-performing and reliable D365 F&O environment.

Leverage query store

Use Query Store, a powerful SQL query optimization tool introduced in SQL Server 2016, to troubleshoot performance issues and view query history, plans, and runtime statistics. Query Store can help monitor regressed queries, identify expensive queries, and troubleshoot performance in everyday scenarios.

Monitor query performance

Plan the optimization by regularly monitoring Dynamics 365 F&O query performance metrics like execution plans, input/output statistics, buffer cache usage, latency, indexes, and memory-optimized tables to evaluate runtime, detect performance issues, and improve query efficiency.

Consider off-peak execution

Schedule heavy query executions during off-peak hours to prevent server overload and avoid restricting access to tables by other users.

Minimize query hints

Be cautious when using query hints for optimization as they can have unexpected side effects or become obsolete over time. Regularly monitor, manage, and update query hints to ensure their continued relevance and effectiveness.

Optimize write operations

Large write operations like writing, modifying, or deleting data can impact query performance. Optimize these operations by adding indexes, checking constraints, and processing triggers efficiently to avoid performance bottlenecks.

Use AOT query

An AOT query with specific fields and adding ranges can speed up the query’s execution process. AOT queries are used to retrieve and display data from the database based on specific criteria. They allow developers to create and manage queries that are used in forms, reports, and other parts of the application. These queries can be customized by selecting fields, adding ranges, and defining data relationships to optimize performance and ensure they meet business requirements.

Key aspects of AOT queries include:

• Use indexes: If you’re querying a database, ensure that appropriate indexes are set up on the columns you frequently query by. This improves Dynamics 365 F&O query speed and data retrieval. Indexes Improving query performance in Dynamics 365 F&O.
• Filter data: Narrow down your query results by applying filters on relevant criteria, such as specific securities, time periods, or trading strategies.
• Aggregate data: Instead of querying raw tick data, consider aggregating data into larger time intervals (e.g., minutes, hours) if your analysis allows it. This can reduce the volume of data being processed.
• Optimize joins: If your query involves joining multiple tables, ensure that join conditions are efficiently written, and consider using inner joins instead of outer joins whenever possible.
• Limit results: If you only need a subset of the data, use limits to restrict the number of rows returned by the query.

Summing up

With these tips and best practices, you can enhance the performance of your Dynamics 365 environment. While it’s possible to implement these changes yourself, partnering with professional developers can help troubleshoot slow queries in Dynamics 365 F&O and ensure optimal results. As a Microsoft Solutions Partner with a team of expert Dynamics 365 consultants and Dynamics 365 F&O query optimization experts, Confiz specializes in Dynamics 365 Finance and Operations implementation and customization. Contact us at marketing@confiz.com for expert assistance and support.