large-sharepoint-rest-calls.jpg

Dealing with Insanely Large SharePoint REST calls

Bo is a Principal Consultant for ThreeWill. He has 18 years of full lifecycle software development experience.

Large SharePoint REST Calls

If you have dealt with SharePoint list queries that have any sort of complexity you know they can get rather large.  Lately, the culprit for my large REST calls has been due to that fact SharePoint is still on OData version 3 which does not support the “in” logic operator.  If/when SharePoint finally supports version 4.01 life may get easier.  A colleague provided me this link that shows support for “in” is coming.  Anyway, I digress.

There are times when REST can become a problem or more specifically, querying SharePoint using REST.

A Couple of Examples

For example, here are two problematic scenarios I run into often.

  1. I needed to create a query that is going to result in 12 or more lookups that need to be expanded. This can be due to a combination of your select or filter.
  2. I needed to create a confoundingly dynamic and complex filter. By its nature, it could become an extremely large query that can end up exceeding what a browser supports.

For issue #1, switching over to a CAML based approach isn’t really an alternative since the lookup limitations still apply there, honestly, there is no getting around it since it’s a part of the throttling necessary to keep a SharePoint environment healthy.  To address issue #2, I did a fair bit of prototyping and testing on switching to a CAML based approach, but I NEVER liked it.  I actually started using the PnP method list.getItemsByCAMLQuery initially but after running into an issue with lookup field and stumbling across this thread I switched to list.renderListDataAsStream which was better but the format of the results was so different than a list.getItems I was still unhappy.

Enter the $batch

I cannot say enough about how batching helped me and PnP makes it SUPER easy to use.  At the most fundamental level batching allows you to POST several REST calls as one unit of work to the server.  With the tools provided through PnP, this becomes dead simple.  Just create a batch, add your requests and execute the batch.  Like the saying goes, I was able to kill two birds with one stone.  I was able to create multiple REST queries that could be “insanely large” and at the same time use the best option available to deal with the 12 lookup constraints.  Okay, so no more talking, I really must show some code and the resulting batch for it to feel like I’ve done my job here.

There is A LOT going on in the method below but I will try to call out as much of what matters as possible.

  • As the method name implies, we are using paging so that comes with its own set of considerations. Without batching it was pretty straightforward with PnP as they provide a getNext() method.  However, I could not find a way for that to work within a batch, so I instead hijacked access to the private member ‘nextUrl’ to get the next page and created an Items SharePointQueryable so I could add it to a batch (Lines 14-36)
  • The second thing you’ll probably notice is that I make 3 distinct getPaged() calls. This is of course because every time you exceed the 12 lookup limit you need another query with different lookups to expand.  In my case, some of the potential filter options can put me over the limit so it required 3 requests to properly share the lookup limit love.
  • You may be wondering about my main*,secondary* and tertiary* select columns and expand columns (Lines 43-44, 55-56, 67-68). I’m not showing those in the code snipped but they are just arrays of field names.  You’ll get to see them in the example of a submitted batch
  • As I suggested batching is really easy with PnP.  Lines 10, 48, 60, 72 and 79-80 are all the batch stuff I layered on top of previously non-batched GET requests.
  • Finally, the filterText (lines 45,57 and 69) which is the main reason I needed to batch anyways right. Well, I seriously didn’t want to blind you by showing how that is built, however just for some context.  The filter that comes into this method is driven from a UI and code process that can result in filtering on 15 different attributes including the current user.  Of these 15 different attributes, 10 of them are lookups to self-referencing lists that can represent a hierarchy like a Geography (Country > State > City).  Therefore, a query for a country must find all State and City lookups, not just the specific country.  This is the stuff that can result in an exceedingly large $filter parameter.
async getPagedItems(pageSize: number = 20,
    filter: InformationProductsFilter,
    previousPagedItems: PagedItems<InformationProduct> = null):
    Promise<PagedItems<InformationProduct>> {

    let pagedItems = new PagedItems<InformationProduct>();
    let currentUser = await this.getCurrentUser();

    try {
        let batch = sp.createBatch();

        if (previousPagedItems !== null 
            && previousPagedItems.mainPagedItemCollection.hasNext) {
            const mainPagedItems = new Items(
                previousPagedItems.mainPagedItemCollection['nextUrl'], null);
            mainPagedItems.inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.mainPagedItemCollection = r;
                });

            const secondaryPagedItems = new Items(
                previousPagedItems.secondaryPagedItemCollection['nextUrl'], null);
            secondaryPagedItems.inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.secondaryPagedItemCollection = r;
                });

            const tertiaryPagedItems = new Items(
                previousPagedItems.tertiaryPagedItemCollection['nextUrl'], null);
            tertiaryPagedItems.inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.tertiaryPagedItemCollection = r;
                });
        }
        else {
            let filterText = await this.getFilterText(filter, true, currentUser);
            let orderBy = 'TypeLookup/SortOrder, Title';

            sp.web.lists.getByTitle(this.listTitle).items
                .select(this.mainSelectColumns.join(','))
                .expand(this.mainExpandColumns.join(','))
                .filter(filterText)
                .top(pageSize)
                .orderBy(orderBy)
                .inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.mainPagedItemCollection = r;
                });

            sp.web.lists.getByTitle(this.listTitle).items
                .select(this.secondarySelectColumns.join(','))
                .expand(this.secondaryExpandColumns.join(','))
                .filter(filterText)
                .top(pageSize)
                .orderBy(orderBy)
                .inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.secondaryPagedItemCollection = r;
                });

            sp.web.lists.getByTitle(this.listTitle).items
                .select(this.tertiarySelectColumns.join(','))
                .expand(this.tertiaryExpandColumns.join(','))
                .filter(filterText)
                .top(pageSize)
                .orderBy(orderBy)
                .inBatch(batch)
                .getPaged()
                .then(r => {
                    pagedItems.tertiaryPagedItemCollection = r;
                });
        }

        await batch.execute()
            .catch(e => { super.logPnpError(e); return false; });

        for (let mainItem of pagedItems.mainPagedItemCollection.results) {
            let secondaryItem = pagedItems.secondaryPagedItemCollection
                .results.find(i => i.ID === mainItem.ID);
            let tertiaryItem = pagedItems.tertiaryPagedItemCollection
                .results.find(i => i.ID === mainItem.ID);
            let mergedItem = { ...mainItem, ...secondaryItem, ...tertiaryItem };
            pagedItems.items.push(
                this.mapItemToInformationProduct(mergedItem, currentUser));
        }
    }
    catch (e) {

    }
    return pagedItems;
}

Ultimately, all of the above code becomes part of a POST as the request payload. As you can see from below, it is not pretty. You’ll need to scroll REALLY far to the right to take it all in.  The first 2 GET requests are each over 3100 characters long. For the most part, it is due to those self-referencing lists where I end up with a large “or” condition to find items in a hierarchy if they are filtering on a level 1, I need to find level 2s and 3s and so on.  The number of lookups in the list also doesn’t help either, each select and expand statement can really eat up a lot of the overall length of a query.

--batch_34515e24-f947-44bb-ad6d-cb99b68aa1b5
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://tenant.sharepoint.com/sites/dev/_api/web/lists/getByTitle('Information Products')/items?$select=ID,Title,Description,IsDeleted,CertificationNotes,CertificationLookup/ID,CertificationLookup/Title,CoreActivityTypeLookup/ID,CoreActivityTypeLookup/Title,OwnershipLookup/ID,OwnershipLookup/Title,ProcessLookup/ID,ProcessLookup/Title,FundingArrangementsMultiLookup/ID,FundingArrangementsMultiLookup/Title,MedicalTypesMultiLookup/ID,MedicalTypesMultiLookup/Title,LinesOfBusinessMultiLookup/ID,LinesOfBusinessMultiLookup/Title,ProductCategoriesMultiLookup/ID,ProductCategoriesMultiLookup/Title,StateLookup/ID,StateLookup/Title,TypeLookup/ID,TypeLookup/Title,TypeLookup/Color,Created,Modified,Author/ID,Author/Name,Author/Title,Editor/ID,Editor/Name,Editor/Title&$expand=CertificationLookup,CoreActivityTypeLookup,OwnershipLookup,ProcessLookup,FundingArrangementsMultiLookup,MedicalTypesMultiLookup,LinesOfBusinessMultiLookup,ProductCategoriesMultiLookup,StateLookup,TypeLookup,Author,Editor&$filter=IsDeleted ne 1 and (StateLookup/ID eq 3 or StateLookup/ID eq 4) and TypeLookup/ID eq 1 and CertificationLookup/ID eq 4 and CoreActivityTypeLookup/ID eq 10 and ProcessLookup/ID eq 6 and ( SegmentsMultiLookup/ID eq 1 or SegmentsMultiLookup/ID eq 35 ) and ( ProductCategoriesMultiLookup/ID eq 1 or ProductCategoriesMultiLookup/ID eq 8 or ProductCategoriesMultiLookup/ID eq 11 or ProductCategoriesMultiLookup/ID eq 12 or ProductCategoriesMultiLookup/ID eq 10 or ProductCategoriesMultiLookup/ID eq 9 ) and ( CFOMultiLookup/ID eq 2 or CFOMultiLookup/ID eq 26 or CFOMultiLookup/ID eq 122 or CFOMultiLookup/ID eq 123 or CFOMultiLookup/ID eq 124 or CFOMultiLookup/ID eq 27 or CFOMultiLookup/ID eq 125 or CFOMultiLookup/ID eq 126 or CFOMultiLookup/ID eq 127 or CFOMultiLookup/ID eq 28 or CFOMultiLookup/ID eq 128 or CFOMultiLookup/ID eq 129 or CFOMultiLookup/ID eq 130 or CFOMultiLookup/ID eq 29 or CFOMultiLookup/ID eq 131 or CFOMultiLookup/ID eq 132 or CFOMultiLookup/ID eq 133 or CFOMultiLookup/ID eq 134 or CFOMultiLookup/ID eq 30 or CFOMultiLookup/ID eq 135 or CFOMultiLookup/ID eq 136 or CFOMultiLookup/ID eq 137 or CFOMultiLookup/ID eq 31 or CFOMultiLookup/ID eq 138 or CFOMultiLookup/ID eq 32 or CFOMultiLookup/ID eq 139 or CFOMultiLookup/ID eq 140 or CFOMultiLookup/ID eq 141 or CFOMultiLookup/ID eq 33 or CFOMultiLookup/ID eq 142 or CFOMultiLookup/ID eq 34 or CFOMultiLookup/ID eq 143 or CFOMultiLookup/ID eq 144 or CFOMultiLookup/ID eq 145 or CFOMultiLookup/ID eq 146 or CFOMultiLookup/ID eq 147 or CFOMultiLookup/ID eq 148 or CFOMultiLookup/ID eq 35 or CFOMultiLookup/ID eq 149 or CFOMultiLookup/ID eq 150 or CFOMultiLookup/ID eq 36 or CFOMultiLookup/ID eq 151 or CFOMultiLookup/ID eq 37 or CFOMultiLookup/ID eq 152 or CFOMultiLookup/ID eq 38 or CFOMultiLookup/ID eq 153 or CFOMultiLookup/ID eq 154 or CFOMultiLookup/ID eq 155 or CFOMultiLookup/ID eq 156 or CFOMultiLookup/ID eq 39 or CFOMultiLookup/ID eq 157 or CFOMultiLookup/ID eq 40 or CFOMultiLookup/ID eq 158 or CFOMultiLookup/ID eq 159 or CFOMultiLookup/ID eq 160 or CFOMultiLookup/ID eq 161 or CFOMultiLookup/ID eq 41 or CFOMultiLookup/ID eq 162 )&$top=25&$orderby=TypeLookup/SortOrder, Title asc HTTP/1.1
accept: application/json;odata=verbose
content-type: application/json;odata=verbose;charset=utf-8
x-clientservice-clienttag: PnPCoreJS:@pnp-1.1.1

