Identifying duplicate items in a large list (>250000 items)

Question

I have a list with ~250,000 items in it and I know that I've a lot of duplicates which I need to identify then clear out.

I don't want to write Server Side code so looking for a imaginative solution.

"Export to Spreadsheet" and Datasheet don't work as they fall foul of List View Threshold limits.

I have written some JSOM that enumerates through all the items, but then checking if each item has duplicates is just way too slow and resource intensive.

Does anyone have any idea how I can identify duplicates (in a URL column)? If I can get the list data out to Excel then I would be sweet.

Ideas?

Answer 1

You can use the below code,

    Add-PSSnapin microsoft.sharepoint.powershell 
    $web = Get-SPWeb -Identity "<URL of Site>" 
    $list = $web.Lists["List Name"] 

    $AllDuplicates = $list.Items.GetDataTable() | Group-Object title | where {$_.count -gt 1} 
    $count = 1 
    $max = $AllDuplicates.Count 
    foreach($duplicate in $AllDuplicates) 
    { 
    $duplicate.group | Select-Object -Skip 1 | % {$list.GetItemById($_.ID).Delete()} 
    Write-Progress -PercentComplete ($count / $max * 100) -Activity "$count duplicates removed" -Status "In Progress" 
    $count++ 
    }
Remove-PsSnapin Microsoft.SharePoint.PowerShell

Note:

1. Change the URL of the site and List Name.

2. In the above code, duplicates are found using the "title" column. if you want to change it, then in the 4th line of the code, change Group-Object "list column Name".

Ref: Geekswithblogs

Hope this helps you.

Answer 2

I would lean towards a PowerShell solution, ideally on a server in the farm that is NOT highly utilized. I would use PowerShell to iterate and dump the entire list to a CSV, handle your duplications in Excel and then come out with a list of IDs to delete (the duplicates). From there, use PowerShell again and delete the items (.Delete not .Recycle).

Another option is to take a backup of the site, bring it to another environment, do the same as above, but use a non-production environment.

Fair warning, if you're going to be bulk deleting this many items, make sure your SQL databases have room on the disk, especially the system DBs (more specifically the temp DB I think). As you work through your list and delete, SQL stores it temporarily until it commits and then can flush the temp DB. This will depend on your maintenance plans in SQL.

I've done something similar to this for a customer and our bigger issue was the SQL server handling the requests. We had to spin up a whole new SQL box with loads more space, move this DB to a new DB server, and then handle it there.

Answer 3

suggestion #1: How about exporting the whole list to an SQL table using C#? I would only export two columns: ListLitemID and URL. Then you can find duplicate's ID's much faster by selecting the rows with Group By URL clause.

suggestion #2: Can you possibly create a view that groups a SharePoint list by URL column? Update: yes, you can. Then you can just show about 3000 items for each page and manually go through all 250K items. That might be about 83 pages at most. Quite a lot of work. but maybe within an hour you can clear the whole thing like that.

suggestion #3: You've mentioned that you have a ready-to-use JSOM enumeration for all 250K items. I would suggest you to get all items first in a large JavaScript array and then start the process of searching for duplicates this way it's going to be hundreds of times faster. But you need to search for the duplicates within the JavaScript collection you've already got.