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.
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: