Developing for the future? Large lists?

At the moment I’m sitting in the Gman garage in Antwerpen waiting on my car to be fixed. So excellent time to write some blog posts that I’ve been putting off for too long now.

Some time ago there was a problem at the client with a list of 10-20k items big. The discussion on how large a list is and should it be put in SP I’ll leave for another time, so the list was there and we had to solve it.

A simple query in an xsltlistviewwebpart would be the beginning of a big issue.

I’ve added some very interesting reads at the bottom of this article but all didn’t solve my issue.

Below was one of the first issues that we had.

Error_ItemLevelPermission

If you Google this one, a lot will tell, just set the threshold of the web application higher.

well, first ask yourself, why is this limit 5000 items?

To minimize database contention, SQL Server uses row-level locking as a strategy to ensure accurate updates without adversely affecting users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at the same time, then it is more efficient for SQL Server to escalate the lock to the entire table until the database operation is completed. When this lock escalation occurs, it prevents other users from accessing the table

This small part in a very large blog article on Technet explains why.

Meanwhile the threshold was already set at 55000 items on production = NOT GOOD

Sure it solved the issue for a while but it created others elsewhere.

So back to the drawing board, why do we have this issue. Why is a query generating this particular error.

I will spare you the details on looking into this issue and skip to the solution.

It was because not only these 10k-20k large list was big but also all the items in it had broken security. So we had item level security in place. This is why we had this issue.

A little re engineering later (using folders with broken security and putting the items in those folders solved it) and all was good to go. From 20k items we went to 35 folders with broken security and the query would run nicely now.

A fun fact: working with large lists it’s often suggested to use the content iterator. Well the content iterator in the back-end applies a row limit, recursive node and listitemcollectionposition. In doing so it queries in batches but uses SPQuery just as well.

So CAML isn’t dead yet hehehe

 

Sneak preview: Did you know that our Caml Designer 2013 now supports SP 2010 as well, so you can work with the newly (and cool) designed CamlDesigner 2013 on both SharePoint versions. (Release is planned for 27 April).

 

Some good reading:

I’ve divided this into a table because of the many SharePoint versions:

Title (link to articles)

SharePoint Version

Comments

Software boundaries for large lists in SharePoint
2013

2013

 

Manage lists and libraries with many items

2013 & Office 365

 

Must do course

2013

 

Technet article

2010

 

Blog article from Steve Peschka

2010

 

Blog article from joel oleson

2010

 

Blog article from the SharePointBaker
(a buddy of mine 😉 )

2010

 

Working with large lists

WSS 3.0

It says WSS 3.0 but it still applies for the other
versions

Official read

2007

 

Leave a Reply

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