Optimize performance using FetchXml

Note

For guidance about general things to avoid when composing Dataverse queries, see Query anti-patterns. The following sections are specific to FetchXml.

Late Materialize query

If you select many lookup and computed columns, and you're experiencing performance issues, you can try setting the fetch element boolean latematerialize attribute. Behind the scenes, this setting breaks the query into smaller parts and reassembles the results before returning them to you.

Using the latematerialize attribute might not always provide a performance benefit. It might make simple queries run more slowly. It's most beneficial when your query:

• Has many joins
• Contains many lookup or computed columns

Query Hints

Important

Only apply these options when recommended by Microsoft technical support. Incorrect use of these options can damage the performance of a query.

Microsoft SQL Server supports many query hints to optimize queries. FetchXML supports query hints and can pass these query options to SQL Server using the fetch element options attribute.

Query option	SQL Server hint
ForceOrder	Force Order
DisableRowGoal	Hint: DISABLE_OPTIMIZER_ROWGOAL
EnableOptimizerHotfixes	Hint: ENABLE_QUERY_OPTIMIZER_HOTFIXES
LoopJoin	Loop Join
MergeJoin	Merge Join
HashJoin	Hash Join
NO_PERFORMANCE_SPOOL	NO_PERFORMANCE_SPOOL
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS	Hint: ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS

More information: Hints (Transact-SQL) - Query

No lock

In earlier versions, the no-lock attribute used to prevent shared locks on records. It's no longer necessary to include this attribute.

Union Hint

You can improve performance when adding a filter element that sets the condition for columns in different tables by setting the hint attribute to union. But there are some restrictions:

• The filter must use the or filter type.
• Each query can contain only one union hint.
• If a filter with union hint isn't at top level filter, Dataverse transforms the query and move the filter with a union hint to root filter.
• If a union hint is more than three levels deep, it's ignored.

The following example sets a filter with the union hint on the telephone1 column for both the account and contact tables.

<fetch>
   <entity name="email">
      <attribute name="activityid" />
      <attribute name="subject" />
      <filter type="and">
         <condition attribute="subject"
            operator="like"
            value="Alert:%" />
         <condition attribute="statecode"
            operator="eq"
            value="0" />
         <filter type="or"
            hint="union">
            <condition attribute="telephone1"
               operator="eq"
               value="555-123-4567"
               entityname="ac" />
            <condition attribute="telephone1"
               operator="eq"
               value="555-123-4567"
               entityname="co" />
         </filter>
      </filter>
      <link-entity name="account"
         from="accountid"
         to="regardingobjectid"
         link-type="outer"
         alias="ac" />
      <link-entity name="contact"
         from="contactid"
         to="regardingobjectid"
         link-type="outer"
         alias="co" />
   </entity>
</fetch>

See also

Query data using FetchXml
Use FetchXml to retrieve data
Select columns using FetchXml
Join tables using FetchXml
Order rows using FetchXml
Filter rows using FetchXml
Page results using FetchXml
Aggregate data using FetchXml
Count rows using FetchXml
FetchXml reference
FetchXml sample code
Query anti-patterns