Avoid enumerating SPListItemCollection object


Enumerating a SPListItemCollection object should be avoided for data querying purposes.


for e.g. in the following code is not optimal,



using(SPSite site = new SPSite(siteURL))
{
   using(SPWeb web = site.OpenWeb())
   {
      SPList tasksList = web.Lists[“Tasks”];
      foreach(SPListItem item in tasksList.Items)
      {
         // Code
      }

   }
}


This is because each step will invoke a separate call to the DB. Instead the foreach loop should be used on a DataTable object which can derived as mentioned below. This will reduce multiple calls to the content DB with just one.



DataTable dt = tasksList.Items.GetDataTable();
foreach(DataRow dr in dt.Rows)
{
   //code
}


 

Comments (3)

  1. Robin says:

    Nice! Never thought of using this method.. maybe even faster than a CAML query :)

  2. Mike M says:

    There is a fairly authoratative whitepaper about dealing with large lists in SharePoint.  For/each is of course absolutely the worst method, but just using a DataTable isn’t a ton better for the most part.

    http://office.microsoft.com/download/afile.aspx?AssetID=AM102377231033

  3. furuknap says:

    Not sure if I get this right, but getting the DT will result in the entire list sent over the wire and into memory. Thus, if you know that you need to get _all_ items and/or have a limited number of items then fine, but if you only need to examine a certain number of items or have a massive list or limited memory, this seems to be a sub-optimal solution.

    Also, in non-web solutions, responsiveness may be an issue, ie if you want to output intermediate results as they are read, you need to wait for the entire datatable to load before you can output anything.

    Or am I missing something?