Wednesday, August 4, 2010

SharePoint 2010 Handling Large SPLists

Handling Large Folders and Lists
Updated: July 2010

When the size of folders and lists increases, you must design custom code that works with them to optimize performance. Otherwise, your applications will run slowly and can cause service or page load timeouts. The two primary areas for concern when handling large folders and lists are the following:

•Query throttling, which can cause the behavior of your code to change in unexpected and unpredictable ways over time as your site evolves and your queries begin to return items that exceed the query threshold.

•Efficient retrieval of items from large folders and lists.

To address both concerns, you must understand how the object model interacts with folders and lists.

Throttling for Large List Queries
--------------------------------------------------------------------------------

Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 apply a default query threshold of 5,000 items. Any custom code that relies on query result sets that can exceed this maximum will not perform as expected. Queries on lists consisting of more than 5,000 items that include fields that are not indexed in their query conditions will also fail, because those queries must scan all rows in a list. Follow the steps listed below to view and increase this limit or to enable the object model to override the limit:

To view and increase this threshold or to enable the object model to override the threshold
1.On the Central Administration site, under Application Management, click Manage Web Applications.

2.Click General Settings, and then click Resource Throttling.

3.View and update the threshold or allow the object model to override the limit.

Working with Folders and Lists
--------------------------------------------------------------------------------

The following recommendations for addressing performance concerns when working with large folders and lists are based on the test results reported in Steve Peschka's white paper, Working with Large Lists in Office SharePoint Server 2007. These recommendations are also applicable to Microsoft SharePoint Server 2010. For additional guidance on using SPQuery and the PortalSiteMapProvider class, which applies specifically to SharePoint Server 2010, see Writing Efficient Code in SharePoint Server.

When you are working with folders and lists:

•Do not use SPList.Items.

SPList.Items selects all items from all subfolders, including all fields in the list. Use the following alternatives for each use case.

◦Adding an item

Instead of calling SPList.Items.Add, use SPList.AddItem.

◦Retrieving all items in a list

Instead of using SPList.Items, use SPList.GetItems(SPQuery query) . Apply filters, if appropriate, and specify only the fields you need to make the query more efficient. If the list contains more than 2,000 items, paginate the list in increments of no more than 2,000 items. The following code example shows how to paginate a large list.

Good Coding Practice

Retrieving items with SPList.GetItems

VBC#C++F#JScriptCopySPQuery query = new SPQuery();
SPListItemCollection spListItems ;
string lastItemIdOnPage = null; // Page position.
int itemCount = 2000

while (itemCount == 2000)
{
// Include only the fields you will use.
query.ViewFields = "";
query.RowLimit = 2000; // Only select the top 2000.
// Include items in a subfolder (if necessary).
query.ViewAttributes = "Scope=\"Recursive\"";
StringBuilder sb = new StringBuilder();
// To make the query order by ID and stop scanning the table, specify the OrderBy override attribute.
sb.Append("");
//.. Append more text as necessary ..
query.Query = sb.ToString();
// Get 2,000 more items.

SPListItemCollectionPosition pos = new SPListItemCollectionPosition(lastItemIdOnPage);
query.ListItemCollectionPosition = pos; //Page info.
spListItems = spList.GetItems(query);
lastItemIdOnPage = spListItems.ListItemCollectionPosition.PagingInfo;
// Code to enumerate the spListItems.
// If itemCount <2000, finish the enumeration. itemCount = spListItems.Count; } The following example shows how to enumerate and paginate a large list. VBC#C++F#JScriptCopySPWeb oWebsite = SPContext.Current.Web; SPList oList = oWebsite.Lists["Announcements"]; SPQuery oQuery = new SPQuery(); oQuery.RowLimit = 10; int intIndex = 1; do { Response.Write("
Page: " + intIndex + "
");
SPListItemCollection collListItems = oList.GetItems(oQuery);

foreach (SPListItem oListItem in collListItems)
{
Response.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) +"
");
}

oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition;
intIndex++;
} while (oQuery.ListItemCollectionPosition != null);

◦Getting items by identifier

Instead of using SPList.Items.GetItemById, use SPList.GetItemById(int id, string field1, params string[] fields). Specify the item identifier and the field that you want.

•Do not enumerate entire SPList.Items collections or SPFolder.Files collections.

The left column in Table 1 lists the methods and properties that if used will enumerate the entire SPList.Items collection, and cause poor performance and throttling for large lists. Instead, use the better-performing alternatives listed in the right column.

Table 1. Alternatives to enumerate SPList.ItemsPoor Performing Methods and Properties
Better Performing Alternatives

SPList.Items.Count
SPList.ItemCount

SPList.Items.XmlDataSchema
Create an SPQuery object to retrieve only the items you want.

SPList.Items.NumberOfFields
Create an SPQuery object (specifying the ViewFields) to retrieve only the items you want.

SPList.Items[System.Guid]
SPList.GetItemByUniqueId(System.Guid)

SPList.Items[System.Int32]
SPList.GetItemById(System.Int32)

SPList.Items.GetItemById(System.Int32)
SPList.GetItemById(System.Int32)

SPList.Items.ReorderItems(System.Boolean[],System.Int32[],System.Int32)
Perform a paged query by using SPQuery and reorder the items within each page.

SPList.Items.ListItemCollectionPosition
ContentIterator.ProcessListItems(SPList, ContentIterator.ItemProcessor, ContentIterator.ItemProcessorErrorCallout) (Microsoft SharePoint Server 2010 only)

SPList.Items.ListItemCollectionPosition
ContentIterator.ProcessListItems(SPList, ContentIterator.ItemProcessor, ContentIterator.ItemProcessorErrorCallout) (SharePoint Server 2010 only)


Note
Using the SPList.ItemCount property is the recommended way to retrieve the number of items in a list. As a side effect of tuning this property for performance, however, the property can occasionally return unexpected results. For example, if you require the exact number of items, you should use the poorer performing GetItems(SPQuery query), as shown in the preceding code example.


•Whenever possible, acquire a reference to a list by using the list's GUID or URL as a key.

You can retrieve an SPList object from the SPWeb.Lists property by using the list's GUID or display name as an indexer. Using SPWeb.Lists[GUID] and SPWeb.GetList(strURL) is always preferable to using SPWeb.Lists[strDisplayName]. Using the GUID is preferable because it is unique, permanent, and requires only a single database lookup. The display name indexer retrieves the names of all the lists in the site and then does a string comparison with them. If you have a list URL instead of a GUID, you can use the GetList method in SPWeb to look up the list's GUID in the content database before retrieving the list.

•Do not enumerate entire SPFolder.Files collections.

The left column in Table 2 lists the methods and properties that inflate the SPFolder.Files collection and cause poor performance and throttling for large lists. Instead, use the better-performing alternatives in the right column.

Table 2. Alternatives to SPFolders.FilesPoor Performing Methods and Properties
Better Performing Alternatives

SPFolder.Files.Count
SPFolder.ItemCount

SPFolder.Files.GetEnumerator()
ContentIterator.ProcessFilesInFolder(SPFolder, System.Boolean, ContentIterator.FileProcessor, ContentIterator.FileProcessorErrorCallout) (SharePoint Server 2010 only)

