|Features in common||SQL Server||SharePoint|
|Data volume||Billions of records||Millions of records|
Apart from the exciting SharePoint features, such as versioning, soon I noticed two differences.
- All SPListItems are stored in one SQL table;
- Compare to SQL Server, "Insert,Update,Delete" operations of SPListItem are extremely slow;
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!