Omicron Llama

Coding all day, every day.

SharePoint 2010 Performance with Item Level Permissions

Tonight’s little project was inspired by a conversation on Twitter with @WonderLaura and @Toddklindt. Laura had asked about the implications of lots of item-level permissions in a big list in SharePoint (here), to which Todd replied with this, and followed up with this and this.

So this got me thinking: where is the performance bottleneck when you have item-level permissions in place? Todd’s comments leant towards the construction of the view at the Web Part level, but something was making me think that the checking of permissions would be done at the database-query level. Surely an SPRequest wouldn’t poll the database for all items in a request if not all of them are viewable by the user starting the request?

Time, I thought, to do an experiment. I want to use the SharePoint Foundation 2010 Developer Dashboard to monitor the performance of a large (20,000 item) list before and after item-level permissions have been applied – to help us see if we can spot the bottleneck.

Using SharePoint Foundation 2010, I created a custom list and left it with just the one column for this experiment. I used Visual Studio 2010 (with the CKS:Dev pack) to populate the list with 20,000 items using a Console Application. (Sources for this project can be found at the end of this post).

I then fired up the developer dashboard to see what the performance looks like. I created a normal View in the GUI which returned 200 items from the list. Here’s the results (click for larger version):

The figures I’m interested in looking at are the “DECLARE @DocParentIdForRF” Database Query and the PerfTest OnPreRender numbers. The latter is the time taking to execute the OnPreRender method of the List (which, in ListViewWebPart, prepares the Ribbon for use, and prepares client side ScriptLinks for DataFormWebPart). I have no idea what the first query is all about, it’s not a stored procedure nor a function in the content database and it’s definitely not constructed by any of the managed code of SharePoint. If anyone does know what this does, please let me know purely to satisfy my curiosity.

Next, I rolled up another console application to randomly apply one of the Role Definitions available in the site to every single item in the list, giving us a kind of “worse-case” scenario to look. Again, source code for this is at the end of this post.

Back to the list view, and here’re the results from the Developer Dashboard. Notice huge jump in both the figures – the database query takes around 2 seconds as well as the OnPreRender for the web part! This was after refreshing the page a total of five times once every 10 seconds to let caching, etc. do its thing.

So, for some reason OnPreRender takes an absolute age with a massive list, even though the web part is rendering the same 200 items in the first page of the view, except now we have item level permissions for each one of those items. This method, however, doesn’t deal with the items! It just registers ScriptLinks used by the DataFormWebPart.

As the database query takes less time in both before and after, this leads me to think that the query is slowing down the OnPreRender for some reason, thus leading me to suspect further that performance loss is indeed down to the querying of the database!

If anyone else has any thoughts on this, they’d be gratefully received.

And now for the source.

This first piece of code will create your 2000 items.

This second piece will assign each one a randomly selected Role Definition, after breaking the items Role Inheritance.

PS: Here’s the detail on that query as called:

6 thoughts on “SharePoint 2010 Performance with Item Level Permissions

  • TGITM says:

    You should also monitor the time it takes (espec on db) to set the unique permissions. A combination of BreakRoleInheritance(true) and lots of different permissions to different users should also take an exponentially growing load on the db due to the way SharePoint sets limited access from list item and up ( to allow users access to navigate to item)

  • James Love says:

    @TGITM – good point and very true. This example would’ve put a horrendous load on the database for the reasons you give. My code examples are obviously a single-run setting up of the environment, and even the coding style I used (I foreach’d over SPList.Items!!) would never be done in production for other performance reasons. This gives me an idea for another blog post for another day actually 😉

  • You’re looking at the wrong place. It’s not OnPreRender that stalls this. The times on the right hand side is just the offset. Take a look on the left hand side and you’ll see that its doing databinding which takes time. The databinding occurs before the onprerender, hence the offset. And it’s the databinding that takes time in this case.

  • Maybe I’m just not following your randomizer code, but what does the first part do? I see you add Guest and Contributor roles to a list of roledefinitions to what? It doesn’t get assigned does it?
    And the roleassignments are random in the parent web? How many role assignments were there? Did you intend to add these roles to the web for the listitem to make use of.

  • James Love says:

    @Brian: yeah the first half just gets me a List collection of 2 roles, and I pick a random one from that list when I assign it to each item in turn. I’ve actually just noticed that the code I pasted into the post is an old version, before I realised I wasn’t doing SPListItem.Updated()! I’ll add this now.

  • Great post! now im thinking about this..

Leave a Reply

Your email address will not be published. Required fields are marked *