Improve performance by adding indices to SharePoint lists

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

My findings are based on a canvas app and 2 SharePoint lists I created. Both lists hold 50.000 items. One list has no indices while the other one has.

What I saw in the app was:

• Retrieving records was much faster for the list with indices than without.
• Filtering server-side on two columns was significant faster than filtering on one and filtering in the canvas app on the second.

The galleries in the app show the time to retrieve the data in milliseconds. The code shown below is the property Text of the labels in the galleries.

DateDiff(ThisItem.DateTime1, ThisItem.DateTime2, Milliseconds)

The code below is placed in the property OnSelect of the related buttons.

A video in support of this blog post can be viewed on my YouTube channel here.

Gallery 1

Refresh('50k - List One');
UpdateContext({var_DateTime1: Now()});
ClearCollect(
    coll_List1,
    Filter(
        '50k - List One',
        (Country = "Netherlands") && ('Item Type' = "Fruits")
    )
);
UpdateContext({var_DateTime2: Now()});
Collect(
    coll_DateTime1,
    {
        DateTime1: var_DateTime1,
        DateTime2: var_DateTime2
    }
)

Gallery 2

Refresh('50k - List Two');
UpdateContext({var_DateTime1: Now()});
ClearCollect(
    coll_List2,
    Filter(
        '50k - List Two',
        (Country = "Netherlands") && ('Item Type' = "Fruits")
    )
);
UpdateContext({var_DateTime2: Now()});
Collect(
    coll_DateTime2,
    {
        DateTime1: var_DateTime1,
        DateTime2: var_DateTime2
    }
)

Gallery 3

Refresh('50k - List Two');
UpdateContext({var_DateTime1: Now()});
ClearCollect(
    coll_List3_Temp,
    Filter(
        '50k - List Two',
        Country = "Netherlands"
    )
);
ClearCollect(
    coll_List3,
    Filter(
        coll_List3_Temp,
        'Item Type' = "Fruits"
    )
);
UpdateContext({var_DateTime2: Now()});
Collect(
    coll_DateTime3,
    {
        DateTime1: var_DateTime1,
        DateTime2: var_DateTime2
    }
)