I recently had a client that is using SharePoint 2007 as a portal to distribute content to a large audience. Not only is the audience large, but it is diverse. Each user will see different content in the portal based on the roles that they serve.
The problem is… there are lots of roles. Thousands of them. Now everybody knows that SharePoint starts to croak at about 2000 items in a list. This also holds true for the number of unique Security Principals in the site. This obviously causes an issue when you want to break permissions on lots of items in a list, because you will end up with way too many Security Principals on the site.
Luckily, the 2000 item limit can be easily broken if you are utilizing custom interfaces to lists. The limits are associated with the default views that come from Microsoft. I learned all of this during a presentation by Eric Shupps, a SharePoint MVP in the Dallas/Ft. Worth area. Most of the content of the presentation is found on his blog here: http://www.binarywave.com/blogs/eshupps/Lists/Posts/Post.aspx?ID=188.
In my situation, I needed to know how SharePoint would handle large lists with unique permissions added into the mix. Specifically, at what point and why does SharePoint break down with a large number of unique permissions in a list.
To find out, I created a console app to measure the time it took to do the following:
- Add a new item to the list
- Assign unique permissions to the list
- Query the first page of 100 items from the list using SPQuery
In order to test this, I created a new web app with a blank site collection and a new content database. I connected it to custom membership and role providers that allow me to have thousands of users. I also used a variation of the technique talked about here: http://sladescross.wordpress.com/2010/03/19/item-level-permissions-performance-problem/. This allowed me to reduce the amount of time it takes to break permissions.
In my first test, I used a pool of 2,000 users and added 8,000 items to a simple list. Each item in the list had unique permissions assigned to it. The first 2,000 items all had a new user who did not have any permissions on the site, so after that point, there were no new users being added to the site. Here is what happened:
You will notice a couple of things. Once I reached 2,000 items, the time to break permissions significantly dropped and flat lined. This was also the time where I stopped adding new users to the site, and started assigning items to users who already had permissions on the site. The other thing you will notice is that the time to query the list jumped at 1000 items, and began increasing linearly.
In my next test, I wanted to see how the time to query the list was affected by the number of unique users on the site, if at all. To do this I ran it for the following 1 user, 1,000 users, 2,000 users, and 12,000 users.
From this, you can see that the total number of users on the site does not have a significant impact on the time to query the list. It is strictly about the number of items that have unique permissions, not the total number of different users on the site. You can see the time to query the list when permissions are not broken as a reference point.
If you remember from the first chart, the time to query the list significantly jumped at 1,000 items. I was curious about the consistency of that number. Here are the times to query the list for the first 2,000 items with unique permissions.
You can clearly see that at 1,000 items the time to query the list significantly increases no matter how many unique users there are.
However, the biggest problem is the amount of time it takes to add a new user to the site. This can get very large very quickly. Take a look at the amount of time it takes to add a user when you start getting near 12,000 users.
You are getting to a point where it takes more than 5 seconds to add a new user to the site.
How can that time be reduced? Is there a way to pre-add users to the site?
In the next part of this post, I will be examining ways to pre-add users to the site, so that new items can be added more quickly. Also, I am curious how lists scale horizontally. I have shown that the time to query a list jumps at 1000 items, but what if I create 100 lists with 500 items each?