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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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 http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730/ref=sr_1_1?ie=UTF8&qid=1338967190&sr=8-1 . 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.

About these ads

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 640 other followers

%d bloggers like this: