Slow running Managed Client Object Model application

Question

I have an application that I built using the Managed Client Object model that performaing poorly and I am curious if I am doing something wrong. The application drills down throw a hierarchy of three parent/child lists. The code I use to access the correct list items at each level looks like this with different list names and slightly different logic:

        ClientContext clientContext = new ClientContext(siteUrl);
        SP.List oList = clientContext.Web.Lists.GetByTitle("Application Inventory");
        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml = "<View><RowLimit>100</RowLimit><Query><OrderBy><FieldRef Name='Title'/></OrderyBy></Query></View>";
        SP.ListItemCollection collListItem = oList.GetItems(camlQuery);
        clientContext.Load(collListItem);
        clientContext.ExecuteQuery();

        foreach (SP.ListItem oListItem in collListItem.ToList<SP.ListItem>())
        {
                          //this gets the second level of the hierarchy
            TreeNode tn = GetAppSolutions(Convert.ToInt32(oListItem["ID"]), new TreeNode(oListItem["Title"].ToString()));
            tv_AppData.Nodes.Add(tn);
        }

My issue is that this code takes about a minute to run and render my ASP.NET treeview. Can anyone see something that I am doing here that could cause this to happen?

As you may be able to see from the above code I am looping through each item in the parent list and making calls to SharePoint for each item to get it's child items. Could this be the problem?

Answer 1

I'm assuming GetAppSolutions() is your own homegrown function, in which case it also looks like you are not passing the clientContext into it, which means that it must be creating a new context for every item in the list, which can be a huge performance hit. Try passing the clientContext, olist and whatever else makes sense to the GetAppSolutions() routine so that you are not constantly refetching the same data over and over.

I would also store the return value of collListItem.ToList() in a variable and then iterating through the variable instead. SharePoint has been known to rebuild entire collections when iterating through them in the manner you have listed.

Answer 2

With reference to your comment: to further increase the performance the next step is to reduce the size and amount of round trips (reduce the number of calls to ExecuteQuery).

• Try loading / queueing up more into ClientContext before calling ExecuteQuery (e.g. if you've loaded up the App Solutions 10 times, then call ExecuteQuery and process the results rather than calling ExecuteQuery for each one sequentially). Also, request only specific things you need e.g. ctx.load(list, l=>l.Title). If you don't pass in the second parameter, the query will fetch all.
• Try deploying your logic to an azure function or Azure API so that the code runs in Azure. This shortens the distance from where ExecuteQuery is run and the SPO servers. My site crawler was double the speed when run in Azure.
• If you take the API approach, you could look at caching the cascading list data in memory by a background thread every hour or so; so then the API will just return the cascading data immediately rather than doing queries each time.