Summary

I have written Site Provisioning Engine in Azure Function using PnP.PowerShell. There is request list name “Master Site Inventory”. This list grew in size over time. The provisioning engine was working fine but it stopped working with above error.

After doing the analysis the error was clearly for the request list size grew over 5,000 items.

The issues was using the CAML query to get items from the list using Get-PnPListItem. I used the CAML Query so I can only get items where RequestStatus is equal to Approved.

$strQuery = "
  <View>
    <Query>
      <Where>
        <Eq>
          <FieldRef Name='RequestStatus' />
          <Value Type='Text'>"Approved"</Value>
        </Eq>
      </Where>
    </Query>
    <ViewFields>
      <FieldRef Name='RequestStatus' />
      <FieldRef Name='Title' />
    </ViewFields>
  </View>"

$HashArguments = @{
  Url            = $webUrl
  ClientId         = $env:GRAPH_APP_ID
  CertificateBase64Encoded = $certificateBase64Encode
  Tenant           = $("{0}.onmicrosoft.com" -f  $tenant)
}

# use splatting to pass parameter.
$conn = Connect-PnPOnline @HashArguments  -ReturnConnection

$HashArguments = @{
  Connection    = $conn
  List      = "MasterSiteInventory” 
  # Query       = $strQuery <---- This is an issue, NO CAML QUERY
}

[array]$items = Get-PnPListItem @HashArguments  -PageSize 1000 | `
		%{New-Object psobject -Property `
		@{ 
		  Id           = $_.Id; 
		  RequestStatus    = $_["RequestStatus"];
		  Title        = $_["Title"]; 
		 }} | `
         	 select ID, 
	        	RequestStatus,
			Title,
$FilteredItems  = $items | 
                  Where-Object {$_.RequestStatus -contains 'Approved'}  | 
                  Sort-Object -Property Created

How was it resolved?

Basically, the CAML query for the large list such as over 5,000 items the Get-PnPListItem will fail with the above error.

The resolution is, user the PageSize of some number and get “ALL” items from the list. After getting all items, apply filter to the returned items.

Conclusion

There are number information on the web for this error. I want to put this resolution just too the point without any noise. Basically, stop using CAML query to get items for large list. Get everything and filter it.

NOTE: Since you are getting all items from the list, make sure you are getting only required columns in my case I just need Id and RequestStatus Column. Once I filter the items from all items I then get rest of the field for the filtered array. This will help in the performance.