Tuesday, July 29, 2014

What is SPListItem/SPFile anyway?

When I started to touch SharePoint years ago, it's natural to treat "List" as "SQL table". They have so many similar features:

Features in commonSQL ServerSharePoint
Data volumeBillions of recordsMillions of records
Event triggerYesYes
Query languageSQLCAML

Apart from the exciting SharePoint features, such as versioning, soon I noticed two differences.
  1. All SPListItems are stored in one SQL table;
  2. Compare to SQL Server, "Insert,Update,Delete" operations of SPListItem are extremely slow;
Now, can we treat a SPListItem as a record in SQL table, with advanced features and poor performance on modification? And, further more, can we treat SPList as a SQL table which allow site owners to customize?

Definitely NOT!

This is the conclusion I got after seven years of development and administration on SharePoint platform. Believe it or not, I feel that SPListItem/SPFile is actually Message, and SPList/SPContentType is Queue.

Let me show an example here.

If there are millions of items in a SPList, and we need to update the "Title" field of all data based on some rules, how should we do it? We cannot update data in batch, instead, we have to do the update one by one. This is how we process messages, not records.

Once we understand this point, then it's easy to understand why all SPListItems are stored in one SQL table, why the modification is so slow, and how we can utilize SharePoint in better way.

What do you think? Any comments are welcome!


  1. interesting, what other analogies can you draw between lists and queues?
    also, you can do batch updates on list items by using SPWeb.ProcessBatchData

    1. Thanks for comment.

      "SPWeb.ProcessBatchData" actually accepts "parameters" in batch, but in the background, the data is still processed piece by piece.

      Many SharePoint "personalities" are anti-logical. Performance is one, permission management is another one. I plan to write a post regarding BCS, which is also awkward.

      But, in the end, I still like it :-)

  2. I just want to thank you for the work that you do and your willingness to share. You have created outstanding materials and we all benefit from your generosity.