SPFolder.Files[System.String]
ContentIterator.GetFileInFolder(SPFolder, System.String)Alternatively, SPFolder.ParentWeb.GetFile(SPUrlUtility.CombineUrl(SPFolder.Url, System.String) (SharePoint Server 2010 only)

SPFolder.Files[System.Int32]
Do not use. Switch to ContentIterator.ProcessFilesInFolder and count items during iteration. (SharePoint Server 2010 only)


Deleting Multiple Versions of a List Item
--------------------------------------------------------------------------------

When you delete multiple versions of a list item, use the DeleteByID() method; do not use the Delete() method. You will experience performance problems if you delete each SPListItemVersion object from an SPListItemVersionCollection object. The recommended practice is to create an array that contains the ID properties of each version and then delete each version by using the SPFileVersionCollection.DeleteByID method. The following code examples demonstrate both the approach that is not recommended and the recommended approach to deleting all versions of the first item of a custom list.

Bad Coding Practice

Deleting each SPListItemVersion object

VBC#C++F#JScriptCopySPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];
SPListItem item = list.GetItemById(1);
SPListItemVersionCollection vCollection = item.Versions;
ArrayList idList = new ArrayList();
foreach(SPListItemVersion ver in vCollection)
{
idList.Add(ver.VersionId);
}
foreach(int verID in idList)
{
SPListItemVersion version = vCollection.GetVersionFromID(verID);
try
{
version.Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Good Coding Practice

Deleting each version of a list item by using the SPFileVersionCollection.DeleteByID method

VBC#C++F#JScriptCopySPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];
SPListItem item = list.GetItemById(1);
SPFile file = web.GetFile(item.Url);
SPFileVersionCollection collection = file.Versions;
ArrayList idList = new ArrayList();
foreach (SPFileVersion ver in collection)
{
idList.Add(ver.ID);
}
foreach (int verID in idList)
{
try
{
collection.DeleteByID(verID);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
If you are deleting versions of items in a document library, you can use a similar approach by retrieving the SPListItem.File.Versions property, as in the following code example.

Good Coding Practice

Deleting each version of a list item in a document library by using the SPFileVersionCollection.DeleteByID method

VBC#C++F#JScriptCopySPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];

SPFile file = list.RootFolder.Files[0];
SPFileVersionCollection collection = file.Versions;
ArrayList idList = new ArrayList();
foreach (SPFileVersion ver in collection)
{
idList.Add(ver.ID);
}
foreach (int verID in idList)
{
try
{
collection.DeleteByID(verID);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Thursday, May 13, 2010

MVC on SharePoint??

http://www.asp.net/mvc/tutorials/asp-net-mvc-overview--cs

Thursday, March 4, 2010

Force Checkin files TFS 2005

tf lock $/MyProject/MyPath/MyFile.cs /lock:none /workspacename:WorkSpaceName /server:my_server

There is also an Undo command. It would get rid of any pending changes that the developer had made and they will lose any work they were doing. The syntax for Undo is:-

tf undo /workspacename:WorkSpaceName;dev_username /server:my_server $/MyProject/MyPath/MyFile.cs

We had a developer leave our company with some files still checked out when we were running on Beta 2. To find out which workspaces the developer had we used:-

tf workspaces /owner:dev_username /computer:* /server:my_server

Then we deleted them. Deleting the workspace also removes the lock on the files. The command to delete a workspace is:-

tf workspace /delete /server:your_tf_server workspacename;workspaceowner

Friday, January 22, 2010

Custom Permissions based on MetaData

An interesting request came my way over the past couple of weeks. Could we provide capability to add security for lists based on MetaData columns. Reading a couple of blogs and information available online I am working on a solution that might provide a solution to our users. Stand by, if successful I will post the code online over the weekend for interested parties.

Thursday, January 14, 2010

Get Specific Folder using SP WebServcie and CAML Query

StringBuilder sbQuery = new StringBuilder(100);
sbQuery.Append("");
sbQuery.Append("<where> ");
sbQuery.Append(" <and>");
sbQuery.Append("<eq> ");
sbQuery.Append("<fieldref name="\">");
sbQuery.Append("<value type="\">1</value>");
sbQuery.Append("</eq>");
sbQuery.Append("<contains>");
sbQuery.Append("<fieldref name="\">");
sbQuery.Append(string.Format("<value type="\">{0}</value>", folderName));
sbQuery.Append("</contains>");
sbQuery.Append(" </and>");
sbQuery.Append("</where>>");
sbQuery.Append("</query>");
sbQuery.Append("<viewfields>");
sbQuery.Append("<fieldref name="\"><fieldref name="\"><fieldref name="\">");
sbQuery.Append("</viewfields>");
sbQuery.Append("<queryoptions>");
sbQuery.Append("<viewattributes scope="\">");
sbQuery.Append("</queryoptions>");
sbQuery.Append("</mylistitemrequest>");



//the key here is the Scope= RecursiveAll will return all folders including subfolders.
//string query = "1";


XmlDocument doc = new XmlDocument();
doc.LoadXml(sbQuery.ToString());

XmlNode queryNode = doc.SelectSingleNode("//Query");
XmlNode viewNode = doc.SelectSingleNode("//ViewFields");
XmlNode optionNode = doc.SelectSingleNode("//QueryOptions");

XmlNode retNode = _lists.GetListItems(listname, string.Empty, queryNode, viewNode, string.Empty, optionNode, string.Empty);


return GetItemId(retNode);