http://apmblog.dynatrace.com/2014/04/03/database-access-patterns-gone-wild-inside-telerik-sharepoint-and-asp-net/

The SharePoint Object Model allows external applications or hosted WebParts to query, modify and create the content which is stored in the SharePoint Content Database. You will find many blog entries, knowledge base articles and best practices about how to correctly use the Object Model for different use case scenarios. I am going to write a series of blog entries over the next week where  I will adress different use case scenarios and will explain what is actually going on within the Object Model.

Scenario 1: How many items are stored in a SharePoint list?

There are multiple ways to answer this question. One example that I’ve seen many times is the following:

int noOfItems = SPContext.Current.List.Items.Count;

This code gives us the number of items in our list but in order to do this it has to retrieve ALL items of the list from the content database. Following PurePath screenshot shows what is executed within the Object Model when accessing the Count property as in the code above:

Accessing the first property of the Items collection queries all items from the content database
Accessing the first property of the Items collection queries all items from the content database

For small lists this might not be a problem as the query is rather fast. But it will become a problem when lists grow over time or when the custom code has never been tested with real life data.

For this scenario Microsoft offers a different property on the SPList object itself called ItemCount. The correct code for this would be:

int noOfItems = SPContext.Current.List.ItemCount;

In this case – SharePoint only needs to query a single record from the Lists table in the content database. The number of items in the list is stored there redundant in order to get this information without the need to query the big AllUserData table where alle SharePoint list items are stored.

Conclusion

If you want to retrieve the number of items in a SharePoint list use the ItemCount property instead of accessing the Count property of the Item’s collection.