So, last week I was at the #SPEvo13 conference in London, and one innocuous session by Wayne Ewington sent shivers down my spine – about query throttling. So, what is query throttling again, and why did it was I worried? Continue reading “Query Throttling in SharePoint 2013”
SPQuery
Dealing with Large Lists Part 3 – The ContentIterator
In Part 1 we looked at the problem of the query throttle in SharePoint 2010, and in Part 2 we looked at some of the ways of getting around that. In this part, though, we’ll look at what I think is the best way – the ContentIterator control. Continue reading “Dealing with Large Lists Part 3 – The ContentIterator”
Dealing with Large Lists Part 2 – Coping with large queries
In Part 1 we looked at what throttling was, why queries could be throttled, the settings that control throttling, and some of the references to other useful information. In this post we’ll look at ways of trying to get your query to run anyway, depsite the throttle. “Writing Efficient Code In SharePoint Server” is a good introduction to the general principles. Continue reading “Dealing with Large Lists Part 2 – Coping with large queries”
Dealing with Large Lists Part 1 – What is throttling and how it causes difficulty
‘Large’ lists – lists with over a few thousand items – can cause problems when you’re developing for SharePoint, and the whole topic is kind of complicated. So what’s the problem?
Well, fundamentally the issue is inefficient queries being run against SharePoint lists. An inefficient query against a list with a small number of items isn’t a big deal, but with larger lists (over 5000 items) SQL server has to escalate from a row-level lock to a table-level lock. This can hold up other users while the table is locked.
So, what are the limits, what options are there with throttling, and what strategies are there for mitigating the effects of query throttling? Continue reading “Dealing with Large Lists Part 1 – What is throttling and how it causes difficulty”
Don't Reuse SPQuery objects!
For God’s sake, don’t reuse SPQuery objects!
Now, with that out of the way, let me explain. I’ve inherited some code. In that code, it’s performing a couple of queries on a List, using an SPQuery object. It’s querying a boolean column, and only one item in the list should be true. It’s a long story why, but that’s the way it is. The list is something like this:
The code needs to query this list for true items (which should only be one) and false items (which you can see above, is more than one). However, these queries didn’t seem to be working. Continue reading “Don't Reuse SPQuery objects!”
Be selective about how you get items from an SPList
Just read an interesting article from Waldek Mastykarz called ‘Performance of various methods to retrieve one list item’. It’s well worth a look – go and read it.
So, the interesting thing I took away from it was just how slow the GetItemById() method was compared to an SPQuery. Why? Wouldn’t it make sense to use and SPQuery within the GetItemById() method?
Well a bit of Reflector digging (again) shows that it does! But it isn’t just a query for the item with that Id – it also specifies that the
SPQuery.ViewAttributes = "Scope="RecursiveAll" ModerationType="Moderator" ";
Hmm. So it’s probably a more complicated query. Depending on your solution, you might not need “RecursiveAll” – I try to avoid folders in lists, preferring views to break down my content.
So, the lesson that I took away was that if you are doing a LOT of getting items from lists, it is worth considering how you want to. There isn’t a right answer, unfortunately, and testing is key. The problem is, there are many factors:
- Can you use caching?
- Do you know the list you’re getting items from?
- Are you getting items from more than one list?
- Do you have folders?
- What other parameters might your query have?
It’s difficult to know, hence I think Waldek is right – give it a test so you have some idea!
Andy Burns' Blog 