Why is SPListItemCollection.Count so slow?

There are situations where people experience disastrous response times when trying to count the number of items in a collection or folder. Why does this happen? A long time ago, we wrote about this at http://www.loisandclark.eu/Pages/Velocity.aspx . Basically, looping thru a collection and calling the Count property leads to a huge amount of SQL queries that are fired, resulting in a disappointing performance.

The best thing you can do in such a situation is establish what happens under the covers, and take actions accordingly. Usually, people advise to do this using SQL Server Profiler, which works fine for dev scenarios, but isn’t recommendable (because of performance reasons) in a production environment. Instead, you’d be better of executing a SQL DMV query that identifies the currently running SQL queries. Such as this one: