Again about SharePoint list performance…

We have been refactoring some elements of our SharePoint HR solution in terms of improving the performance of reading data from SharePoint lists (thousands of items).

I’d like to share few “bottlenecks” we faced and their solutions.

1. ViewFields property of SPQuery
There are a lot of recommendations to use SPQuery.ViewFields property to limit the set of fields returned by your query.

However, sometimes it is ignored when you need to read all the fields you have created.

We recommend defining this property anyway as far as SPQuery would read a number of internal fields, which you probably do not need.

So in our case defining all our custom fields in combination with ViewFieldsOnly = True gave the significant performance improvement.

2. SPUser field
User field is quite useful and popular when designing custom lists. However creating an SPUser object based on a field value takes very long time.

The good news that in a lot of cases (like our) you do not really need a full SPUser object, but UserID or User Name only.

User field contains a value similar to a Lookup (like, “1;#John Smith”).

So instead of creating SPUser object, we just parsed that string and took ID and Name we needed.

Again significant performance improvements…

3. Lookups with additional fields.

The great new feature of SharePoint 2010 is the ability to “map” additional fields when creating a Lookup field. However that brings additional overheads when you read such data.

We had a list with 4 lookups and 2 of them had additional “mapped” fields.

Reading only 2000 items have been taking a lot of time

That’s a mystery how SharePoint treats that internally, however deleting of only one lookup with an additional field decreased the reading time about 5 times.

Recommendation that is difficult to follow J but try to minimize lookup fields and especially mapping additional fields.



There are not any world-shaking discoveries, however still the practical recommendations, which would probably help you.