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:
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)
