Optimizing SharePoint Online Query Performance using PnP PowerShell

Optimizing query performance in SharePoint Online is crucial for faster data retrieval and avoiding list view threshold errors (5000+ items).
Using PnP PowerShell, you can:

Query large lists efficiently
Use indexed columns to avoid throttling
Apply CAML queries for targeted results
Implement pagination for batch processing

Step 1: Connect to SharePoint Online

Before running queries, authenticate securely:

$siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
Connect-PnPOnline -Url $siteUrl -Interactive

✔ Ensures secure connection to SharePoint.

Step 2: Use Indexed Columns for Faster Queries

Check existing indexed columns in a list:

$listName = "LargeDocuments"

$indexedColumns = Get-PnPList -Identity $listName | Select -ExpandProperty Fields | Where-Object { $_.Indexed }

Write-Host " Indexed Columns in $listName:"
$indexedColumns | Select Title, InternalName, Indexed

✔ Indexed columns prevent threshold errors.

If an important column is not indexed, add an index:

$columnName = "Category"

Set-PnPField -List $listName -Identity $columnName -Values @{Indexed=$true}

Write-Host " Indexed column added: $columnName"

✔ Helps optimize queries.

Step 3: Use CAML Query for Precise Filtering

Instead of fetching all items (slow ), use CAML Query (fast ) to filter data efficiently.

Retrieve Active Documents from a large list:

$xmlQuery = @"
<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='Status' />
                <Value Type='Text'>Active</Value>
            </Eq>
        </Where>
    </Query>
</View>
"@

$items = Get-PnPListItem -List $listName -Query $xmlQuery

Write-Host " Retrieved $($items.Count) Active Documents"

✔ Queries only required items, improving speed.

Step 4: Use Pagination for Large Lists

For lists with 10,000+ items, process them in batches:

$pageSize = 2000
$items = Get-PnPListItem -List $listName -PageSize $pageSize -Fields "Title","Status"

Write-Host " Retrieved $($items.Count) items in batches of $pageSize"

✔ Prevents list view threshold errors.

Step 5: Retrieve Only Required Columns

Instead of loading all fields (slow ), retrieve specific columns (fast ):

$fields = @("ID", "Title", "Status")

$filteredItems = Get-PnPListItem -List $listName -Fields $fields -PageSize 1000

Write-Host " Retrieved $($filteredItems.Count) items with selected fields"

✔ Reduces query execution time.

Step 6: Optimize Sorting and Indexing

Sorting a large list without an index = Slow
Sorting a pre-indexed column = Fast

$sortedQuery = @"
<View>
    <Query>
        <OrderBy>
            <FieldRef Name='Modified' Ascending='FALSE' />
        </OrderBy>
    </Query>
</View>
"@

$sortedItems = Get-PnPListItem -List $listName -Query $sortedQuery

Write-Host " Retrieved $($sortedItems.Count) items sorted by Modified Date"

✔ Uses indexed sorting for efficiency.

Step 7: Automate Query Performance Optimization

Schedule a PowerShell script to check and optimize queries daily:

$scriptPath = "C:\Scripts\OptimizeQueries.ps1"

$taskAction = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-File $scriptPath"
$taskTrigger = New-ScheduledTaskTrigger -Daily -At 2AM
Register-ScheduledTask -TaskName "Optimize SharePoint Queries" -Action $taskAction -Trigger $taskTrigger -RunLevel Highest

✔ Ensures automated query optimization.