Edit

Query anti-patterns to avoid in Dataverse

Composing optimized queries for Dataverse is vital to ensure applications provide a fast, responsive, and reliable experience. Learn about query anti-patterns to avoid when composing queries for standard tables by 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 by using OData.

Note

The guidance in this article 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 problems due to the size of the dataset or complexity of the query.

Minimize the number of selected logical columns

Avoid requesting too many columns, especially 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 wildcards in filter conditions

Queries that use conditions with leading wildcards (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 uses leading wildcards, which forces SQL to scan the entire table. Table scans can happen even if there are other nonleading wildcard queries that limit the result set.

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

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

When queries time out and this pattern is detected, Dataverse returns a unique error to help identify which queries use 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 wildcard queries that are identified as a risk to the health of the environment to help prevent outages. When a query fails due to throttling and this pattern is detected, Dataverse returns a unique error:

Name: DataEngineLeadingWildcardQueryThrottling
Code: 0x80048644
Number: -2147187132
Message: This query cannot be executed because it conflicts with Query Throttling; the query uses a leading wildcard value in a filter condition, which will cause the query to be throttled more aggressively.
or
This query is throttled as it negatively impacts the database health; the query uses a leading wildcard value in a filter condition, which will cause the query to be throttled more aggressively.

If you use leading wildcard queries, consider these options:

  • Use Dataverse search instead.
  • Change your data model to help people avoid needing leading wildcard values.

Other wildcard characters

As described in Use wildcard characters in conditions for string values, other characters beyond the percent sign (%) character can act like a wildcard. The following query strings also behave like leading wildcards:

  • _234%
  • [^a]234%

Dataverse heavily throttles queries with search strings that start with these other leading wildcard special characters.

Hyphen and apostrophe (single quote) characters

Database collation Unicode sorting rules make some search strings that start with a hyphen (-) or an apostrophe (') perform like leading wildcard searches. Search strings that start with a hyphen or apostrophe can't take advantage of database indexes if the search string doesn't contain a nonwildcard character before the occurrence of the % character in the string. For example, -%, -%234, '%, and '%751 can't efficiently use database indexes, while -234% and '751% can. Dataverse heavily throttles inefficient search strings that start with hyphens or apostrophes. To understand more about the database collation Unicode sorting rules for hyphens and apostrophes, see SQL Server collations.

Avoid using formula or calculated columns in filter conditions

Formula and calculated column values are calculated in real-time when you retrieve them. 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 by using SQL.

When queries time out and this pattern is detected, Dataverse returns a unique error to help identify which queries use 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.

Dataverse heavily throttles queries that have filters on calculated columns that are identified as a risk to the health of the environment to help prevent outages. When a query fails due to throttling and this pattern is detected, Dataverse returns a unique error:

Name: DataEngineComputedColumnQueryThrottling
Code: 0x80048744
Number: -2147186876
Message: This query cannot be executed because it conflicts with Query Throttling; the query uses a computed column in a filter condition, which will cause the query to be throttled more aggressively.
or
This query is throttled as it negatively impacts the database health; the query uses a computed column in a filter condition, which will cause the query to be throttled more aggressively.

Avoid ordering by choice columns

When you use FetchXml or QueryExpression, and you order query results by a choice column, you sort the results by the localized label for each choice option. Ordering by the number value stored in the database doesn't provide a good experience in your application. 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. By using the Dataverse Web API, $orderby sorts rows by the integer value of the choice column rather than the localized label.

Avoid ordering by columns in related tables

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 in the following sections:

Avoid using conditions on large text columns

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

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

Use conditions on string columns that have a MaxLength set to 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.

Dataverse error for query timeouts caused by anti-patterns

When a query times out and uses one of the anti-patterns detailed on this page, Dataverse returns the following unique error. This error helps you identify which anti-patterns the query uses:

Name: PerformanceValidationIssuesCauseTimeout
Code: 0x80048575
Number: -2147187339
Message: The database operation timed out; this may be due to the query performance issues identified in a query executed on this request. Please optimize the query by addressing the following identified performance issues: {0}. Please reference this document for guidance: https://go.microsoft.com/fwlink/?linkid=2300520

The {0} part of the exception message lists the anti-pattern that the query is using. If there are multiple anti-patterns used by the query, they're comma separated. For example, if a query is filtering on a large text column and is also selecting a large number of columns, the exception message contains the string PerformanceLargeColumnSearch,LargeAmountOfAttributes. The following table lists the anti-patterns with links to explanations:

Anti-pattern identifier Explanation link
PerformanceLeadingWildCard Avoid leading wild cards in filter conditions
PerformanceLargeColumnSearch Avoid using conditions on large text columns
OrderOnEnumAttribute Avoid ordering by choice columns
OrderOnPropertiesFromJoinedTables Avoid ordering by columns in related tables
LargeAmountOfAttributes Minimize the number of selected columns
LargeAmountOfLogicalAttributes Minimize the number of selected logical columns
FilteringOnCalculatedColumns Avoid using formula or calculated columns in filter conditions

Use the guidance on this page to understand the anti-patterns. Modify the query to avoid using these anti-patterns.

Note

If a query contains either the PerformanceLeadingWildCard or the FilteringOnCalculatedColumns anti-pattern, Dataverse throws a different error. Queries that use the PerformanceLeadingWildCard anti-pattern throw the LeadingWildcardCauseTimeout error mentioned on this page. Queries that use the FilteringOnCalculatedColumns anti-pattern throw the ComputedColumnCauseTimeout error mentioned on this page.

The LeadingWildcardCauseTimeout and ComputedColumnCauseTimeout errors predate the PerformanceValidationIssuesCauseTimeout error. To maintain backward compatibility, Dataverse continues to throw LeadingWildcardCauseTimeout and ComputedColumnCauseTimeout.

Query throttling
Use wildcard characters in conditions for string values
Query data using FetchXml
Query data using QueryExpression
Use OData to query data

  • Last updated on