Eric Fang's SharePoint Boat: SharePoint 2010 Data Access Performance Issue

Four years ago, I built a small windows form program to test SharePoint 2007 data access performance. The SharePoint server is hosted on local laptop, which has around 4GB ram and 2 CPU cores (at around 2GHz). The "insert" speed is around 50 items/second.

Recently I upgraded my desktop to Ivy bridge core i7 + Intel 520 SSD + 32GB ram. SharePoint 2010 is hosted in a Hyper-V virtual machine with 4 CPU cores and 8GB ram. Then I start to feel curious about the data access performance. The test result is shocking. When I tried to insert list items through SharePoint object model, it's still 50 items/second !

So, despite all those hardware improvement, there is no performance gain at all! It doesn't make sense, I told myself, and decide to dig into it.

First, I opened windows Task Manager. During the test, the CPU utilization rate is less than 5%, and there are plenty spare memory.

Then I started SQL Server Profiler. We need to confirm that the performance bottleneck is not at database side. As the screenshot below, each "insert" takes around 5ms. So, theoretically, the speed may reach above 200 items/second.

SQL Server Profiler

Then, I realized that I made an obvious mistake. I should not run the test program from Visual Studio 2010. When Visual Studio hosts the whole running environment, and intercepts all relevant events and objects, the test result is degraded. After running the program directly from OS, I was surprised to see the speed increased from 50 items/second to 85 items/second.

Then, I tried to insert list items in batch. I didn't check the implementation of "ProcessBatchData" method, but can imagine that it would save unnecessary round trips.

It's true. the speed increased to 140 items/second. Good. That looks much better.

The next step is the parallelization. Since SharePoint is designed to handle thousands of requests, parallelization should be able to speed up the process.

It's also true. Below is the final result.

Action	Single, 1 thread	Bulk, 1 thread	Single, 4 thread	Bulk, 4 thread
Insert	85	140	215	245
Update	75	300	450	1150
Delete	40	70	115	110
Retrieve	1000	N/A	2000	N/A

Please let me know your thoughts. Any advice is appreciated.

PS: The source code of that test program is here: Github

Below are some relevant screenshots of the test result.