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); 

    $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?

1 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 

    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
        #MOVE IT
while ($spQuery.ListItemCollectionPosition -ne $null)
  • As I feared. I'm not moving to other lists, I'm moving to folders in the current list - the details of the project are elaborated in the original post. Also as I mentioned I'm not moving every record, but only selected records that meet a search criteria. But this looks like it'll do the job - it'll all depend on how well it all runs against the monstrous size of the links. Thanks Commented Oct 21, 2016 at 14:33
  • In CAML query you can only get fixed (small) number of items from source folder and move those, then repeat. Basically this means you can move the WHILE loop to higher level, and always make new query + move operation until you get no results from the source location, meaning you've moved everything.
    – Jussi Palo
    Commented Oct 24, 2016 at 9:17
  • This seems to be the way to go, but I'm having trouble accessing the fields of the list for some reason. I can get the query above to return info, as it's effectively the equivalent of "where 1=1",bit I can't seem to reference any of the fields in any of the list items. It's almost certainly a syntax issue. If I don't make any headway I'l post some sample code. Commented Oct 25, 2016 at 17:10
  • You need to include $spQuery.ViewFields to get field values.
    – Jussi Palo
    Commented Oct 26, 2016 at 12:33
  • Yep, that was it - it's flying now. Cool beans. Commented Oct 27, 2016 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.