Query optimization principles

  • 10 minutes

Poorly created queries can cause performance issues. You can apply a few best practices to queries to help avoid unnecessary impacts, including:

  • All queries should pull only the required data needed - Your queries should pull from a defined list of fields. For example, if you need to pull a customer’s account number, you should limit your query to only pull the Account number field. Pulling unnecessary fields decreases performance.
  • Avoid nested queries - Nested queries are queries within a query. For example, a nested query occurs if you create a query to pull the Customer table and then create a second query to pull the Sales table based on the first query. Instead, use a join to link the two tables into a single query.

The four types of joins that you can make with select queries are:

  • Join - Pulls records that match on both tables, such as an inner join.
  • Outer join - Pulls records regardless of whether those records match on both tables.
  • Exists join - Pulls all records from the first table that match the records in the second table. No records from the second table are returned.
  • Notexists join - Pulls all records from the first table that don’t match records in the second table. No records from the second table are returned.

Using the appropriate join can increase performance by reducing the number of unneeded records and fields that are pulled.

On large tables and rapidly growing tables, such as an inventory transaction table, you should use an index and index hint in queries to sort the data. The keyword index tells the system to look for an optimal index to use to sort the data. The index hint suggests an index.

Maintain diagnostic validation rules

Some tools can help you identify areas to optimize performance. The Optimization advisor workspace within finance and operations apps is where you can view a list of messages from the last performance check of possible opportunities toward better performance.

You can maintain the rules and frequency of the performance check from System administration > Periodic tasks > Maintain diagnostics validation rule and System administration > Periodic tasks > Schedule diagnostics validation rule.

Dynamics 365 Lifecycle Services provides tools to help you monitor performance. To access these tools, go to the Cloud-hosted environments page and select the Full details link.

Optimization tools

Finance and operations apps can use different tools for monitoring and diagnosing performance issues, including:

  • Trace Parser, which includes long-running X++ methods, time-consuming SQL queries, and client server calls.
  • Performance SDK, which is for performance load test.
  • SQL Insights in Lifecycle Services, which includes performance metrics, index analysis, live view, queries, and actions for a specific environment.
  • SQL trace, which uses a SQL profiler on a Tier-1 environment.
  • Monitoring of server health metrics in Lifecycle Services for healthcheck for AOS, batch frameworks, the Data Import/Export framework, Microsoft Azure SQL, and Management reporter.

Optimize data migration

Data migration is a key success factor in almost every implementation. A primary concern for customers is how long a data migration might take, which depends on the amount of data that’s being migrated. Not all data entities are optimized for migration and the data volume that a migration might have.

You can make certain configurations to the Data management framework to help improve performance in data-migration scenarios, including:

  • Turn off change tracking - Change tracking is useful when you’re performing incremental data exports, and it slows down the migration process. Turning off change tracking helps improve performance.
  • Turn on set-based processing - The Set-based processing field on the Data entity page is typically turned off. Set-based processing means bulk operations to the database instead of single record operations. You can also set the set-based processing feature at the top node for the data entity by setting the Enable Set Based SQL Operations and Support Set based SQL Operations properties to On.
  • Create a data migration batch group - You should perform migration when few or no other activities are occurring on your system. Then, it might be helpful to run the jobs in a batch group.
  • Enable priority-base batch scheduling - Optimize how batch jobs are run, which can use reserved capacity.
  • Set up the maximum number of batch threads - The default maximum number of batches on a server configuration is eight. However, you can increase that to 12 or 16 threads. We recommend that you don’t set the number of threads higher than 16 without conducting full performance testing to determine potential system impacts.
  • Import in batch mode - Use this configuration to use optimization configuration instead of a single thread.
  • Clean staging tables - We recommend that you clean up staging tables. For more information, see Clean up the staging tables.
  • Update statistics - Updating statistics across associated tables can be helpful, and you can do so by using Lifecycle Services.
  • Entity execution parameters - In the data import/export framework parameters on the Entity settings page, you can select the Configure entity execution parameters. Additionally, use the Import threshold record count and Import task count fields for optimization.
  • Validations - You can turn off the ability to perform migration business validations and business logic by inserting and updating, which means that validateWrite() won’t run for validation and insert() and update() won’t run on the table. Also, you can turn off validation at the field level.

General recommendations for optimizing data migration include:

  • Separate files into smaller chunks.
  • Test performance in an appropriate Tier-2 or higher environment.
  • Test performance in a mock cutover before going live.

Take notes when testing migration performance so that you’ve collected information on how to set up entities, batches, parameters, and other items.