cancel
Showing results for 
Search instead for 
Did you mean: 
Rick72

AMFTF: Do not forget to index your large SharePoint lists

SharePoint has the functionality to index columns on a SharePoint list. Indexing has a positive impact on performance especially when using large lists. I did some testing and in this blog post I share my findings.

 

To get a more quantified view how indexing a column impacts performance, I create an app for this. Ofcourse, there is an app for everything Robot LOL I have added an image to better understand the app.

 

Indexing.PNG

 

Four lists were used. Two (1k-1 & 1k - 2) had 1000 items and two (50k - 1 & 50k - 2) had 50000 items. The lists ending on '1' had an indexed column. This column contained all kind of countries. The lists contained sales info and I filtered on the country 'Sweden'.

 

I created a timer which triggered the 'OnSelect' of the four collect buttons (they have 'Sweden' in it) every 60 seconds and let it run 21 times. Every collect button did a serie of actions. Below is the 'OnSelect' property of button "Sweden - 1k - 1".

 

UpdateContext({start: Now()});
ClearCollect(onek1, Filter('1000records1', Country="Sweden"));
UpdateContext({stop: Now()});
UpdateContext({diffonek1: DateDiff(start, stop, Milliseconds)});
Collect(colDiffOneK1, diffonek1);
UpdateContext({A1: Average(colDiffOneK1, Value)})

 

The first number after a collection button is the last measured value. The second number is the average over the 21 runs. 

 

I plotted the collections (colDiffOneK1, etc.) on the charts at the botton of the screen.

 

This information made me conclude:

Adding an index to a SharePoint list has a big positive impact on performance when using large lists. When using smaller lists, the impact is smaller but indexing still sounds valid because it looks that the extremes (spikes) are relative larger for non-indexed collections.

Comments

What version of SharePoint were you using?

 

Hi @DLGross,

 

SharePoint Online.

 

Robot Happy Rick

Indexes should be added automatically, are you seeing this happen in your large lists?

Nope. I had to add them myself. The second screenshot is from the 50k list without indexes.

 

Figure 1

1.PNG

 

Figure 2

2.PNG

I used the "Import Spreadsheet" app in SharePoint Online with Internet Explorer to add a list with 50k items. It took around one hour if I remember correctly to add the 50k items. So that are ~14 items per second.