Optimizing FetchXML Performance in Dataverse with Query Hints and Best Practices

FetchXML Optimization Techniques in Microsoft Dataverse

FetchXML is the native query language of the Microsoft Dataverse platform, widely used in Power Platform and Dynamics 365 environments for interacting with relational data. However, in complex scenarios involving large datasets and intricate entity relationships, FetchXML can exhibit significant performance issues.

This contribution presents two advanced optimization techniques:

1. The use of specific query hints through the options attribute;

2. A systematic restructuring of the <filter> block to facilitate the generation of more efficient SQL execution plans.

Internal Translation to T-SQL

Microsoft Dataverse internally translates FetchXML queries into T-SQL statements executed by SQL Server. Execution plan optimization is therefore heavily influenced by:

• The structure of the FetchXML (particularly the placement of filters and linked entities);

• The SQL engine's ability to effectively use indexes and statistics.

Query Hints

Query hints are optional directives that modify the behavior of the underlying T-SQL compiler, influencing how the execution plan is generated.

Syntax

Query hints are passed via the options attribute of the <fetch> node, as follows:

<fetch version="1.0" options="ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS,OptimizeForUnknown">
  ...
</fetch>

Hint Descriptions

ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS
Promotes the use of ascending key indexes even when historical modifications exist in the data. This is particularly effective for temporal datasets or audit trails.

OptimizeForUnknown
Disables a technique known as parameter sniffing, prompting SQL Server to generate an execution plan based on generic statistical distributions instead of the current parameter values.

Filter Restructuring: Centralized Approach

A structural strategy that complements the use of query hints involves centralizing filters related to linked entities in the root <filter> node using the entityname attribute.

Optimized Example

<filter type="and">
  <condition attribute="statecode" operator="eq" value="0" />
  <condition entityname="om" attribute="statecode" operator="eq" value="0" />
  <condition entityname="om" attribute="hera_fineomologa" operator="on-or-after" value="2025-06-27" />
  <condition entityname="om" attribute="hera_inizioomologa" operator="on-or-before" value="2025-06-27" />
  <condition entityname="om" attribute="hera_uldid" operator="eq" value="b59a9af7-8335-f011-8c4e-7c1e5273d7a6" />
</filter>

Expected Benefits

• All filter predicates are explicitly defined at the root level, enabling more effective global optimization;

• Avoids nested filters within <link-entity> blocks, which can hinder SQL plan optimization;

• Improves FetchXML code readability and maintainability.

Experimental Results

In test environments involving queries with 3–5 linked entities and temporal filters on datasets with over 100,000 records, the combined use of query hints and filter restructuring reduced response times from:

• ~10,000 ms (baseline)
  to

• ~100–200 ms (optimized)

These results were achieved in Dynamics 365 environments with complex data models and significant customizations.

Discussion

While the results are promising, selective application of these techniques is advised. Query hints can sometimes degrade performance when applied to simple or frequently reused queries. Filter centralization, however, is generally recommended in all scenarios involving multiple linked entities.

Optimizing FetchXML in Dataverse is essential to ensure adequate performance in business applications based on Power Platform. The combined use of specific query hints and rational restructuring of filter logic are effective and repeatable approaches to significantly improve query execution times.

References:

• Microsoft Docs – Optimize performance using FetchXML

• Microsoft Docs – Hints (Transact-SQL)