Performance issue: CAML against a large list

How do you measure and tune performance on the custom form of the SharePoint list?

Recently, I was lucky enough to take part in the performance issue investigation.
We have a huge custom form, in which we pull data from 6 lists.

Here are the steps that I took to investigate performance issue on the custom form:

1.  I  turned the Developer Dashboard On.

It allowed me to see the execution total time and the specific action in that execution.

Since I knew, we were dealing with related items loading from different lists, I focused on the "EnsureListItemsData" on the left side.

EnsureListItemsData is related , exactly, to list items loading.

How do you know which EnsureListItemsData# relates to what list?


2. I turned stack trace on in the web.config file


In the trace you can search by specific EnsureListItemsData#[number] to find the SQL query where one of the parameters is a list name.


3. Before changing anything, I documented  the average execution time for every related list.

4. I identified the most expensive list items load


5. Then, I moved to the code to investigate how the form was written regarding this list.

I have looked at the code and found useful to analyze this piece:

<sharepoint:spdatasource runat="server" id="dsDocument" datasourcemode="List" useinternalname="true"

                            scope="Recursive" selectcommand="<View><Query><Where><Eq><FieldRef Name=' Mngr_Id' LookupId='TRUE'></FieldRef><Value Type='Integer'>{Mngr_Id}</Value></Eq></Where></Query><ViewFields><FieldRef Name='ID'/><FieldRef Name='ContentType'/><FieldRef Name='EncodedAbsThumbnailUrl'/><FieldRef Name='FileRef'/><FieldRef Name='FileLeafRef'/><FieldRef Name='Title'/></ViewFields></View>">

                          <SelectParameters>

                              <asp:Parameter Name="ListName" DefaultValue="Documents" />

                              <asp:QueryStringParameter runat="server" Name="Mngr_Id" QueryStringField="ID" />

                          </SelectParameters>

                        </sharepoint:spdatasource>

Since, the CAML was trying to get the data from List by the field Mngr_Id, I wanted to check if the field is indexed

It turned out that is not indexed!

That means every time the CAML query  selects all items from the list and then applies filter.  by the provided value in the field Mngr_Id.

Here is an article in MSDN that discuses Query Throttling and Indexing. "How Does Indexing Affect Throttling?" precisely describes the current situation in my custom form that uses CAML query.

Recommendations:

1. To Optimize the select.

Here the options that I can see:

1. Set index on the column Mngr_Id in the Documents list:

Here is a PowerShell script to setup the index on SharePoint list automatically.

The script helps in case you have an array of the lists that you want to index.
Of you want to have an automated process of the changes in SharePoint Farm.

I have tested index Mngr_Id :

Measurement before setting the index:

EnsureListItemsData#6 (1575.84 ms)

Measurement after setting the index:

EnsureListItemsData#6 (675.89 ms)

2. Change the CAML query:

2a. Using subfolders. That calls for re-organization items in the document , a folder name should match the Manager Title. This was you MAY probably specify from what view you can select in the spdatasource control;

2. To analyze the changed web application settings:

We have to keep the default settings.

Default:

List View Lookup Threshold 8

List View Threshold  5000

Instead of:

Modified config threshold settings:

List View Lookup Threshold 36

List View Threshold 500 000

This allows us to restrict developers beforehand and optimize the structure and code.

Wish you a fast SharePoint site and calm administrative days)