AMFTF: Do not forget to index your large SharePoint lists

[Rick72]

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  I have added an image to better understand the app.

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.