In one of our SharePoint list we have more than 10 look-up columns with managed metadata type. One fine day our UAT SharePoint site went very low and the CPU utilization in DB Servers associated with the UAT farm started increasing up to 80%. At this point of time the total number of UAT users involved in testing are only 4 to 7. We have around 25k+ terms stored under different terms sets.

Cause: When we started investigating we found that below key points

• CAML query calls coming from the custom pages to get managed metadata column values are the most expensive queries on the DB server side.
• This behavior is happening only when we access those particular custom pages for the first time after an IIS reset.

We all know SharePoint will take more time when we load anything after IIS Reset. But it should not consume more CPU and get into deadlock situation.

Imagine the situation if we move this code to production with 10 to 15 concurrent users? The whole UAT SharePoint farm users and shared resources will be affected because of the CAML Query Issue after any IIS reset.

Solution:

When we approached MS, they suggested “Every taxonomy field of the item should have a hidden field together; e.g. with the Category (taxonomy) field, there should also be a hidden field named something like Category_0 (in my farm), try to use this field in the view fields instead and see if it will improve the results. The reason is that the hidden field is not a lookup field that it shouldn’t need to do any follow up queries to SQL but 1 single query should do the job. Note that the hidden field doesn’t just contain the value that it will also contain the Taxonomy ID, so after getting the value of the hidden field, you will need to parse it to extract the field value. But this should minimize the queries sending to SQL.”

When we modified our CAML queries with the hidden column, we found that the CPU utilization is not increasing and the site performance also very decent.

I am not sure is there any better way to improve this. If you have any ideas, let me know.

Important Note: The timer job which updates the taxonomy look-up column will not update this hidden column until there is a change in the item where it is looked-up. We were informed by MS that they are working on this issue.

To validate the hidden column names, I am using SharePoint 2013 Client Browser tool.

Sample JavaScript Code

var siteUrl = "/";

var listfld_Product;
var listfld_Release;

var listfld_Product_str;
var listfld_Release_str;

var Inputstr = "Product A";

function retrieveInternalNames() {
  try {
    var clientContext = new SP.ClientContext(siteUrl);
    var oList = clientContext
      .get_web()
      .get_lists()
      .getByTitle("CA_Product_Master_List");
    clientContext.load(oList);

    listfld_Product = oList.get_fields().getByTitle("Product_0");
    listfld_Release = oList.get_fields().getByTitle("Release_0");

    clientContext.load(listfld_Product);
    clientContext.load(listfld_Release);

    clientContext.executeQueryAsync(
      function() {
        listfld_Product_str = listfld_Product.get_internalName();
        listfld_Release_str = listfld_Release.get_internalName();
        retrieveListItems();
      },
      function(sender, args) {
        alert(
          "Request failed. " + args.get_message() + "\n" + args.get_stackTrace()
        );
      }
    );
  } catch (err) {
    alert(err.message);
  }
}

function retrieveListItems() {
  try {
    var clientContext = new SP.ClientContext(siteUrl);
    var oList = clientContext
      .get_web()
      .get_lists()
      .getByTitle("CA_Product_Master_List");

    var camlQuery = new SP.CamlQuery();

    camlQuery.set_viewXml(
      "<View><ViewFields><FieldRef Name='" +
        listfld_Product_str +
        "' Type='Notes'/><FieldRef Name='" +
        listfld_Release_str +
        "' Type='Notes'/></ViewFields><Query> <Where> <BeginsWith> <FieldRef Name='" +
        listfld_Product_str +
        "' /><Value Type='Notes'>" +
        Inputstr +
        "|" +
        "</Value> </BeginsWith> </Where> </Query></View>"
    );

    this.collListItem = oList.getItems(camlQuery);
    clientContext.load(collListItem);
    clientContext.executeQueryAsync(
      Function.createDelegate(this, this.onQuerySucceeded),
      Function.createDelegate(this, this.onQueryFailed)
    );
  } catch (err) {
    alert(err.message);
  }
}

function onQuerySucceeded(sender, args) {
  var listItemInfo = "";
  var listItemEnumerator = collListItem.getEnumerator();
  while (listItemEnumerator.moveNext()) {
    var oListItem = listItemEnumerator.get_current();

    var releaseArray = oListItem
      .get_item(listfld_Release.get_internalName())
      .split(";");
    var rleaseInfo = "";
    for (var i = 0; i < releaseArray.length; i++) {
      rleaseInfo += releaseArray[i].split("|")[0] + ";";
    }

    listItemInfo +=
      oListItem.get_item(listfld_Product.get_internalName()).split("|")[0] +
      "" +
      rleaseInfo +
      "";
  }

  document.getElementById("div_Prod_Data").innerHTML = listItemInfo;
}

function onQueryFailed(sender, args) {
  alert("Request failed. " + args.get_message() + "\n" + args.get_stackTrace());
}

_spBodyOnLoadFunctionNames.push("retrieveInternalNames");