如何优化Dynamics CRM查询？请求遇System.TimeoutException超时

1. 为过滤/关联字段添加索引 #

Dynamics CRM对自定义字段默认不创建索引，你的查询里用到了Contact.win_Country和Contact.win_Brand两个自定义字段做过滤，加上关联用的Incident.CustomerId、ActivityPointer.RegardingObjectId，建议检查并添加索引：

• 登录Dynamics CRM后台，进入高级设置 > 自定义 > 自定义系统 > 实体
• 找到对应实体（Contact、Incident、Activity Pointer），进入字段，编辑目标字段后勾选对应索引选项（查找字段选「为查找列创建索引」，文本字段选「为文本列创建索引」）
• 保存并发布变更，索引生效后可大幅提升过滤和关联速度

2. 调整查询关联顺序，先过滤小数据集 #

当前查询从Activity实体开始关联，会先扫描大量Activity记录再过滤。建议改为从Contact开始，先筛选出符合条件的Contact记录，再关联Incident和Activity，缩小初始数据集范围：

public List<T> GetMarketActivities(win_market market)
{
    var team = (EntityReference)((AliasedValue)market["bu.win_defaultteam"]).Value;

    // 从Contact开始构建查询，先过滤目标数据
    var query = new QueryExpression(Contact.EntityLogicalName);
    query.ColumnSet = new ColumnSet(false); // 不需要Contact字段，仅用于关联
    query.Criteria.AddCondition(new ConditionExpression(Contact.Fields.win_Country, ConditionOperator.Equal, market.win_marketcountry.Id));
    query.Criteria.AddCondition(new ConditionExpression(Contact.Fields.win_Brand, ConditionOperator.Equal, market.win_marketbrand.Id));

    // 关联Incident
    LinkEntity caseLink = new LinkEntity(Contact.EntityLogicalName, Incident.EntityLogicalName, Contact.Fields.ContactId, Incident.Fields.CustomerId, JoinOperator.Inner);
    caseLink.ColumnSet = new ColumnSet(false); // 不需要Incident字段
    query.LinkEntities.Add(caseLink);

    // 关联Activity并过滤OwnerId
    LinkEntity activityLink = new LinkEntity(Incident.EntityLogicalName, _activity.LogicalName, Incident.Fields.IncidentId, ActivityPointer.Fields.RegardingObjectId, JoinOperator.Inner);
    activityLink.ColumnSet = new ColumnSet(ActivityPointer.Fields.ActivityId, ActivityPointer.Fields.OwnerId);
    activityLink.LinkCriteria.AddCondition(new ConditionExpression(ActivityPointer.Fields.OwnerId, ConditionOperator.NotEqual, team.Id));
    activityLink.TopCount = 100;
    caseLink.LinkEntities.Add(activityLink);

    var results = _organizationService.RetrieveMultiple(query);

    // 提取关联的Activity实体
    if (results.Entities?.Count > 0)
    {
        return results.Entities
            .Select(e => e.GetRelatedEntity<T>(activityLink.EntityAlias))
            .Where(a => a != null)
            .ToList();
    }

    return new List<T>();
}

3. 限制关联实体的返回字段集 #

原查询未限制关联实体的返回字段，即使不需要也会返回默认字段，增加数据传输量。修改LinkEntity时指定仅返回关联所需字段：

// 原caseLink修改
LinkEntity caseLink = new LinkEntity(_activity.LogicalName, Incident.EntityLogicalName, 
    ActivityPointer.Fields.RegardingObjectId, Incident.Fields.IncidentId, JoinOperator.Inner);
caseLink.ColumnSet = new ColumnSet(Incident.Fields.IncidentId, Incident.Fields.CustomerId); // 仅保留关联用字段
query.LinkEntities.Add(caseLink);

// 原contactLink修改
LinkEntity contactLink = new LinkEntity(Incident.EntityLogicalName, Contact.EntityLogicalName, 
    Incident.Fields.CustomerId, Contact.Fields.ContactId, JoinOperator.Inner);
