Querying Large Lists using CAML

Struggling with this one for a large point of the day, before I realised what was happening.  I was running a query using SPQuery and CAML against a list that needed to be restricted for a subset of users.  I was happily running it as me, but my test (restricted) user was getting Null reference errors.

I quickly realised that my list had over 36,000 items in it (split into folders of < 2000 items) so I had to come up with a way of running the same CAML query over the list independent of user.

There are number of options (well explained by <this excellent blog entry by Steve Peschka to deal with list thresholds and programmatic overrides.

SPList has an EnableThrottling property, which is set to True by default.  You can use the Object Model to set it to False temporarily.  The problem is that the permissions to do so cannot be done by anyone who isn’t a Farm Administrator.  SPSecurity.RunWithExecutivePrivileges run as the Application Pool account which is, unless you’ve configured something wrongly should not be a Farm Admin level account.

To cut a long story short, you need to either do this in code or Powershell when the list is created.  Alternatively, if your list has popped over the item count threshold, you can do it at any time.  The Powershell script is simple.

$url="<%YOUR URL HERE%>"
$listname="<%YOUR LIST NAME HERE%>"
$web = Get-SPWeb $url
$list = $web.Lists[$listname]
$list.EnableThrottling = $false

To switch throttling back on, simply re-run the script, changing the $false to $true.

The beauty of this is that it applies on a list by list basis, so you don’t have to go into Central Administration and make Web Application wide changes to get a single large list to query.  The script is modified from one published by Dina Ayoub.

Posted on March 17, 2013, in SharePoint and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: