Do not retrieve Entity all columns via query APIs
Category: Performance
Impact potential: High
Symptoms
Retrieving all columns can cause:
- Performance issues due to the amount of data being retrieved
- Unintended plug-in/process execution
Guidance
For optimal performance, you should only select the minimum amount of data needed by your application when querying Microsoft Dataverse data.
ColumnSet Parameter
When you use the IOrganizationService.Retrieve method set the columnSet
parameter to a ColumnSet instance with specified columns. When you use QueryExpression set the ColumnSet property with the required attributes.
The following are some examples:
ColumnSet(param string[] columns) constructor overload for QueryExpression.
var query = new QueryExpression("account") { ColumnSet = new ColumnSet("name", "address1_city") }; var results = service.RetrieveMultiple(query);
ColumnSet(param string[] columns) constructor overload for RetrieveRequest.
var entity = service.Retrieve("account", Guid.NewGuid(), new ColumnSet("name", "address1_city"));
ColumnSet.AddColumn(String) method call.
var query = new QueryExpression("account"); query.ColumnSet.AddColumn("name"); query.ColumnSet.AddColumn("address1_city"); var results = service.RetrieveMultiple(query);
ColumnSet.AddColumns(String[]) method call.
var query = new QueryExpression("account"); query.ColumnSet.AddColumns("name", "address1_city"); var results = service.RetrieveMultiple(query);
The following classes contain a ColumnSet instance:
- ConvertQuoteToSalesOrderRequest
- GenerateInvoiceFromOpportunityRequest
- GenerateQuoteFromOpportunityRequest
- GenerateSalesOrderFromOpportunityRequest
- RetrieveAllChildUsersSystemUserRequest
- RetrieveBusinessHierarchyBusinessUnitRequest
- RetrieveMembersTeamRequest
- RetrieveRequest
- RetrieveSubsidiaryTeamsBusinessUnitRequest
- RetrieveSubsidiaryUsersBusinessUnitRequest
- RetrieveTeamsSystemUserRequest
- RetrieveUnpublishedRequest
- RetrieveUserSettingsSystemUserRequest
- ReviseQuoteRequest
- SearchByBodyKbArticleRequest
- IOrganizationService.Retrieve
- QueryExpression
Problematic patterns
Queries that include a defined ColumnSet where the AllColumns property is true
instruct the platform to issue a SQL command to "SELECT *" on all physical data included in the query plan. This scenario should be avoided whenever possible.
Warning
These scenarios should be avoided.
ColumnSet.AllColumns setter method call.
var columns = new ColumnSet(); columns.AllColumns = true; var query = new QueryExpression("account"); query.ColumnSet = columns; var results = service.RetrieveMultiple(query);
ColumnSet(bool allColumns) constructor overload.
var query = new QueryExpression("account") { ColumnSet = new ColumnSet(true) }; var results = service.RetrieveMultiple(query);
ColumnSet(bool allColumns) constructor overload for RetrieveRequest.
var entity = service.Retrieve("account", Guid.Parse("bec45132-392a-4617-b935-a64ef04738e4"), new ColumnSet(true));
Additional information
Queries submitted to retrieve data from Dynamics 365 should not select all columns. Rather, specific individual columns should be specified in the ColumnSet instance associated to the query. Retrieving all columns for an entity can have a negative impact on performance. Additionally, you can unintentionally trigger plug-in registration events by retrieving columns you are not working with and issuing an update.
See also
ColumnSet Class
Select columns using QueryExpression
Query data using QueryExpression
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應