What is the right way to iterate the SharePoint List Items?

No comments

 

Introduction

Generally when developer writes the code quickly – this might be observed. Lets say in SharePoint “PurchaseOrder” list, there are 500 items. And thru the SharePoint object model coding we want to read these 500 items and display the “OrderNo” column . For this, the code can be written as below:

Sample code(untested):

string strUrl = “http://server001:1111/”;
using(SPSite oSite = new SPSite(strUrl))
{
using(SPWeb oWeb = oSite.OpenWeb())
{
SPList poList= oWeb.Lists[“PurchaseOrder”];
for(int i=0;i<500 && i<poList.Items.Count;i++) //This is an overhead to SQL database.
{
SPListItem oneListItem = poList.Items[i];
Console.WriteLine(oneListItem [“OrderNo”])+ “\n”);
}
}
}

Analysis on the above code

Even though we are iterating 500 times – internally for each iteration it sends request to SQL database server twice i.e. one is for poList.Items.Count and another one is for actual item. So for 500 items internally 1000 times, the above code sends the request to SQL server which is not recommended and hits performance of the code.

What is the recommended way to write the above code?

The above code can be written below way – just instead of passing the items.count inside the loop, we need to assign the all 500 items in the SPListItemCollection variable – then this variable can be passed inside the loop. This way we can reduce the half of the SQL server trip for the above scenario. On top of these, when first time – SPListItemCollection gets the 500 items from SQL, it stored in memory and gets cached, so from the subsequent looping, items are fetched from the cached memory. Hence it speeds up the execution.

Sample code(untested):

string strUrl = “http://server001:1111/”;
using(SPSite oSite = new SPSite(strUrl))
{
using(SPWeb oWeb = oSite.OpenWeb())
{
SPList poList= oWeb.Lists[“PurchaseOrder”];
SPListItemCollection poItems = poList.Items; //This code only one time sends the request to SQL and caches the resulted items in the memory.
for(int i=0;i<500 && i<poItems.Count;i++)
{
SPListItem oneListItem = poItems[i];
Console.WriteLine(oneListItem[“OrderNo”])+ “\n”); //The individual item is being read and displayed from the “poItems” variable not directly from SQL.
}
}
}

More optimization on the above code – in fact most recommended one:

Lets say we have thousands of items in the list – then if we want to fetch them, we should not fetch all items at a time like above. In this scenario – we should use SPQuery or CAML (Collaborative Markup Language) query.

Sample code(untested):

string strUrl = “http://server001:1111/”;
using(SPSite oSite = new SPSite(strUrl))
{
using(SPWeb oWeb = oSite.OpenWeb())
{
SPList poList= oWeb.Lists[“PurchaseOrder”];
SPQuery query = new SPQuery();
query.RowLimit = 300; // As an example retrieve 300 items.
query.ViewFields = “<FieldRef Name=’ID’/><FieldRef Name=’Text Field’/><FieldRef Name=’OrderNo‘/>”; //We can use the view fields attributes of SPQuery,when we want to retrieve the specific column from database.
SPListItemCollection poItems = poList.GetItems(query);
for(int i=0;i<500 && i<poItems.Count;i++)
{
SPListItem oneListItem = poItems[i];
Console.WriteLine(oneListItem[“OrderNo”])+ “\n”);
}
}
}

Summary:

Thus, in this article we have learned that how recommended way we can query the list data.