locked
SPListItemCollection.Count is slow RRS feed

  • Question

  • Why is SPListItemCollection.Count so slow even there is no item in it?

    Here is my code:

    SPList list = oSite.GetList(listPath);
    SPFolder folder = oSite.GetFolder(folderPath);
    SPQuery query = new SPQuery();
    query.ViewAttributes = "Scope=\"Recursive\"";
    query.Folder = folder;
    SPListItemCollection items = list.GetItems(query);
    int count = items.Count;

    In my code, items.Count takes 15,000ms, even the running machine is a virtual server that has 4 cpu and 8gb memory.

    Hope someone helps me.

    Regards,

    Tuesday, June 5, 2012 9:14 AM

Answers

  • I think you may find one of my older articles (it was about 2007, but still applies), very interesting: http://www.loisandclark.eu/Pages/Velocity.aspx In general, calling count leads to multiple SQL queries to establish the count. If you do this when looping thru a folder collection, performance can become disastrous fast. I'm assuming that, although there or no items, there are a lot of folders...

    Best thing you can do now is profile the performance and see what actually happens under the covers using either SQL Profiler (not recommended in a production situation) or the following SQL DMV query (recommended in production):

    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


    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com

    • Marked as answer by Shimin Huang Friday, June 15, 2012 6:19 AM
    Wednesday, June 6, 2012 7:13 AM

All replies

  • if want to get list items count, use list.ItemCount .

    yaşamak bir eylemdir

    Tuesday, June 5, 2012 9:26 AM
  • Hi,

    The SPList.ItemCount property is the recommended way to retrieve the number of items in a list. Take a look at this thread Best Practices: Common Coding Issues When Using the SharePoint Object Model


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    • Proposed as answer by Ludovic Caffin Tuesday, June 5, 2012 9:37 AM
    • Unproposed as answer by Thomas2012 Wednesday, June 6, 2012 6:28 AM
    Tuesday, June 5, 2012 9:33 AM
  • Thanks for replays. Not only items count but the SPListItem in SPListItemCollection will be needed in my code.

    Because the document library has possibility to contain both large and small number of items, I decide to use SPQuery to get SPListItemCollection instead of get SPListItem step by step.

    Regards,

    Wednesday, June 6, 2012 6:31 AM
  • I think you may find one of my older articles (it was about 2007, but still applies), very interesting: http://www.loisandclark.eu/Pages/Velocity.aspx In general, calling count leads to multiple SQL queries to establish the count. If you do this when looping thru a folder collection, performance can become disastrous fast. I'm assuming that, although there or no items, there are a lot of folders...

    Best thing you can do now is profile the performance and see what actually happens under the covers using either SQL Profiler (not recommended in a production situation) or the following SQL DMV query (recommended in production):

    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


    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com

    • Marked as answer by Shimin Huang Friday, June 15, 2012 6:19 AM
    Wednesday, June 6, 2012 7:13 AM