Omicron Llama

Coding all day, every day.

8 10 2011

List Item Performance – Wide Lists and Lookups

Jimmywim | development, performance, sharepoint | 3

Disclaimer: 
The examples shown in this post are extremely unrealistic in terms of real-life scenarios. The numbers and types of fields used in these experiments are designed purely to test the physical limitations of SharePoint. At no point is it suggested that a real-life use of such list architecture be used. If you do have lists this wide and are wondering why it is slow, remove the nutmeg from your coffee and learn how to design actually usable schemas. 🙂


Also: thanks to a commenter, “Pro100Oleh”, it’s been pointed out that the Admin account (under which my tests were performed), does actually put more stressful queries into the database. At this moment, I don’t have the time to compare with regular user accounts, so if someone else does, they’re welcome to share the results!


As part of my ongoing adventures into how to make SharePoint fall over I decided to take a look at how wide Lists can be (in terms of number of fields) before performance really takes a nosedive. I set up a little test console application that uses a helper class that takes two numbers, one for the number of rows to create and a second number for the number of fields to create. The application then deletes the test List if it already exists, creates a new one, creates the specified number of fields then continues to create the specified number of items, populating all the fields for each one.

At first, I did this with simple Text fields, and the tests faired pretty well. After keeping folks updated with my proceedings, Neil Hodgkinson reminded me that it’s lookup fields that really caned performance. So I modified the app ever so slightly to create lookup list columns instead. The lookup columns are set up to look at the Title field of the same list, and when populating the new items, I set the lookup value to the previous item in the list to the one just added. Confused? I’m just creating lots of fields and lots of items and timing how long it takes to do so.

I scripted the application so I could run it with increasing numbers of fields, then I was able to chart the performance hit as the number of fields grew. See the results below, and also the source code to the application I used. The code isn’t exactly ideal at the moment to be a true, flexible performance stress testing application, but it sufficed to create the tests and time how long it took to populate them.

Whilst performing these test, I came across a similar blog post from a few years back, when SharePoint 2007 was still in beta. This post kinda brings these findings up to date, as well as testing with Lookup fields (the original post only tested Text Fields, and lots of them!). Read this post here: http://www.sharepoint-tips.com/2006/06/sharepoint-2007-column-limits.html

The Results

For each test I ran, I chose a fixed number of fields that would help in contribute to the stress test such that the number of fields created in each turn could show a perceived reduction in performance. The tables below show the number of items created and the fields created in each iteration. The timings shown firstly show the time for the entire operation in minutes, secondly the average time to create each list item in minutes, and finally a ‘delta’ which shows the difference in time (seconds) since the previous test iteration.

When graphing the results, I am taking the number of fields as the X axis along the bottom, and the time taken on average to create each item as the Y axis. This allows to show a progression in performance reduction as the number of fields increases, and hopefully points to a ‘kill point’ at which performance reduces exponentially.

Text Only Fields

I decided to create 1000 list items on each iteration, with each adding 10 more fields. The following table shows the results:

Operation Item Count Fields Time (Minutes) Time Per Item (Seconds) Delta Overall Time
Create Items 1000 11 0.167 0.01002
Create Items 1000 21 0.22 0.0132 0.053
Create Items 1000 31 0.296 0.01776 0.076
Create Items 1000 41 0.253 0.01518 -0.043
Create Items 1000 51 0.27 0.0162 0.017
Create Items 1000 61 0.281 0.01686 0.011
Create Items 1000 71 0.335 0.0201 0.054
Create Items 1000 81 0.368 0.02208 0.033
Create Items 1000 91 0.378 0.02268 0.01
Create Items 1000 101 0.383 0.02298 0.005
Create Items 1000 111 0.419 0.02514 0.036
Create Items 1000 121 0.449 0.02694 0.03
Create Items 1000 131 0.498 0.02988 0.049
Create Items 1000 141 0.511 0.03066 0.013
Create Items 1000 151 0.524 0.03144 0.013
Create Items 1000 161 0.595 0.0357 0.071
Create Items 1000 171 0.58 0.0348 -0.015
Create Items 1000 181 0.588 0.03528 0.008
Create Items 1000 191 0.621 0.03726 0.033
Create Items 1000 201 0.7 0.042 0.079

And here is the graph:

This graph shows a steady increase the time to create each item as the number of fields increases. Nothing really out of the ordinary here, though if you have 200 text fields in a list I’d love to see your user interface…

Lookup Fields – Shorter Lists

After being pointed out to the fact that Lookups really degrade performance I decided to go all out. This  first test tries out a relatively ‘short’ list with 100 items, but increasing in Lookup fields by 5 each time. Obvious, as the list grows, the performance of the lookup degrades as there are more items in the lookup field, so the average time per item shows as an average across the entire test run. At least that is the assumption. See the second test for a challenge to that.

Here’s the table for the first test:

Operation Item Count Fields Time (Minutes) Time Per Item (Seconds) Delta Overall Time
Create Items 100 5 0.031 0.0186
Create Items 100 10 0.034 0.0204 0.003
Create Items 100 15 0.035 0.021 0.001
Create Items 100 20 0.04 0.024 0.005
Create Items 100 25 0.636 0.3816 0.596
Create Items 100 30 1.7 1.02 1.064
Create Items 100 35 2.209 1.3254 0.509
Create Items 100 40 3.17 1.902 0.961
Create Items 100 45 3.737 2.2422 0.567
Create Items 100 50 4.835 2.901 1.098
Create Items 100 55 6.268 3.7608 1.433
Create Items 100 60 8.459 5.0754 2.191
Create Items 100 65 10.818 6.4908 2.359
Create Items 100 70 12.525 7.515 1.707

And here’s the graph:

The first few test runs, up to 20 lookup fields didn’t show much of a reduction at all, but performance soon started to take a hit from 25 fields, and getting exponentially worse from 50 fields and up, where it’s taking nearly 3 seconds to create every single item!

Lookup Fields – Longer Lists

For the second test with Lookup Fields, I decided to see what would happen with ‘longer’ lists, that had 500 items. This is under the assumption that a lookup field’s performance degrades as the list upon which the lookup operates gets longer.

Here’s the results:

Operation Item Count Fields Time (Minutes) Time Per Item (Seconds) Delta Overall Time
Create Items 500 1 0.397 0.04764
Create Items 500 2 0.469 0.05628 0.072
Create Items 500 3 0.536 0.06432 0.067
Create Items 500 4 0.624 0.07488 0.088
Create Items 500 5 0.745 0.0894 0.121
Create Items 500 6 0.823 0.09876 0.078
Create Items 500 7 0.919 0.11028 0.096
Create Items 500 8 1.013 0.12156 0.094
Create Items 500 9 1.138 0.13656 0.125
Create Items 500 10 1.369 0.16428 0.231
Create Items 500 11 1.455 0.1746 0.086
Create Items 500 12 1.586 0.19032 0.131
Create Items 500 13 2.442 0.29304 0.856
Create Items 500 14 1.981 0.23772 -0.461
Create Items 500 15 2.222 0.26664 0.241
Create Items 500 16 2.405 0.2886 0.183
Create Items 500 17 3.375 0.405 0.97
Create Items 500 18 3.127 0.37524 -0.248
Create Items 500 19 3.43 0.4116 0.303
Create Items 500 20 4.067 0.48804 0.637

And the graph:

This graph shows a steadily decreasing reduction in performance (disregarding the two anomalies). The thing to note here (apart form the trend towards an eventual exponential trend in performance reduction), is that 15 fields with 500 items is timed at 0.26664 seconds per item. The same width-list with 100 items came in at 0.021.

This mean a list with 15 lookup fields that looks up to 500 items is 10x slower than looking up to a list with only 100 items.

Let’s compare to 20 lookup fields. Time of 0.48804 seconds per item with 500 items compared to 0.024 seconds per item when populating 100 items. This makes lookups to 500 items over 20x slower than with 100 items. Proving the fact that more items, coupled with more lookup fields, leads to far, far slower performance when creating and updating list items.

Stating the absolute obvious? Most definitely, but at least now you can see some numbers proving it 🙂

Addendum – Source Code

Here’s the source code for my helper class and console application to create the lists and the list items. By the time I was scripting this application I had it optimised to create lookup fields, but at least you can see the process used.

And here’s the Console App:

3 thoughts on “List Item Performance – Wide Lists and Lookups”

Leave a Reply

Your email address will not be published. Required fields are marked *