Performance Tuning for List Rollups

Performance Tuning for List Rollups

As one might expect, the performance of List Rollup is affected by two major components:

  • the number of Lists
  • the number of List Items that are included in a rollup
  • the number of columns displayed for each item
  • the type of column displayed

The actual time it takes to perform a rollup will vary depending on server hardware and general system load. Some servers may be able to quickly process rollups containing over 50 lists and some will experience noticeable delays processing as few as 20. The best way to determine the practical limit in your environment is to try rollups with a varying number of Lists.

Some tips to improve performance are listed in the table below:

Tip Explanation
Review the types of columns displayed

SharePoint data is stored as text. When it is retrieved for display in a list rollup, non-text data (i.e., dates, KPIs) needs to be formatted. This sometimes takes more time than just getting the data. To improve performance,

  • minimize the number of non-text columns, if possible
  • if you have some date columns that you don’t need to sort, create a calculated column in the list(s) that converts the date to a text column. A typical formula for this is:

    =IF([Review Date],TEXT([Review Date],"mm/dd/yyy"),"-")
    
Review the number of columns displayed Only display the columns that your end users really need to see. If you are rolling up Tasks assigned to [Me] (the logged in user), do you need to display the Assigned To column?
Review the number items displayed If you are rolling up events, do you need to see events that occurred in the past?

I don’t see any data in my rollup – what’s wrong?

I don’t see any data in my rollup – what’s wrong?

Issue:

You had a CAML working great on one group of lists. You created a list template for one of the lists in the group and used it to create another set of similar lists. You then created a rollup for the new group and applied the same CAML filter. No data was returned. What happened?

Shown below is one of the “new” lists that you want to rollup:

example list.png

Specifically, you want to rollup all the items assigned to the logged in user that are older than 30 days (in other words, where the status is “Open” and the Date Received is Less than or Equal to Today minus 30 days).

Resolution:

The latest version of List Rollup uses the Internal Column Name in the CAML query. If you use the Display Name of a column, you may not get the results that you expect because if a column name has been changed, the Internal Name is different than the Display Name.

In the example, the new lists created from the list templates had a few columns renamed. The original CAML for the List Rollup filter was changed to reflect the new column names. The revised CAML looked like this:

<Where>
<And><And>
  <Eq><FieldRef Name="AssignedTo" /><Value Type="Integer"><UserID /></Value></Eq>
  <Leq><FieldRef Name="DateReceived" />
    <Value Type="DateTime"><Today /><addValue value="-30" /></Value>
  </Leq>
</And>
<Eq><FieldRef Name="Status" /><Value Type="Choice">Open</Value></Eq>
</And>
</Where>

No results were returned when using this filter because the DateReceived column was renamed and the Display Name did not match the Internal Name. The Internal (or original) Name of the column is StartDate.

Icon-WarningIMPORTANT: A column’s Display Name and it’s Internal Name will be the SAME unless the column was renamed. You should not run into this issue unless you rename a column.

Changing the CAML to that shown below fixes the issue. Note the changed FieldRef Name in the fourth line.

<Where>
<And><And>
  <Eq><FieldRef Name="AssignedTo" /><Value Type="Integer"><UserID /></Value></Eq>
  <Leq><FieldRef Name="StartDate" />
    <Value Type="DateTime"><Today /><addValue value="-30" /></Value>
  </Leq>
</And>
<Eq><FieldRef Name="Status" /><Value Type="Choice">Open</Value></Eq>
</And>
</Where>

Icon-TipTip: To check the value of the Internal Name of a column, follow the steps in the table below:

Step Action
1. Go to the List Settings for the list in question.
2. In the Columns section of the page, click the name of the column in question.
3. When the Change Column page appears, look at the URL in the browser address bar. The parameter called Field,shown at the end of the URL, is the Internal Name for the column.

Internal vs Display Name.png