SharePoint Dragons

Nikander & Margriet on SharePoint

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 . 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:


SELECT er.session_Id AS [Spid] , sp.ecid , DB_NAME(sp.dbid) AS [Database] , sp.nt_username , er.status , er.wait_type , SUBSTRING (qt.text, (er.statement_start_offset/2) + 1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , sp.program_name , sp.Hostname , sp.nt_domain , er.start_time

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

WHERE session_Id > 50

AND session_Id NOT IN (@@SPID)

ORDER BY session_Id, ecid

SQL DMV query techniques are discussed in the excellent book . We were technical reviewers for this book, and will devote a separate blog post to this book, but if you’re interested, check it out.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: