Optimize performance using QueryExpression

Статия
06/05/2024

This article describes ways you can optimize performance when retrieving data using QueryExpression.

Patterns to avoid

Composing optimized queries for Dataverse is vital to ensure applications provide a fast, responsive, and reliable experience. This section describes patterns to avoid and concepts to understand when composing queries for standard tables using the RetrieveMultiple message, or messages that have a parameter that inherits from the QueryBase class. This guidance also applies when sending a GET request against a collection of records using OData. The guidance here might not apply for Elastic tables or when using Dataverse Search.

Minimize the number of selected columns

Don't include columns you don't need in your query. Queries that return all columns or include a large number of columns can encounter performance issues due to the size of the dataset or complexity of the query.

This practice is especially true for logical columns. A logical column contains values that are stored in different database tables. The AttributeMetadata.IsLogical property tells you whether a column is a logical column. Queries that contain many logical columns are slower because Dataverse needs to combine the data from other database tables.

Avoid leading wild cards in filter conditions

Queries that use conditions with leading wild cards (either explicitly, or implicitly with an operator like ends-with) can lead to bad performance. Dataverse can't take advantage of database indexes when a query using leading wild cards, which forces SQL to scan the entire table. Table scans can happen even if there are other non-leading wild card queries that limit the result set.

The following example is a FetchXml condition element that uses a leading wild card:

<condition attribute='accountnumber'
   operator='like'
   value='%234' />

The following example is a QueryExpression ConditionExpression that uses a leading wild card:

new ConditionExpression("accountnumber", ConditionOperator.Like, "%234")

The following example is a OData query that uses a leading wild card:

$filter=startswith(accountnumber,'%234')

When queries time out and this pattern is detected, Dataverse returns a unique error to help identify which queries are using this pattern:

Name: LeadingWildcardCauseTimeout
Code: 0x80048573
Number: -2147187341
Message: The database operation timed out; this may be due to a leading wildcard value being used in a filter condition. Please consider removing filter conditions on leading wildcard values, as these filter conditions are expensive and may cause timeouts.

Dataverse heavily throttles leading wild card queries that are identified as a risk to the health of the org to help prevent outages. Learn more about query throttling

If you find yourself using leading wild card queries, look into these options:

Use Dataverse search instead.
Change your data model to help people avoid needing leading wild cards.

Learn more about using wildcard characters in conditions for string values

Avoid using formula or calculated columns in filter conditions

Formula and calculated column values are calculated in real-time when they're retrieved. Queries that use filters on these columns force Dataverse to calculate the value for each possible record that can be returned so the filter can be applied. Queries are slower because Dataverse can't improve the performance of these queries using SQL.

When queries time out and this pattern is detected, Dataverse returns a unique error to help identify which queries are using this pattern:

Name: ComputedColumnCauseTimeout
Code: 0x80048574
Number: -2147187340
Message: The database operation timed out; this may be due to a computed column being used in a filter condition. Please consider removing filter conditions on computed columns, as these filter conditions are expensive and may cause timeouts.

To help prevent outages, Dataverse applies throttles on queries that have filters on calculated columns that are identified as a risk to the health of the environment. Learn more about query throttling

Avoid ordering by choice columns

When using FetchXml or QueryExpression, when you order query results using a choice column, the results are sorted using the localized label for each choice option. Ordering by the number value stored in the database wouldn't provide a good experience in your application. You should know that ordering on choice columns requires more compute resources to join and sort the rows by the localized label value. This extra work makes the query slower. If possible, try to avoid ordering results by choice column values.

Note

OData is different. With the Dataverse Web API, $orderby sorts rows using the integer value of the choice column rather than the localized label.

Ordering by columns on related tables makes the query slower because of the added complexity.

Ordering by related tables should only be done when needed to as described here:

Avoid using conditions on large text columns

Dataverse has two types of columns that can store large strings of text:

StringAttributeMetadata can store up to 4,000 characters.
MemoAttributeMetadata can store a higher number.

The limit for both of these columns is specified using the MaxLength property.

You can use conditions on string columns that have a MaxLength configured for fewer than 850 characters.

All memo columns or string columns with a MaxLength greater than 850 are defined in Dataverse as large text columns. Large text columns are too large to effectively index, which leads to bad performance when included in a filter condition.

Dataverse search is a better choice to query data in these kinds of 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. 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";

Споделяне чрез

Optimize performance using QueryExpression

Patterns to avoid

Minimize the number of selected columns

Avoid leading wild cards in filter conditions

Avoid using formula or calculated columns in filter conditions

Avoid ordering by choice columns

Avoid using conditions on large text columns

Query Hints

No lock

Union Hint

See also

Обратна връзка

Допълнителни ресурси

Споделяне чрез

Optimize performance using QueryExpression

Patterns to avoid

Minimize the number of selected columns

Avoid leading wild cards in filter conditions

Avoid using formula or calculated columns in filter conditions

Avoid ordering by choice columns

Avoid ordering by columns in related tables

Avoid using conditions on large text columns

Query Hints

No lock

Union Hint

See also

Обратна връзка

Допълнителни ресурси