Does The Order Of Sharepoint Filtering In A View Matter

Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced past the administrator.

Every SharePoint dev faced this effect at least once or multiple times. It's a well-known 'issue/limitation' when working with larger data sets in SharePoint lists. Some customers might exist asking for that 'contains search behaviour' which is not supported in a SharePoint search query.

In this blog post I'm going to try and find a solution for most filtering issues / scenarios in large SharePoint lists. I'll compare different methods to check which are limited in which cases. You will likewise discover a few tips & tricks, for whenever you want to filter large lists, to avoid the threshold limitations.

In 1 of my next web log posts, we'll also bank check how SharePoint is handling this limitation in its mod listing UI and what will work / what will intermission whenever we hit the list threshold of 5000 items.

On a side note: this does not mean that my methods / tips & tricks are the all-time solution for yous. These are just a few solutions that I thought of and used in the past to solve different large list query scenarios. If you have whatsoever suggestions or want to add some corrections to my blog, please get out them in the comments.

I will discuss 3 possible methods of filtering large lists in SharePoint Online.

Method one – Query all items and filter in retention
Method 2 – Virtually used REST & CSOM filter method
Method 3 – Filter with RenderListDataAsStream
Summary

Method 1 – Query all items and filter in memory

For both CSOM and REST at that place are options to query all items. Which is of course e'er an option if you are sure that there volition exist enough time to do this.

CSOM

By using the 'RowLimit' belongings in a CAML Query nosotros tin can limit our results. This choice makes use of a 'paging' principle which is adjusting the ListItemCollectionPosition to return the next fix of 5000 items until nosotros reach the 'end' of the list.

When querying a document library, make sure you utilise the Scope='RecursiveAll' aspect in the View element. This way yous will query all folders, subfolders and items that are nested in your document library. This aspect has no affect on a normal list without folders.

E'er brand use of the ViewFields element to limit your return values. This will significantly improve query performance.

var list = ctx.Web.Lists.GetByTitle("listName");  CamlQuery camlQuery = new CamlQuery(); camlQuery.ViewXml = @"<View Scope='RecursiveAll'> <ViewFields><FieldRef Proper name='ID'/><FieldRef Proper noun='Title'/></ViewFields>  <RowLimit>5000</RowLimit> </View>";  List<ListItem> allListItems = new List<ListItem>(); practise {     ListItemCollection listItemCollection = list.GetItems(camlQuery);     ctx.Load(listItemCollection);     ctx.ExecuteQuery();      allListItems.AddRange(listItemCollection);      camlQuery.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;  } while (camlQuery.ListItemCollectionPosition != null);  //Filter your data with a standard C# linq query var filteredItems = allListItems.Where(particular => item.FieldValues["Title"].ToString().Contains("Examination")).ToList();

REST

We can do the same with Balance using the $top query parameter in our request URI. We'll get all items first and and then do the filtering in retentivity.

One time you add $top=5000&paged=true to your Balance request. Yous'll get the kickoff 5000 items with a 'link' element that has an href aspect. This attribute contains the URL to get the side by side folio of results.

There is a very simple style to implement this cheers to the PnPjs library. Click here to see a sample on how yous could implement this pick with just i line of lawmaking using the getAll() function.

Make sure you ascertain your $select parameter when doing this… there is a huge performance comeback and is nearly necessary for such large queries.

Looking at the browsers network inspector, we'll see these kind of requests passing by when using the getAll() function of PnPjs.

After completing this getAll() part we tin can of course only filter in retentiveness, merely as nosotros did in C#. No limitations to column types for filtering. But might be a slow solution again if you are talking about a list with for example 50000+ items.

Method 1 – Pros

Any filtering/ordering later on processing can be hands washed in memory, no matter which field types, values, …
You should never query the list more than than once, because we already have all list information in memory

Method 1 – Cons

Could be likewise slow for your use instance (query took 2 – 5 seconds / 5000 items, depending on the defined view fields)
Throttling might even boot in with very large lists. This volition force you to build a throttling check, which will consequence in even more delay for your last query consequence.

Method 2 – About used REST & CSOM filter method

I judge the methods below in CSOM and Balance API are the most used methods, but this will point out that they are clearly non the 'best' methods in all scenarios. Especially non for big lists.

CSOM

We have a lot of unlike ways of filtering our data with the CAML Query. Using all the operators bachelor in CAML, we can do a lot of stuff… Contains, In, Eq, Geq, Leq, …

