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 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|
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.
|Insert - 1 thread|
|Insert - 4 thread|
|Update - 1 thread|
|Update - 4 thread|
|Delete - 1 thread|
|Delete - 4 thread|
|Retrieve - 1 thread|
|Retrieve - 4 thread|