SPList ItemCount on a > 100.000 items list

Having
a list containing more than 100.000 items provided us with some challenges, apparently
it is not possible to retrieve the ItemCount based on certain criteria using the object
model.

The
problem is that we actually had to retrieve those items.

So
a colleague came up with a rather dirty but very useful  fix
for that.

It’s
a small piece of code that makes a View based on a desired query, and sort this on
the Author (all the items are created by the same account). By setting the Collapse
property on true SharePoint renders a HTML Header containing the total amount of items.
By actually reading that piece of HTML he found a quick&dirty way to retrieve
those totals.

Below
you can find the code, and we were wondering if any of you out there would know another
way without using the Search or talking directly to the Content Database.

///
<summary>

///
This function will return the count of the items found by the query, using the RenderAsHTML()
method of the SPView object

///
IMPORTANT: It asumes that all Items are created using the same account (Author), if
this is not the case,

///
please find or provide an other property that is equal for all ListItems

///
NOTE: This function has no error-handling inside.

///
</summary>

///
<param name="list">SPList object that holds the items to count</param>

///
<param name="query">CAML query-string</param>

///
<returns>-1 on error, otherwise number of items found</returns>

privatestaticint GetItemCount(SPList
list
,string query)

{

    return GetItemCount(list, query,"Author");

}

privatestaticint GetItemCount(SPList
list
,string query,string GroupByProperty)

    {

        //Since
files are added by the system, author will be the same for all

        //adding
GroupBy and setting the Collapse to true, the view will be rendered collapsed and
show only the itemcount

        query = String.Format("<GroupBy
Collapse=\"TRUE\" GroupLimit=\"1\"><FieldRef Name=\"{0}\" /></GroupBy>{1}"
,GroupByProperty, query);

        //create
temp view

        list.ParentWeb.AllowUnsafeUpdates =true;

        string TempViewName ="TempViewForItemCount";

        SPView
newview
= list.Views.Add(TempViewName,new StringCollection(){"Soep1","Author"}, query,10000000,false,false);

        newview.Update();

        list.Update();

        //RenderAsHtml
creates a small piece of HTML that contains the itemcount

        string html = list.Views[newview.ID].RenderAsHtml();

        //remove
the temp view

        list.Views.Delete(newview.ID);

        list.ParentWeb.AllowUnsafeUpdates =false;

        //init
a counter

        int count =0;

        //grab
the count which is in brackets right after the '&#8206;'-character

        if(html.IndexOf("&#8206;(")<=0)

            return1;

        html = html.Substring(html.IndexOf("&#8206;(")+8, html.IndexOf(")", html.IndexOf("&#8206;("))(html.IndexOf("&#8206;(")+8));

       

        //try
to parse the count

        if(!int.TryParse(html,out count))

            return1;

        return count;

    }

//testfunction

privatevoid Test()

{

    using(SPWeb
myweb
=new SPSite("http://somedomain.com").RootWeb)

    {

        SPList
SomeList
= myweb.Lists["SomeList"];

        Console.WriteLine("End
loading web at: "
+ DateTime.Now.ToLongTimeString());

        string q ="<Where><Eq><FieldRef
Name=\"SomeField\" /><Value Type=\"Text\">SomeValue</Value></Eq></Where>"
;

        int count = GetItemCount(MenuVanDeDag, q);

    }

}

 

Leave a Reply