Column Indexing Oddities in SharePoint

2013-12-172014-11-30 Andy BurnsLarge Lists, SP2013 Development, SPFieldLookup, TaxonomyLeave a comment

I’ve come across a few issues lately with list column indices (indexes?) in SharePoint that have caused some trouble recently.

Multi-valued columns cannot be indexed. It doesn’t matter what type they are, they can’t be indexed. This isn’t actually so surprising when you think about it – where in the index would they be – though it might be nice if they simply appeared in it twice. To be honest, I’d known this for some time, but it did catch me out recently.
Lookup Columns can’t be used in ListViews set up through the UI. It seems that the SharePoint UI sets the list view up to filter using the ‘text’ of the column, which isn’t indexed – rather than using the LookupId, which is. A List View in a list definition, or set up through code, can therefore be fine…
The Approval Status column added by Content Approval can’t be indexed.
Single-valued Managed Metadata (Taxonomy) columns can be indexed – but not sorted. ORDER BY clauses won’t work (more on how to solve that in a later post). I think this is due to multi-lingual support – but it is a pain.

Yes, some of these issues are particularly acute given some of Office 365’s search limitations, and awkwardness with Content Approval.

"Minor" Search limitations in Office 365

2013-12-122014-11-30 Andy BurnsLarge Lists, Office 365, QueryThrottling, SP2007 Search, SP2013 Development2 Comments

If you’re a SharePoint developer like me, you probably find the Query Throttle in SharePoint an absolute pain. This is a feature where, under an unnecessarily complicated set of conditions, if you try to ask for too many list items in one query, SharePoint refuses.

Now, while I understand why such a limit is necessary – albeit I’d rather it was simpler – it is something of a problem. For example, multi-choice columns cannot be indexed. If you want to query such a column, and there are more than 5000 items in the list, we’ll, you’re outta luck.

That situation is surprisingly common in Document Management systems. Continue reading “"Minor" Search limitations in Office 365” →

Content Approval and Large Lists

2013-12-112014-11-30 Andy BurnsContent Approval, Large Lists, Office 365, QueryThrottling, SP2013 Administration, SP2013 Development2 Comments

So I’ve discovered an interesting problem with Content Approval, which is a special issue for Office 365. I have a document library with more than 5000 documents in it. Content Approval is turned on, and the customer would like a view of ‘Unapproved Documents’.

For a Document Management System, 5000 documents in one repository is small beer, so this is a bit of a problem. “Okay”, I thought, “this must be a choice column, so I’ll just add an index to the column”. Continue reading “Content Approval and Large Lists” →

Looking up against Large Lists in Office 365

2013-10-242014-11-30 Andy Burnsautocomplete, JavaScript, jQuery, Large Lists, Office 365, QueryThrottleException, QueryThrottling, SP2013 Development14 Comments

So, I had an issue that I’ve a customer who wanted to have some items look up against a large list. (In fact, it was a large document library). This large list had more than 5000 items. This is a little unfortunate, due to a painful and annoying quirk in SharePoint’s design. Continue reading “Looking up against Large Lists in Office 365” →

Query Throttling in SharePoint 2013

2013-04-262014-11-30 Andy BurnsLarge Lists, QueryThrottling, SP2013 Development, SPQueryLeave a comment

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” →

Dealing with Large Lists Part 3 – The ContentIterator

2012-07-132014-11-30 Andy BurnsContentIterator, Large Lists, SP2010 Development, SPQuery1 Comment

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

2012-07-112014-11-30 Andy BurnsLarge Lists, SP2010 Development, SPQuery2 Comments

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

2012-07-092014-11-30 Andy BurnsLarge Lists, QueryThrottleException, QueryThrottling, SP2010 Development, SPQueryLeave a comment

‘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” →

Andy Burns' Blog

Whatever I'm working on

Large Lists