Things might get pretty frustrating in one case we are using these on big lists. In one case a list reaches 5000+ items it volition always return the post-obit error when doing any CAML query. I'll but throw in an example hither which is not working for me at the moment.

var list = ctx.Web.Lists.GetByTitle("Big data list");  var camlQuery = new CamlQuery();  camlQuery.ViewXml = @"<View>     <ViewFields><FieldRef Proper noun='ID'/><FieldRef Name='Championship'/></ViewFields>     <Query><Where><Contains><FieldRef Name='Championship'/><Value Type='Text'>Testtttt</Value></Contains></Where></Query>     <RowLimit>5000</RowLimit>     </View>";  var items = listing.GetItems(camlQuery);  ctx.Load(items); ctx.ExecuteQuery();

Hither we run into our favourite exception once again… Microsoft.SharePoint.Client.ServerException: 'The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.'

And then why does this still happen now, even if we have set the RowLimit to 5000? It did piece of work in method 1 for the CSOM CAML Query.

After applying a filter (Where element) in our CAML query, it will look at this filter beginning before applying the RowLimit. Which is causing our mistake here, because our list does comprise more than 5000 items. So how could nosotros ready this?

Indexing

We could apply an alphabetize to the field(due south) which we are trying to filter. In case you lot don't know how to do this, check the ii screenshots beneath where you tin find the indexed fields of a listing in the list settings page.

Now you've entered the folio where all the indexed columns of your list are listed. Notice that there is a maximum of twenty indexes / list.

After applying an alphabetize to the 'Title' field which I was filtering on in my sample, I did get the result I was looking for. My query ran successfully now.

Annotation: Only the post-obit column types can exist indexed.

Unmarried line of text
Date
Choice (but unmarried value)
Lookup (only single value)
Taxonomy (only single value)
Person / Group (only single value)

Any other column types than the ones mentioned above (most of the time these are columns which can contain a lot of data) cannot be indexed at this fourth dimension.

We are not finished yet with this method, considering there might still raise an mistake, even when indexing is applied.

If there are notwithstanding more than 5000 results after applying your start filter in your Where element. It volition keep crashing on the ' list view threshold ' mistake. Even if our filtered column is properly indexed. So there is no fashion to filter if you are non a 100% certain that your kickoff filter is non going to limit your consequence set to 5000 or less items. We'll accept a solution for this issue in method iii.

REST API

We can do the same as above in Balance with or without the use of a CAML query.

Every SharePoint developer probably knows the {sitecollectionURL}/_api/spider web/lists/GetByTitle('listname')/Items endpoint

We can easily leverage this 1 through PnPjs also. You tin check some samples hither on the official documentation, explaining how to filter in the REST API with PnPjs.

