Powershell - Memory use when calling SPList.Items

Question

This is an expansion of the issue I've been working on here.

As discussed, my solution works fine in development against a list with 150 records to find in a list of 20,000.

It's not working as well looking for 20,000 records in a list of ten million.

$SPItems = $SPList.Items | Where { $_["Processing_Date"] -eq $SPListDate }

$SPCount = $SPItems.count.ToString()

Write-Host("${SPCount} records found");

$SPDirName + "/" + $file.Name); $file.Update();}

$SPindex = 0

$SPItems | foreach-object { 
    $file = $_.file; 
    $file.MoveTo($SPListPath + $SPDirName + "/" + $file.Name); 
    $file.Update();

    $SPindex = $SPindex + 1;
    $SPMod = $Spindex % 50;
    If ($SPMod -eq 0) {
        Write-Host("${SPindex} records processed");
    }
}

It hang on trying to run the query on the first line, grabbing a huge amount of memory over time. So I'm assuming it's trying to pull the whole dataset of the query into memory, which would be sizable.

So I tried just checking every record in the initial list with no query, and comparing each record for the test:

$SPList.Items | foreach-object { 
If( $_["Processing_Date"] -eq $SPListDate ) {
#   $file = $_.file; 
#   $file.MoveTo($SPListPath + $SPDirName + "/" + $file.Name); 
#   $file.Update();

    $SPindex = $SPindex + 1;
    $SPMod = $Spindex % 50;
    If ($SPMod -eq 0) {
        Write-Host("${SPindex} records processed");
    }
}

$SPCheck= $SPCheck + 1;
$SPMod1 = $SPCheck % 50;
If ($SPMod1 -eq 0) {
    Write-Host("${SPCheck} records processed");
}

}

But I suspect it's still drawing the whole thing into memory, as I've not seen anything kick out yet, and the memory is creeping up again.

So I think the question is, (how) can I iterate through the items of a (very large) list without having it grab a lot of memory?

Answer 1

SPList.Items fetches all items, and only does filtering after you have all items, which leads to performance and memory issues like you described.

Instead do tasks in batches and only get subset of the items using Microsoft.SharePoint.Client.CamlQuery and GetItems, so something like this might work especially if you're moving items to another list. If you're moving them within the list you need to come up with a CAML query that only returns you source items:

$web = Get-SPWeb http://yoursite.sharepoint.com
$list = $web.Lists["YourList"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 1000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.ViewFields = '<FieldRef Name="SomeField" />'
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        #MOVE IT
    }
}
while ($spQuery.ListItemCollectionPosition -ne $null)