Guidance to create high-performing queries in Azure Boards
TFS 2017 | TFS 2015 | TFS 2013
While you can easily create work item queries, to create high-performing queries requires a deeper understanding. By improving your query performance, you improve your individual productivity, dashboard performance, and resource rate limits.
Note
Reference to service or resource rate limits only applies to queries run against Azure DevOps Services. To learn more, see Service limits and rate limits.
This article provides general guidelines on how to write a high-performing query. These guidelines apply to the following queries you create:
Create focused, selective queries
The first guideline for a high-performing query is to define a highly selective query. To do this, apply all filters that are necessary for your query. The more selective the query is, the smaller the result set is. The smaller the result set is, the more targeted and selective your query is.
Use tags to categorize work items
Use work item tags to categorize your work items instead of a custom field. Queries that filter on tags usually perform faster over those queries that filter on string matches.
Unlike custom field matches or partial matches, a query with a Tags Contains operation doesn't require a complete scan of all work item tables.
Use Contains words for string matches
To filter on a string match, use the Contains Words instead of the Contains operator. The Contains Words operator runs a full-text search on the specified field, which tends to complete more quickly.
The Contains operator runs a table scan, which is a slower operation than that used with the Contains Words operator. It also consumes more CPU cycles. These CPU cycles can cause you to encounter rate limitations. For more information, see Service limits and rate limits and Rate limits.
Specify small groups with the In Group operator
The In Group operator filters work items based on matches within a group of values. The group of values correspond to those contained within a team, security group, or work tracking category. For example, you can create a query to find all work items that are assigned to any member of a team. Or, find all work items that belong to the requirements category (Microsoft.RequirementCategory).
When you filter on a group that contains a large number of members, your result set tends to be larger and non-selective. Also, if a group corresponds to a large Azure Active Directory (Azure AD) group, the query generates a fairly large cost to resolve that group from Azure AD.
Avoid use of negated operators
Negated operators—such as <>, Not In, Not Under, Not In Group—tend to generate non-selective queries and large result sets.
Only use negated operators when absolutely necessary. Always try to find alternatives first. For example, if Field1 has values A, B, C, D; specify the Field1 In A, B, C clause, instead of the negated Field1 <> D clause.
Avoid string comparisons
Queries that contain string comparisons generate table scans that are inherently inefficient. Instead, we recommend you use tags or a specific custom field as alternatives, particularly when a query performs poorly.
Limit Or operators
Try to limit the number of Or operators defined in your query. Queries run better when fewer Or operators are used. Too many Or operators can make your query non-selective. If your query runs slowly, reorder the Or operator clause towards the top of the query clauses.
Save your query
Save your query to improve query performance. Due to internal optimizations, saved queries tend to perform better over unsaved queries. Always save your query when you plan to reuse it. Even WIQL queries run through a REST API, save the WIQL through the web portal to make your REST API calls less prone to future performance regressions.
SDK resources
To programmatically interact with queries, see Query for Bugs, Tasks, and Other Work Items.