SharePoint 2007 Performance: Difference between foreach and CAML query in getting certain list item

Okay back to normal status (in English and SharePoint ;). Back in the Tech Ed 2008, I heard some side notes about how much better it is to use CAML queries in getting certain list items from SharePoint’s list. Those were just side mentions and those got me thinking how much is the difference. So in the one morning I created this small C# console code to check things up:

 

SPSite site = new SPSite("http://localhost");

           SPWeb web = site.OpenWeb("mySite");

          DateTime startDT = DateTime.Now;

          foreach (SPListItem itemi in web.Lists["ListWithItems"].Items)
          {
              try
              {
                  if (itemi["ItemColumnWithWantedInfo"].ToString() == @"SomeFancyRule")
                  {
                      //found the right one

                  }
              }
              catch (Exception ex)
              {

              }
          }

          DateTime endDT = DateTime.Now;

          TimeSpan time = startDT – endDT;

          Console.WriteLine("ForEach: " +time.ToString());

           DateTime startCaml = DateTime.Now;

           string strQuery = "<Where><Eq><FieldRef Name=’ItemColumnWithWantedInfo’ /><Value Type=’Text’>SomeFancyRule</Value></Eq></Where>";

          SPQuery query = new SPQuery();
          query.Query = strQuery;

          SPListItemCollection myColl = web.Lists["ListWithItems"].GetItems(query);

          DateTime endCaml = DateTime.Now;

          TimeSpan timeCaml = startCaml – endCaml;

          Console.WriteLine("CAML: " + timeCaml.ToString());

 

So this code at first looks up list items in foreach and later with CAML query. There is something weird caching stuff going on with that demo, if you run that code as is it shown above you’ll get something like 00:00:00:00 for the CAML query. So comment the part you do not want to run and then kick it.

The difference between foreach and CAML query is something like 2 seconds (foreach runs for around 2 secs in a list that has 12 items and foreach runs something like 0.28 seconds). So CAML rocks in this scenario and you’ll understand it since CAML queries do run in database end and foreach handles all the stuff on client end. I do not know how rigged this is but feel free to comment.

Applies to: (havent figured the tags out yet;)

Windows SharePoint Services 3.0, WSS 3.0, MOSS, SharePoint Products and Technology 2007

Advertisements

About Sakarih

I am SharePoint developer/architect from Finland and my blogposts are all about SharePoint 2007/2010/2013 stuff. There might be also something else posted.
This entry was posted in Uncategorized. Bookmark the permalink.

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