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("LongList") '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("LongList") '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="Integer">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.
A really difficult workaround was needed to fix the AddNewItem function where I had code like this:
Dim oNewItem as SPListItem
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>"
oItemColl = oList.GetItems(oEmptyQuery)
oNewSPItem = oItemColl.Add() 'adds an item to the empty list
'saves the new item and re-reads it from SharePoint
oNewSPItem.Update()
oNewSPItem = oList.GetItemById(oNewSPItem.ID)
The two last lines were needed because it seems that the field collection is not geting populated when a query returns no rows. However, my code didn’t seem to run nice without the last two rows.
