Optimize performance using QueryExpression

2025-08-13

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

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. QueryExpression supports query hints and can pass these query options to SQL Server using the QueryExpression.QueryHints property.

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 QueryExpression.NoLock property used to prevent shared locks on records. It's no longer necessary to include this property

Union Hint

You can improve performance when adding a FilterExpression that sets the ConditionExpression for columns in different tables by setting the FilterExpression.FilterHint property to union. But there are some restrictions:

The FilterExpression.FilterOperator must use LogicalOperator.Or.
Each query can contain only one union hint.
If a FilterExpression 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.

QueryExpression query = new("email")
{
   ColumnSet = new ColumnSet("activityid", "subject"),
   Criteria = new FilterExpression(LogicalOperator.And)
   {
      Conditions = {
         {
            new ConditionExpression(
               attributeName:"subject",
               conditionOperator:ConditionOperator.Like,
               value: "Alert:%")
         },
         {
            new ConditionExpression(
               attributeName:"statecode",
               conditionOperator:ConditionOperator.Equal,
               value: 0)
         }
      },
      Filters = {
         {
            new FilterExpression(LogicalOperator.Or){
               FilterHint = "union",
               Conditions = {
                  {
                     new ConditionExpression(
                        attributeName:"telephone1",
                        conditionOperator:ConditionOperator.Equal,
                        value: "555-123-4567"){
                           EntityName = "ac"
                        }
                  },
                  {
                     new ConditionExpression(
                        attributeName:"telephone1",
                        conditionOperator:ConditionOperator.Equal,
                        value: "555-123-4567"){
                           EntityName = "co"
                        }
                  }
               }
            }
         }
      }
   }
};        

LinkEntity linkToAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "regardingobjectid",
      linkToAttributeName: "accountid",
      joinOperator: JoinOperator.LeftOuter);
linkToAccount.EntityAlias = "ac";

LinkEntity linkToContact = query.AddLink(
      linkToEntityName: "contact",
      linkFromAttributeName: "regardingobjectid",
      linkToAttributeName: "contactid",
      joinOperator: JoinOperator.LeftOuter);
linkToContact.EntityAlias = "co";

Automatic query optimization

Don't be surprised when the performance of a slow query improves without any change on your part. Dataverse actively monitors data retrieval operations to improve performance. When a specific query using standard tables performs poorly, Dataverse might automatically make changes that improve the performance of the query. This behavior might make certain performance issues appear transient because they can't be reproduced later.

Automatic query optimization doesn't require any configuration. It's enabled by default for everyone.