Today, My Client has a new requirement to delete certain records from a SharePoint Custom List which contains 80+ k of items. First thing came into my head is to run a powershell script to delete the item based on a certain filter condition. (In my case, it is to delete item with column “Application” equals to “Event”)

I drafted this powershell script and ran with Administrator rights.

[sourcecode language=”powershell”]

$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$items = $list.Items | Where {$_["Application" ] -match "Event"}

foreach($item in $items)

{

Write-Host "Deleting " $item.ID

$item.Delete()

}

[/sourcecode]

Well, with the huge number of list item in the list, one SPListItem took me 40+ second to delete! That’s ridiculous. And what if i have 10k record to be deleted? It would take up 400k seconds (111 hours) to complete!

After few minutes of googling, some suggested to use CAML Query and limit the number of query by using RowLimit = 1000

This doesn’t really help as i really want to delete them ALL at once!

The better way to delete the items is to change the way you query the data especially when comes to retrieving the List item. Here it goes

[sourcecode language=”powershell”]

$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$query = New-Object Microsoft.SharePoint.SPQuery

$query.RowLimit = 999999

$query.Query = "<Where><Eq><FieldRef Name=’Application’/><Value Type=’Text’>Event</Value></Eq></Where>"

$items = $list.GetItems($query)

foreach($item in $items){

Write-Host "Deleting" $item.ID

$list.GetItemById($item.ID).Delete()

}

[/sourcecode]

Woosh!!

the script completed in 30+ mins!