Be very careful how you reference your SPListItem objects through the SharePoint API. I’ll say it again. Be very careful how you reference your SPListItem objects through the SharePoint API. Ok, now that you get the point that this will be a “learn from my mistakes and don’t do unsmart things like I did” post, let’s dig into what it was that I did poorly.
For the past year I’ve been building custom .Net applications that are hosted through SharePoint. These application involve a number of SharePoint lists, external databases, custom web parts, and other SharePoint elements to provide functionality. About two weeks ago I received a message from one of our end users that a custom application was performing slowly. Specifically performance was slow when users were performing actions that interacted with the primary SharePoint list storing data for that app.
I took a copy of the production site into a dev environment to investigate the code that was executing. After attaching the debugger and running through the code I quickly found pieces of code referencing SPListItem objects (like below) that were performing very poorly:
As it turns out the SPList I was referencing was fairly large at ~1000 items and weighing in over 150 MB. You see the problem with my above code is that I retrieved the SPListItem by first (unnecessarily) going through the Items member of the list. As I understand it, when doing so the executing code will attempt to resolve that entity and pull it from the database and into RAM (all 150 MB.) This causes the equivalent of a 50 car pile up in terms of performance with a single update taking more than 15 seconds.
The solution is actually quite simple and I wish I had realized this during development. Instead of going through the Items member it is possible to call GetItemById(…) directly on the SPList as in the example below:
After making this simple change performance skyrocketed and updates were back to less than a second.
When given the option between two solutions, usually the simplest is the best solution. In my scenario I was adding extra complexity going through the API the long way around to get to the objects I needed and it ended up hurting performance greatly. Luckily we were able to find and resolve the performance issue in a relatively short amount of time. Like I said at the beginning of the post, learn from my mistakes and hope it helps you.
Image linked from http://www.freespirit.com/files/IMAGE/COVER/LARGE/BeCarefulSafe.jpg