We often get requests to limit list items to a section of people like HR, IT, PMO, etc. HR should see fewer items that other groups should not and vice versa. The solution for this problem appears straight forward (by breaking permissions of items and assigning them to corresponding SPGroups). However, this approach leads to serious performance issues for a large number of items and can bring entire SPFarm down!
The recommended number of unique security scopes for a SPList is 5000. When the number of unique security scopes for a list exceeds the value of the list view threshold (set by default at 5,000 list items), additional SQL Server round trips take place when the list is viewed, and this can adversely affect list view performance.
Possible workaround for this problem is to have folder level security in SPList. For the above problem statement, we will be having folders for HR, IT, PMO in SPList and HR folder will be accessed only by HR and Admin SPGroups (same approach for other folders as well)
With this change, all items under HR folder will inherit permissions from HR folder instead of having separate permissions. Below is the comparison between item level permissions and folder level permissions
List Item Count: 2000
Without using Folders –We would be having more than 2000 unique security scopes
With using Folder – We would be having 4 unique security scopes (Permissions for List itself and Permissions for 3 folders)