Thursday, February 18, 2010

Adding items to Large Lists

So I had this blog up before, but I did some digging with a friend at Microsoft and it turns out the additemoptimized method before really wasn't optimized at all... Here is new code that really is optimized...

When you access the List.Items property in the SharePoint API (say to add a new item to a list: List.Items.Add()) this get method runs (thanks Reflector!):


public virtual SPListItemCollection Items
{
get
{
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope=\"Recursive\"";
return this.GetItems(query);
}
}


That actually makes sense if you were looking for the entire SPListItemCollection for the list, but doesn't make sense if you just want to add a new item (Why query ALL items in the list if you are just adding a new one??). Additionally, this becomes a problem when you are working with Very Large Lists as this query can be SLOW and possibly lock the table.

The alternative is to write your own query to get a much smaller SPListItemCollection for the list (I wanted a helper method to give me the collection so I can call Add() on it with different params, this one will only return the schema and not pull any items):


public static SPListItemCollection AddItemOptimized(SPList list)
{
SPQuery EmptyQuery = new SPQuery();
EmptyQuery.Query = "<Where><Eq><FieldRef Name='ID'/><Value Type='Number'>0</Value></Eq></Where>";
return list.GetItems(EmptyQuery);
}


And there you go, your query now returns your SPListItemCollection ready for .Add() without querying for any items!

2 comments:

Anonymous said...

Hey, Thanks for the info.
This confirms my suspicion and help resolving a newly brought up issue in my site.

Anonymous said...

Hey, thanks for the info.
This confirm my suspicion and help resolve a newly brought up issue in my 2007 site.