import { sp } from "@pnp/sp/presets/all";   consign default class TestSPRestCall extends React.Component<ITestSPRestCallProps, ITestSPRestCallState> {          constructor(props) {         super(props);                  //Hook WebPartContext to PnPjs         sp.setup({           spfxContext: this.props.context         });     }          private async getSPListItems(){                  let items = await sp.web.lists.getByTitle("Big data list").items.filter("substringof('TestValue', Title)").get();              }      }

As for limitations to large list querying we are somewhat at the aforementioned level equally in CSOM for this method. Just this one has one more limiting factor, namely the fact that information technology can not filter by CAML query. This ways that for instance filtering on a managed metadata field etc. is non possible.

Although in that location is a getItemsByCAMLQuery ('getItems' API endpoint) function in PnPjs which as well enables the exact same functionality for filtering in CAML as Option two in CSOM does. With the exact same limitations of your get-go filter parameter having to limit the result prepare to 5000 items or less, else it will throw the same listing view threshold exceeded error.

import { sp } from "@pnp/sp/presets/all";   export default grade TestSPRestCall extends React.Component<ITestSPRestCallProps, ITestSPRestCallState> {          constructor(props) {         super(props);                  //Hook WebPartContext to PnPjs         sp.setup({           spfxContext: this.props.context         });     }          individual async getSPListItems(){                  let items = wait sp.spider web.lists.getByTitle("Large information list").getItemsByCAMLQuery({           ViewXml: "<View><Query><Where><Contains><FieldRef Proper noun='Championship'/><Value Type='Text'>TestValue</Value></Contains></Where></Query></View>"         });              }      }

And then indexing is again important if you are using this one in large lists. Cheque the role above, to go more info on field indexing.

Method 2 – Pros

Directly query to the SharePoint list, and then minimal information transfer
Could return results pretty fast if the issue set of the first Where parameter is limited to 5000 results or less

Method 2 – Cons

Yous need to be 100% sure of your first Where parameter in your CAML Query OR first condition in your $filter query parameter to return 5000 results or less, else your solution volition suspension
No possibility to index specific types of columns. Then these column types cannot be filtered properly.

Method ii – Tip(due south)

Most of the time when I'chiliad able to use this method, I'yard trying to find a mode to limit my items by a date range. Y'all could alphabetize a date field (maybe the standard 'Modified' or 'Created' field) and use a filter where the engagement of your items should be in betwixt a certain appointment range of let's say the concluding 4 weeks. If y'all are sure that this scope will always return 5000 items or less, then you'll be fine with this method.
As mentioned in the scenario higher up, yous are able to employ '<And><Geq></Geq><Leq></Leq></And>' in your CAML query or '(condition1 and condition2)' in your $filter query param as long every bit you target the same indexed field in both conditions. This will count equally '1' filter param and does utilise both operators correctly in the And chemical element if this kickoff filter does limit your result set to 5000 items or less.

Method 3 – Filter with RenderListDataAsStream

In my stance this is the well-nigh flexible way to query/filter your large SharePoint lists. Only how is it unlike from the previous selection that we just covered?

This API endpoint simply removes the requirement of your first filter parameter in your Where element or in your $filter parameter to limit the outcome fix to 5000 items or less. For some reason this API is non express to the view threshold. This ways that after applying my kickoff filter (if indexed) the result might contain more 5000 results. Information technology doesn't matter for this endpoint, it will return your results just fine.

Let'south testify some samples in SPFx and CSOM. I'll include paging in the sample, so y'all know yous could also page through the records, just as SharePoint does whenever you are scrolling downwardly in modern list views.

REST

Below a sample in SPFx with the apply of PnPjs.

import { sp } from "@pnp/sp/presets/all";   consign default class TestSPRestCall extends React.Component<ITestSPRestCallProps, ITestSPRestCallState> {          constructor(props) {         super(props);                  //Hook WebPartContext to PnPjs         sp.setup({           spfxContext: this.props.context         });     }          private async getSPListItems(){                  //Title field is indexed, filter / orderBy fields should notwithstanding be indexed for this endpoint         let firstPageResult = look sp.web.lists.getByTitle("Large data list").renderListDataAsStream({           ViewXml: `<View><Query><OrderBy><FieldRef Name='ID' Ascending='Truthful'/></OrderBy>           <Where><Contains><FieldRef Proper name='Title'/><Value Type='Text'>harga</Value></Contains></Where></Query>           <ViewFields><FieldRef Proper noun='ID'/><FieldRef Name='Title'/></ViewFields>           <RowLimit Paged='TRUE'>5000</RowLimit></View>`         });                  //With Paged set to truthful in the RowLimit, we can query the next page         let nextPageResult = wait sp.spider web.lists.getByTitle("Big data listing").renderListDataAsStream({           ViewXml: `<View><Query><OrderBy><FieldRef Proper noun='ID' Ascending='True'/></OrderBy>           <Where><Contains><FieldRef Name='Title'/><Value Type='Text'>harga</Value></Contains></Where></Query>           <ViewFields><FieldRef Name='ID'/><FieldRef Proper noun='Title'/></ViewFields>           <RowLimit Paged='TRUE'>5000</RowLimit></View>`,           Paging: firstPageResult.NextHref.substring(one)         });              }      }

CSOM

public void GetSPItems(){     using (var ctx = new ClientContext("https://tenantname.sharepoint.com/sites/testbench"))     {         ctx.Credentials = new SharePointOnlineCredentials("username@tenant.onmicrosoft.com", "pass".ToSecureString());         var bigDataList = ctx.Web.Lists.GetByTitle("Large data list");                  //Render type of the RenderListDataAsStream in CSOM is ClientResult<Stream>         //So nosotros just go a JSON string back from the server, only as with the Residue API         var firstPageResult = bigDataList.RenderListDataAsStream(new RenderListDataParameters()         {             ViewXml = "<View>" +             "<Query><OrderBy><FieldRef Name='ID' Ascending='Truthful'/></OrderBy>" +              "<Where><Contains><FieldRef Proper noun='Title'/><Value Blazon='Text'>harga</Value></Contains></Where></Query>" +             "<ViewFields><FieldRef Name='ID'/><FieldRef Proper name='Title'/></ViewFields>" +             "<RowLimit Paged='True'>4999</RowLimit>" +             "</View>"         }, new RenderListDataOverrideParameters() {});         ctx.ExecuteQuery();          var result = "";         using (var reader = new StreamReader(firstPageResult.Value, Encoding.UTF8))         {             result = reader.ReadToEnd();         }                  dynamic dynamicResult = JsonConvert.DeserializeObject(outcome);                           var nextPageResult = bigDataList.RenderListDataAsStream(new RenderListDataParameters()         {             ViewXml = "<View>" +             "<Query><OrderBy><FieldRef Name='ID' Ascending='Truthful'/></OrderBy>" +              "<Where><Contains><FieldRef Name='Title'/><Value Blazon='Text'>harga</Value></Contains></Where></Query>" +             "<ViewFields><FieldRef Name='ID'/><FieldRef Name='Title'/></ViewFields>" +             "<RowLimit Paged='TRUE'>4999</RowLimit>" +             "</View>",             Paging = (dynamicResult["NextHref"].Value every bit string).Substring(i)         }, new RenderListDataOverrideParameters() { });         ctx.ExecuteQuery();                  //Do something with the results of both queries     } }     public static SecureString ToSecureString(this string plainString){     if (plainString == nothing)         return null;      SecureString secureString = new SecureString();     foreach (char c in plainString.ToCharArray())     {         secureString.AppendChar(c);     }     return secureString; }

Make certain you are using an OrderBy element whenever you use this RenderListDataAsStream endpoint. Equally a default OrderBy value, I always use ID in Ascending order. This is also what SharePoint is doing, whenever using the RenderListDataAsStream endpoint if no other Order field is specified by the user. If you desire to OrderBy any other column, this is possible, just the column should be indexed before the OrderBy will work on lists with more than 5000 items.

It's of import that nosotros also sympathize the limitations of this API endpoint, because SharePoint is using this endpoint a lot in it'southward own modern UI. Almost all listing data shown in SharePoint itself is queried through this endpoint.

There is really only one thing nevertheless missing here and that'due south the fact that information technology still needs indexing to filter / order properly in large lists. Then this means that we can not filter / order properly on multi person fields, multi lookup fields, multi taxonomy fields, … .

This might still exist a blocker for your scenario. Y'all could be creative with a Note field (multi line text field) and catechumen those multi user/taxonomy/lookup fields into JSON. Afterwards that start using the SharePoint Search API to query and filter these fields also.

But the decision is that it requires a pretty big workaround and involves some custom work to get those fields, that can't exist indexed, filtered properly.

Method 3 – Pros

The only endpoint that allows a full filtering experience (if the fields being filtered are indexed) no affair the size of the result set.
Has the all-time flexibility available now of filtering with whatsoever kind of operator using CAML Query in REST likewise as CSOM.
Does besides offer options to return more detailed information of your field values than other endpoints. Bank check this weblog post to see how it tin expose sure field data more easily.

Method 3 – Cons

Still doesn't offer a solution to filter multi value taxonomy / user / lookup fields in large lists, because nosotros are non able to alphabetize these fields.
Might be more complicated to use in CSOM, considering information technology is returning a raw JSON cord every bit a event.
Much less documentation & samples available.

Summary

I recall we can conclude that the RenderListDataAsStream (method 3) endpoint is the nearly flexible one, non simply when speaking nearly filtering capabilities. But also when it comes to returning more details for certain field types without the need of expanding.

Y'all might need to consider this if you lot really need the flexibility of this endpoint. Because in CSOM it's not always the almost 'handy' endpoint to work with as information technology returns raw JSON. Instead of a typed C# object equally you lot are used to with the normal list GetItems function.

In 1 of my next blogs I'll go into item on the standard mod list UI in SharePoint Online. This blog will go more than in depth on 2 specific points:

How they are fetching their information using the RenderListDataAsStream endpoint and how you lot could benefit from information technology to hands extend the standard list features.
The scenarios where your standard filtering in your SharePoint list might break and how we can fix them.

For case creating a ListView Control Set which displays the full amount of list items later on a user has applied some filters on a view.