Rediger

Del via

Do not retrieve Entity all columns via query APIs

  • Artikkel

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:

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