Working with sharepoint large lists

Please visit this : Samsung Service Galaxy S

In a recent project, I had to get some information from the last sharepoint listitems inserted. I used foreach. Of course it is a bad idea. When the list grew (thousand of listitems)…the program worked as fast as a drunken turtle. Ok… As I was saying the data I needed was from the last x items inserted. So I replaced the foreach with a simple for:
count=10; (the last 10 items inserted)
for (int i = oList.Items.Count – count; i < oList.Items.Count; i++)

I ran the program and saw that the improvement was not what I expected, so I made a testProject to measure the execution time, for the foreach block code and the code above (with the simple for);

DateTime startTime = DateTime.Now;

chunk of code (for or foreach iteration)

DateTime stopTime = DateTime.Now;
TimeSpan duration = stopTime – startTime;

For large lists the foreach statement took forever. The for statement took from 5 to 30 seconds for 4000 list items.
The odd part was that in the for statement as I said before, i iterated through the last 10 items.
So for 10 items I waited some good seconds.
I googled for the truth and I found this interesting article.

So I found out the SPQuery is one of the best ways to work with large lists.

SPQuery query = new SPQuery(oList.Views[0]);
query.RowLimit = 10;
SPListItemCollection filteredList = oList.GetItems(query);
foreach (SPListItem item in filteredList)

I ordered the list Descending by their ID so i got the most recent 10 list items.
I measured the execution time. At the first run it took 800 milliseconds for 4000 list items. The next executions took only from 15 to 30 milliseconds.
I would say…it is at least a good improvement;
An easier way to create CAML queries is with U2U CAML Query Builder
I tried to use CAML query to get the max ID directly but I didn`t succeed. If someone reads this…:P and knows how please leave a reply.

Commerce Server 2009
Thank you,
Ing. Msc. Dan Gheorghe


Add RoleAssignment to listitem.

Please visit this : Samsung Service Galaxy S

This is continuing the previous post. After you create the list item impersonating the current logged in user in the sharepoint site you might want to give that user only the right to read it.
After oListItem.Update(); you must run the code to do this with elevated Privileges to assign the role.

SPUser user = oWebsiteRoot.SiteUsers.GetByID(web.CurrentUser.ID);
SPSite site = new SPSite(SPControl.GetContextSite(this.Context).ID);
SPWeb w = site.OpenWeb();
Guid g = oListItem.UniqueId;
oListItem = w.Lists["Announcements"].GetItemByUniqueId(g);

oListItem is the listitem created in the previous post

I tried to set the RoleInheritence of the list item to false: oListItem.BreakRoleInheritance(false);
but i had some problems with it. I got the error:
“Updates are currently disallowed on GET requests.”

oListItem.BreakRoleInheritance(false) sets the spweb.AllowUnsafeUpdates to false; so after this you must set it true manually again even though we did that in the code before this (in the previous post) :

w.AllowUnsafeUpdates = true;

Now to set the roles:

SPRoleDefinitionCollection webroledefinitions = w.RoleDefinitions;

SPRoleAssignment roleassignment = new SPRoleAssignment(user);



And now every user sees the the items created by themselves.

Commerce Server 2009
By Ing. Msc. Dan Gheorghe