contactLink.ColumnSet = new ColumnSet(Contact.Fields.ContactId); // 仅保留关联用字段
contactLink.LinkCriteria.AddCondition(new ConditionExpression(Contact.Fields.win_Country, ConditionOperator.Equal, market.win_marketcountry.Id));
contactLink.LinkCriteria.AddCondition(new ConditionExpression(Contact.Fields.win_Brand, ConditionOperator.Equal, market.win_marketbrand.Id));
caseLink.LinkEntities.Add(contactLink);

4. 启用分页查询 #

即使设置了TopCount，单次查询仍可能因数据集过大超时，改用分页分批次获取数据：

public List<T> GetMarketActivities(win_market market)
{
    var team = (EntityReference)((AliasedValue)market["bu.win_defaultteam"]).Value;
    List<T> activities = new List<T>();
    int pageNumber = 1;
    string pagingCookie = null;

    do
    {
        var query = new QueryExpression(_activity.LogicalName);
        query.TopCount = 50; // 每页取50条
        query.ColumnSet = new ColumnSet(ActivityPointer.Fields.ActivityId, ActivityPointer.Fields.OwnerId);
        query.Criteria.AddCondition(new ConditionExpression(ActivityPointer.Fields.OwnerId, ConditionOperator.NotEqual, team.Id));
        
        // 关联设置同原代码（需配合步骤3的字段限制）
        LinkEntity caseLink = new LinkEntity(_activity.LogicalName, Incident.EntityLogicalName, 
            ActivityPointer.Fields.RegardingObjectId, Incident.Fields.IncidentId, JoinOperator.Inner);
        caseLink.ColumnSet = new ColumnSet(Incident.Fields.IncidentId, Incident.Fields.CustomerId);
        query.LinkEntities.Add(caseLink);

        LinkEntity contactLink = new LinkEntity(Incident.EntityLogicalName, Contact.EntityLogicalName, 
            Incident.Fields.CustomerId, Contact.Fields.ContactId, JoinOperator.Inner);
        contactLink.ColumnSet = new ColumnSet(Contact.Fields.ContactId);
        contactLink.LinkCriteria.AddCondition(new ConditionExpression(Contact.Fields.win_Country, ConditionOperator.Equal, market.win_marketcountry.Id));
        contactLink.LinkCriteria.AddCondition(new ConditionExpression(Contact.Fields.win_Brand, ConditionOperator.Equal, market.win_marketbrand.Id));
        caseLink.LinkEntities.Add(contactLink);

        // 设置分页参数
        query.PageInfo = new PagingInfo
        {
            PageNumber = pageNumber,
            PagingCookie = pagingCookie,
            Count = query.TopCount.Value
        };

        var result = _organizationService.RetrieveMultiple(query);
        if (result.Entities?.Count > 0)
        {
            activities.AddRange(result.Entities.Select(e => e.ToEntity<T>()));
        }

        // 更新分页状态
        pagingCookie = result.PagingCookie;
        pageNumber++;
    } while (!string.IsNullOrEmpty(pagingCookie) && activities.Count < 1000); // 最多取1000条

    return activities;
}

5. 替换为FetchXML（可选） #

部分复杂关联场景下，FetchXML的查询优化表现更优，可尝试转换为以下FetchXML执行：

<fetch top="100" >
  <entity name="activitypointer" >
    <attribute name="activityid" />
    <attribute name="ownerid" />
    <filter type="and" >
      <condition attribute="ownerid" operator="neq" value="{TEAM_ID}" />
    </filter>
    <link-entity name="incident" from="incidentid" to="regardingobjectid" link-type="inner" >
      <link-entity name="contact" from="contactid" to="customerid" link-type="inner" >
        <filter type="and" >
          <condition attribute="win_country" operator="eq" value="{COUNTRY_ID}" />
          <condition attribute="win_brand" operator="eq" value="{BRAND_ID}" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

代码中调用方式：

var fetchXmlTemplate = "<fetch top=\"100\" >...</fetch>"; // 替换为上述完整FetchXML
var fetchXml = string.Format(fetchXmlTemplate, team.Id, market.win_marketcountry.Id, market.win_marketbrand.Id);
var results = _organizationService.RetrieveMultiple(new FetchExpression(fetchXml));
// 后续实体转换逻辑同原代码

内容的提问来源于stack exchange，提问作者jbm_force_tranquille