For optimal performance, you should only select the minimum amount of data needed by your application when querying CRM data.  Queries that include a defined ColumnSet where the ColumnSet.AllColumns property is ‘true’ instruct the CRM data access platform to issue a SELECT * on all physical data included in the query plan.  This scenario should be avoided whenever possible.  
 

Violation Examples

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.NewGuid(), new ColumnSet(true));

Guideline 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 column) method call
    var query = new QueryExpression("account");
    query.ColumnSet.AddColumn("name");
    query.ColumnSet.AddColumn("address1_city");
 
    var results = service.RetrieveMultiple(query);
ColumnSet.AddColumns(param string[] columns) method call
    var query = new QueryExpression("account");
    query.ColumnSet.AddColumns("name", "address1_city");
 
    var results = service.RetrieveMultiple(query);

Usage of the Retrieve method should set the columnSet parameter to a ColumnSet instance with specified columns.  Usage of QueryExpression should set the  QueryBase.ColumnSet property with the required attributes. 

The following messages contain reference a ColumnSet instance:

Message
ConvertQuoteToSalesOrderRequest Class 
ConvertSalesOrderToInvoiceRequest Class 
GenerateInvoiceFromOpportunityRequest Class 
GenerateQuoteFromOpportunityRequest Class 
GenerateSalesOrderFromOpportunityRequest Class 
QueryByAttribute Class 
QueryExpression Class 
RetrieveAllChildUsersSystemUserRequest Class 
RetrieveBusinessHierarchyBusinessUnitRequest Class 
RetrieveMembersTeamRequest Class 
RetrieveRequest Class 
RetrieveSubsidiaryTeamsBusinessUnitRequest Class 
RetrieveSubsidiaryUsersBusinessUnitRequest Class 
RetrieveTeamsSystemUserRequest Class 
RetrieveUnpublishedRequest Class 
RetrieveUserSettingsSystemUserRequest Class 
ReviseQuoteRequest Class 
SearchByBodyKbArticleRequest Class

Build queries with QueryExpression

In Microsoft Dynamics 365 (online & on-premises), you can use the QueryExpression class to programmatically build a query containing data filters and search conditions that define the scope of a database search. A query expression is used for single-object searches. For example, you can create a search to return all accounts that match certain search criteria. The QueryBase class is the base class for query expressions. There are two derived classes: QueryExpression and QueryByAttribute. The QueryExpression class supports complex queries. The QueryByAttribute class is a simple means to search for entities where attributes match specified values.

Query expressions are used in methods that retrieve more than one record, such as the IOrganizationService.RetrieveMultiple method, in messages that perform an operation on a result set specified by a query expression, such as BulkDeleteRequest and when the ID for a specific record is not known.

In addition, there is a new attribute on the organization entity, Organization.QuickFindRecordLimitEnabled. When this Boolean attribute is true, a limit is imposed on quick find queries. If a user provides search criteria in quick find that is not selective enough, the system detects this and stops the search. This supports a faster form of quick find and can make a big performance difference.

References:  

Use the QueryExpression Class

http://msdn.microsoft.com/en-us/library/gg334688.aspx

ColumnSet Class

http://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.query.columnset.aspx

Use of the ColumnSet Class

http://msdn.microsoft.com/en-us/library/gg309532.aspx

Please follow and like us: