Finding SPListItem by its ID -- Optimized Approach

I just found out that some operations in my SharePoint application run quite slow when used on lists containing large amounts of listitems. In the particular case it was approximately 2,500 items in the list.

The result is: NEVER use this code construction to find an element by its ID:

oList = oWeb.Lists("MyCustomList")     //gets the list object

oItem = oList.Items.GetItemById(123)   //finds the element within the list in the list

You should rather use this one:

oList = oWeb.Lists("MyCustomList")     //gets the list object

oItem = oList.GetItemById(123)         //finds the element within the list in the list

The big mistake is that accessing the “Items” property of the SPList object oList creates an SPQuery object in background to read all the items in particular list and then GetItemById() method of the SPListItemCollection (oList.Items is a SPListItemCollection object) runs a loop, looking for an item with ID attribute set to the value 123.

However, if you use the second code example the SPList ojbect oList creates an SPQuery object in background to read only one object having ID attribute set to 123, i.e.:

<Where><Eq><FieldRef Name="ID"></FieldRef><Value Type="Counter">123<Value></Eq></Where>

and then returns the element it found.

The same applies to the DeleteItemById() method. I had to work around in a bit different manner:

oList.Items.DeleteItemById(123) //this was the original code

oList.GetitemById(123).ListItems.DeleteItemById(123) //this is the improved code

 

As you can see, the improved code first finds the list item by its ID, then accesses the ListItems property (which is again SPListItemCollection) and executes a DeleteItemById() command on the particular collection where the size of the collection is 1. 

Note : Before delete verify whether the item is available or not by using GetItemById() for unwanted failure

 

A really difficult workaround was needed to fix the AddNewItem function where I had code like this:

SPListItem oNewItem;

oNewItem = oList.Items.Add()

Again, accessing the Items attribute leads to reading all the items in the list. To avoid that, I created a SPQuery object that never returned any elements, then called Add() method on the collection returned by the query.

//reads all items having id=0. Should return no rows

oEmptyQuery.Query = "<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Eq></Where>";

SPListItemCollection oItemColl = oList.GetItems(oEmptyQuery);

SPListItem oNewSPItem = oItemColl.Add();    //adds an item

oNewSPItem["FieldName"] = value;

oNewSPItem.Update()

Happy Coding.................