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 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.
Comments
-
AMFTF: Do not forget to index your large SharePoint lists
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.
-
AMFTF: Do not forget to index your large SharePoint lists
You may find this discussion of interest, https://techcommunity.microsoft.com/t5/SharePoint/Limitations-of-Predictive-Indexing/td-p/158375
-
AMFTF: Do not forget to index your large SharePoint lists
Nope. I had to add them myself. The second screenshot is from the 50k list without indexes.
Figure 1
Figure 2
-
AMFTF: Do not forget to index your large SharePoint lists
Indexes should be added automatically, are you seeing this happen in your large lists?
-
AMFTF: Do not forget to index your large SharePoint lists
What version of SharePoint were you using?
*This post is locked for comments