--batch_34515e24-f947-44bb-ad6d-cb99b68aa1b5
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://tenant.sharepoint.com/sites/dev/_api/web/lists/getByTitle('Information Products')/items?$select=ID,BusinessActivitiesMultiLookup/ID,BusinessActivitiesMultiLookup/Title,CentersOfExcellenceMultiLookup/ID,CentersOfExcellenceMultiLookup/Title,ConsumptionLayersMultiLookup/ID,ConsumptionLayersMultiLookup/Title,DataPipelinesMultiLookup/ID,DataPipelinesMultiLookup/Title,LocalMarketMultiLookup/ID,LocalMarketMultiLookup/Title,CFOMultiLookup/ID,CFOMultiLookup/Title,SegmentsMultiLookup/ID,SegmentsMultiLookup/Title,SourceSystemsMultiLookup/ID,SourceSystemsMultiLookup/Title,BusinessNeedsMultiLookup/ID,BusinessNeedsMultiLookup/Title,KeyContactLookup/ID,KeyContactLookup/Name,KeyContactLookup/Title,ManagersMultiLookup/ID,ManagersMultiLookup/Name,ManagersMultiLookup/Title&$expand=BusinessActivitiesMultiLookup,CentersOfExcellenceMultiLookup,ConsumptionLayersMultiLookup,DataPipelinesMultiLookup,LocalMarketMultiLookup,CFOMultiLookup,SegmentsMultiLookup,SourceSystemsMultiLookup,BusinessNeedsMultiLookup,KeyContactLookup,ManagersMultiLookup&$filter=IsDeleted ne 1 and (StateLookup/ID eq 3 or StateLookup/ID eq 4) and TypeLookup/ID eq 1 and CertificationLookup/ID eq 4 and CoreActivityTypeLookup/ID eq 10 and ProcessLookup/ID eq 6 and ( SegmentsMultiLookup/ID eq 1 or SegmentsMultiLookup/ID eq 35 ) and ( ProductCategoriesMultiLookup/ID eq 1 or ProductCategoriesMultiLookup/ID eq 8 or ProductCategoriesMultiLookup/ID eq 11 or ProductCategoriesMultiLookup/ID eq 12 or ProductCategoriesMultiLookup/ID eq 10 or ProductCategoriesMultiLookup/ID eq 9 ) and ( CFOMultiLookup/ID eq 2 or CFOMultiLookup/ID eq 26 or CFOMultiLookup/ID eq 122 or CFOMultiLookup/ID eq 123 or CFOMultiLookup/ID eq 124 or CFOMultiLookup/ID eq 27 or CFOMultiLookup/ID eq 125 or CFOMultiLookup/ID eq 126 or CFOMultiLookup/ID eq 127 or CFOMultiLookup/ID eq 28 or CFOMultiLookup/ID eq 128 or CFOMultiLookup/ID eq 129 or CFOMultiLookup/ID eq 130 or CFOMultiLookup/ID eq 29 or CFOMultiLookup/ID eq 131 or CFOMultiLookup/ID eq 132 or CFOMultiLookup/ID eq 133 or CFOMultiLookup/ID eq 134 or CFOMultiLookup/ID eq 30 or CFOMultiLookup/ID eq 135 or CFOMultiLookup/ID eq 136 or CFOMultiLookup/ID eq 137 or CFOMultiLookup/ID eq 31 or CFOMultiLookup/ID eq 138 or CFOMultiLookup/ID eq 32 or CFOMultiLookup/ID eq 139 or CFOMultiLookup/ID eq 140 or CFOMultiLookup/ID eq 141 or CFOMultiLookup/ID eq 33 or CFOMultiLookup/ID eq 142 or CFOMultiLookup/ID eq 34 or CFOMultiLookup/ID eq 143 or CFOMultiLookup/ID eq 144 or CFOMultiLookup/ID eq 145 or CFOMultiLookup/ID eq 146 or CFOMultiLookup/ID eq 147 or CFOMultiLookup/ID eq 148 or CFOMultiLookup/ID eq 35 or CFOMultiLookup/ID eq 149 or CFOMultiLookup/ID eq 150 or CFOMultiLookup/ID eq 36 or CFOMultiLookup/ID eq 151 or CFOMultiLookup/ID eq 37 or CFOMultiLookup/ID eq 152 or CFOMultiLookup/ID eq 38 or CFOMultiLookup/ID eq 153 or CFOMultiLookup/ID eq 154 or CFOMultiLookup/ID eq 155 or CFOMultiLookup/ID eq 156 or CFOMultiLookup/ID eq 39 or CFOMultiLookup/ID eq 157 or CFOMultiLookup/ID eq 40 or CFOMultiLookup/ID eq 158 or CFOMultiLookup/ID eq 159 or CFOMultiLookup/ID eq 160 or CFOMultiLookup/ID eq 161 or CFOMultiLookup/ID eq 41 or CFOMultiLookup/ID eq 162 )&$top=25&$orderby=TypeLookup/SortOrder, Title asc HTTP/1.1
accept: application/json;odata=verbose
content-type: application/json;odata=verbose;charset=utf-8
x-clientservice-clienttag: PnPCoreJS:@pnp-1.1.1

--batch_34515e24-f947-44bb-ad6d-cb99b68aa1b5
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://tenant.sharepoint.com/sites/dev/_api/web/lists/getByTitle('Information Products')/items?$select=ID,ApproverLookup/ID,ApproverLookup/Name,ApproverLookup/Title&$expand=ApproverLookup&$filter=IsDeleted ne 1 and (StateLookup/ID eq 3 or StateLookup/ID eq 4) and TypeLookup/ID eq 1 and CertificationLookup/ID eq 4 and CoreActivityTypeLookup/ID eq 10 and ProcessLookup/ID eq 6 and ( SegmentsMultiLookup/ID eq 1 or SegmentsMultiLookup/ID eq 35 ) and ( ProductCategoriesMultiLookup/ID eq 1 or ProductCategoriesMultiLookup/ID eq 8 or ProductCategoriesMultiLookup/ID eq 11 or ProductCategoriesMultiLookup/ID eq 12 or ProductCategoriesMultiLookup/ID eq 10 or ProductCategoriesMultiLookup/ID eq 9 ) and ( CFOMultiLookup/ID eq 2 or CFOMultiLookup/ID eq 26 or CFOMultiLookup/ID eq 122 or CFOMultiLookup/ID eq 123 or CFOMultiLookup/ID eq 124 or CFOMultiLookup/ID eq 27 or CFOMultiLookup/ID eq 125 or CFOMultiLookup/ID eq 126 or CFOMultiLookup/ID eq 127 or CFOMultiLookup/ID eq 28 or CFOMultiLookup/ID eq 128 or CFOMultiLookup/ID eq 129 or CFOMultiLookup/ID eq 130 or CFOMultiLookup/ID eq 29 or CFOMultiLookup/ID eq 131 or CFOMultiLookup/ID eq 132 or CFOMultiLookup/ID eq 133 or CFOMultiLookup/ID eq 134 or CFOMultiLookup/ID eq 30 or CFOMultiLookup/ID eq 135 or CFOMultiLookup/ID eq 136 or CFOMultiLookup/ID eq 137 or CFOMultiLookup/ID eq 31 or CFOMultiLookup/ID eq 138 or CFOMultiLookup/ID eq 32 or CFOMultiLookup/ID eq 139 or CFOMultiLookup/ID eq 140 or CFOMultiLookup/ID eq 141 or CFOMultiLookup/ID eq 33 or CFOMultiLookup/ID eq 142 or CFOMultiLookup/ID eq 34 or CFOMultiLookup/ID eq 143 or CFOMultiLookup/ID eq 144 or CFOMultiLookup/ID eq 145 or CFOMultiLookup/ID eq 146 or CFOMultiLookup/ID eq 147 or CFOMultiLookup/ID eq 148 or CFOMultiLookup/ID eq 35 or CFOMultiLookup/ID eq 149 or CFOMultiLookup/ID eq 150 or CFOMultiLookup/ID eq 36 or CFOMultiLookup/ID eq 151 or CFOMultiLookup/ID eq 37 or CFOMultiLookup/ID eq 152 or CFOMultiLookup/ID eq 38 or CFOMultiLookup/ID eq 153 or CFOMultiLookup/ID eq 154 or CFOMultiLookup/ID eq 155 or CFOMultiLookup/ID eq 156 or CFOMultiLookup/ID eq 39 or CFOMultiLookup/ID eq 157 or CFOMultiLookup/ID eq 40 or CFOMultiLookup/ID eq 158 or CFOMultiLookup/ID eq 159 or CFOMultiLookup/ID eq 160 or CFOMultiLookup/ID eq 161 or CFOMultiLookup/ID eq 41 or CFOMultiLookup/ID eq 162 )&$top=25&$orderby=TypeLookup/SortOrder, Title asc HTTP/1.1
accept: application/json;odata=verbose
content-type: application/json;odata=verbose;charset=utf-8
x-clientservice-clienttag: PnPCoreJS:@pnp-1.1.1

--batch_34515e24-f947-44bb-ad6d-cb99b68aa1b5--

A Final Note

One final lesson/note before I close.  In making the switch to batching my request I learned that the case sensitivity of your URLs matters.  If you created the site collection as /sites/DEV, then sites/dev will fail.  To that end, you’ll need to be sure to build your URLs based on the case sensitivity that you get from some web-based context.

 


Related Posts

Bo GeorgeDealing with Insanely Large SharePoint REST calls