Improve performance by adding indices to SharePoint Online

SharePoint Online has the functionality to index columns on a 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.

My findings are based on a canvas app and 2 SharePoint Online 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
    }
)

Leave a Reply

Your email address will not be published.