SharePoint 优化

5000条优化

思路
1. 查询列表最大id
2. 循环每次查询5000条，知道大于最大id

前台示例

  // 5000 件最大id
  var maxId = "";
  var searchLine = 5000;
  var startindexgroup = 1;
  // 查询样本量
  function GetSampleCountInfo()
  {
      var siteUrl = _spPageContextInfo.webServerRelativeUrl;
      var clientContext = new SP.ClientContext(siteUrl);
      this.CurrentList = clientContext.get_web().get_lists().getByTitle("列表名称");
      var viewquery = "<View><RowLimit>1</RowLimit>" +
                          "<Query>" +
                              "<OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy>" +
                          "</Query>" +
                      "<ViewFields><FieldRef Name='ID' /></ViewFields></View>";
      var MaterialDetailscamlQuery = new SP.CamlQuery();
      MaterialDetailscamlQuery.set_viewXml(viewquery);
      this.MaxDataItem = this.CurrentList.getItems(MaterialDetailscamlQuery);
      clientContext.load(this.MaxDataItem);
      clientContext.executeQueryAsync(
          Function.createDelegate(this, this.FindDataMaxIdSucceeded),
          Function.createDelegate(this, this.QueryFailed)
      );
  }
  function FindDataMaxIdSucceeded()
  {
      var mdEnumerator = this.MaxDataItem.getEnumerator();
      while (mdEnumerator.moveNext()) {
          var item = mdEnumerator.get_current();
          var ID = item.get_item('ID');
          maxId = ID;
      }

      startindexgroup = 1;
      QueryMaterialDetails();
  }
  function QueryMaterialDetails()
  {
      var siteUrl = _spPageContextInfo.webServerRelativeUrl;
      var clientContext = new SP.ClientContext(siteUrl);
      var allquery = "";
      this.CurrentList = clientContext.get_web().get_lists().getByTitle("列表名称");

      var queryCaml = "<And><Leq><FieldRef Name='ID'/><Value Type='Counter'>" + startindexgroup * searchLine + "</Value></Leq>"
          + "<Gt><FieldRef Name='ID'/><Value Type='Counter'>" + (startindexgroup - 1) * searchLine+ "</Value></Gt></And>";

      // 循环拼接查询xml
      var queryForStr = "";
      let queryCount = 0;
      RCData.forEach(element => {
          if (queryCount > 1)
          {
              queryForStr = "<Or>"+queryForStr+"</Or>";
          }
          if (DropdownField.indexOf() > 0)
          {
              // 下拉框用包含的方式查询
              queryForStr += "<Contains><FieldRef Name='" + element.ClassficationDimensionEn + "' /><Value Type='Text'>" + element.ClassficationName + "</Value></Contains>";
          }
          else
          {
              queryForStr += "<Eq><FieldRef Name='" + element.ClassficationDimensionEn + "' /><Value Type='Number'>" + element.ClassficationName + "</Value></Eq>";
          }
          queryCount ++;
      });
      if (queryCount > 1)
      {
          queryCaml += "<Or>"+queryForStr+"</Or>";
      }
      else
      {
          queryCaml += queryForStr;
      }

      // 查询全部
      allquery = "<View><Query><Where>" + queryCaml + "</Where></Query>"+ProductsMainViewFields+"</View>";

      var MaterialDetailscamlQuery = new SP.CamlQuery();
      MaterialDetailscamlQuery.set_viewXml(allquery);
      this.Currentitems = this.CurrentList.getItems(MaterialDetailscamlQuery);
      clientContext.load(this.Currentitems);
      clientContext.executeQueryAsync(
          Function.createDelegate(this, this.QueryMaterialDetailsSuccess),
          Function.createDelegate(this, this.QueryFailed)
      );
  }
  function QueryMaterialDetailsSuccess()
  {
      var mdEnumerator = this.Currentitems.getEnumerator();
      while (mdEnumerator.moveNext()) {
          var item = mdEnumerator.get_current();

          // 具体业务逻辑
      }

      if (startindexgroup * searchLine < maxId) {
          startindexgroup++;
          QueryMaterialDetails();
      } else {
          startindexgroup = 1;
          BindData();
      }	
  }

后台示例

  private int MaxID = 0;
  private int NUM_PER_SELECT = 5000;
  /// <summary>
  /// 查询目前所有的【产品信息主数据】
  /// </summary>
  /// <param name="context"></param>
  /// <returns></returns>
  private List<ProductsMainModel> GetProductsMainList(ClientContext context)
  {
      // 当前所有【产品信息主数据】
      List<ProductsMainModel> ProductsMainList = new List<ProductsMainModel>(); 
      // 最大ID
      if (MaxID == 0)
      {
          MaxID = FindMaxID(context);
      }

      int Productstartindexgroup = 1;

      RecursionSelectProductData(context, Productstartindexgroup, ProductsMainList);

      return ProductsMainList; 
  }

  /// <summary>
  /// 查询最大ID
  /// </summary>
  /// <param name="context"></param>
  /// <returns></returns>
  public int FindMaxID(ClientContext context)
  {
      // 获取客户端上下文
      // ClientContext context = SPContext.CreateClientContext(webUrl);
      int id = 0;
      string strqueryone = "<RowLimit>1</RowLimit>" +
                          "<Query>" +
                          "<OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy>" +
                          "</Query>";

      List productsMainlist = context.Web.Lists.GetByTitle(ProductsMainListName);
      CamlQuery SonQuery = new CamlQuery();
      SonQuery.ViewXml = "<View>" + strqueryone + "</View>";
      ListItemCollection listitems = productsMainlist.GetItems(SonQuery);
      context.Load(listitems);
      context.ExecuteQuery();
      foreach (ListItem listitem in listitems)
      {
          int count = listitems.Count;
          id = Convert.ToInt32(listitem["ID"]);
          break;
      }
      return id;
  }

  private List<ProductsMainModel> RecursionSelectProductData(ClientContext context, int productstartindexgroup, List<ProductsMainModel> ProductsMainList)
  {
      List list = context.Web.Lists.GetByTitle(ProductsMainListName);
      string strquery = @"<Query>" +
                      @"   <Where>" +
                      //@"      <And>" +
                      @"         <And>" +
                      @"            <Gt>" +
                      @"               <FieldRef Name='ID' />" +
                      @"               <Value Type='Counter'>" + (productstartindexgroup - 1) * NUM_PER_SELECT + "</Value>" +
                      @"            </Gt>" +
                      @"            <Leq>" +
                      @"               <FieldRef Name='ID' />" +
                      @"               <Value Type='Counter'>" + productstartindexgroup * NUM_PER_SELECT + "</Value>" +
                      @"            </Leq>" +
                      @"         </And>" +
                      //@"         <Neq>" +
                      //@"            <FieldRef Name='DataStatus' />" +
                      //@"            <Value Type='Text'>申请中</Value>" +
                      //@"         </Neq>" +
                      //@"      </And>" +
                      @"   </Where>" +
                      @"   <OrderBy>" +
                      @"      <FieldRef Name='ID' Ascending='True' />" +
                      @"   </OrderBy>" +
                      @"</Query>";
      strquery += @"<ViewFields>
                      <FieldRef Name='ClassficationName' />
                      <FieldRef Name='DataStatus' />
              </ViewFields>";
      CamlQuery querycaml = new CamlQuery();
      querycaml.ViewXml = "<View>" + strquery + "</View>";

      ListItemCollection items = list.GetItems(querycaml);
      context.Load(items);
      context.ExecuteQueryWithIncrementalRetry(SSBGConfig.RETRY_COUNT, SSBGConfig.DELAY_SECOND);
      LogHelper.Debug(ProductsMainListName, items.Count.SafeToString());
      foreach (ListItem item in items)
      {
          // 跳过不符合条件的数据
          string dataStatus = item["DataStatus"].SafeToString();
          if (dataStatus.Equals("申请中"))
          {
              continue;
          }

          //  结果数据整理
          ProductsMainModel mainData = new ProductsMainModel();
          // ......
          mainData.ClassficationName = item["ClassficationName"].SafeToString();
          mainData.DataStatus = dataStatus;

          ProductsMainList.Add(mainData);
      }

      if (productstartindexgroup * NUM_PER_SELECT < MaxID)
      {
          productstartindexgroup++;
          // 递归调用继续查询
          RecursionSelectProductData(context, productstartindexgroup, ProductsMainList);
      }

      // 查询结束以后，直接返回查询结果
      return ProductsMainList;
  }

查询速度优化

建索引
caml语句查询的时候，查询具体的字段（指定要查询的具体字段），不全查所有的字段

插入/更新异常

循环插入的时候，后台会出现莫名的错误；排查方向：
1. iis的链接时常是不是太短；
2. 使用下面的代码循环的时候批量插入，不一条一条的插入；

/// <summary>
/// 插入【待配置学习结果[MLLearningResult]】列表
/// </summary>
/// <param name="mlLearningResultModelList"></param>
/// <param name="context"></param>
private void InsertMLLearningResult(List<MLLearningResultModel> mlLearningResultModelList)
{
    LogHelper.MethodStart("插入【待配置学习结果[MLLearningResult]】列表");
    int count = 1;
    try
    {
        context = SPContext.CreateClientContext(AnalysisStatics.webUrl);
        List resultDataList = context.Web.Lists.GetByTitle("待配置学习结果");

        LogHelper.Info(string.Format("插入【待配置学习结果[MLLearningResult]】列表条数：{0}条", mlLearningResultModelList == null ? 0 : mlLearningResultModelList.Count));
        // 循环插入学习样本数据列表
        foreach (MLLearningResultModel resultData in mlLearningResultModelList)
        {
            ListItemCreationInformation creation = new ListItemCreationInformation();
            ListItem listitem = resultDataList.AddItem(creation);

            listitem["Title"] = resultData.Title;
            listitem["LearningBatch"] = resultData.LearningBatch;
            // ......

            // 更新
            listitem.Update();

            // 每100条提交一次
            if (count % 100 == 0)
            {
                LogHelper.Info(string.Format("count:{0}", count));
                context.ExecuteQueryWithIncrementalRetry(SSBGConfig.RETRY_COUNT, SSBGConfig.DELAY_SECOND);

                // 初始化相关信息
                context = SPContext.CreateClientContext(AnalysisStatics.webUrl);
                resultDataList = context.Web.Lists.GetByTitle("待配置学习结果");
            }
            else if (count == mlLearningResultModelList.Count)
            {
                // 最后一条未满足100的倍数，不足100条的提交一次
                LogHelper.Info(string.Format("count:{0}", count));
                context.ExecuteQueryWithIncrementalRetry(SSBGConfig.RETRY_COUNT, SSBGConfig.DELAY_SECOND);
            }
            count++;
        }
    }
    catch (Exception ex)
    {
        LogHelper.Error("Part.3 【Method.InsertMLLearningResult】查询异常");
        LogHelper.Info(string.Format("count:{0}", count));
        LogHelper.Error(ex);
        throw ex;
    }

    LogHelper.MethodEnd("插入【待配置学习结果[MLLearningResult]】列表");
}

首页 > 学习总览 > 开发语言 > SharePoint

blogSite

我是谁？我在哪儿？我在干什么？

SharePoint 优化

5000条优化

查询速度优化

插入/更新异常