Tag Archive: SharePoint Large List

In last post Working with SharePoint Large List – Part 1, I just showed to you about how to get SharePoint data list by many ways:

– By Object Module with For/Each loop

– By SPQuery

– By DataTable

– By Web Services

– By Portal SiteMap Provider

And in this post, we will review the performance of each via tested result in the charts. It’s also compare Add and Delete Items between Indexed fields, No Indexed Fields and By ID field.

1. Add Item

2. Delete Item

3. Data Retrieval

Data Retrieval with under 1500 Items


Data Retreival around 100,000 Items


Retrieval Data with Indexed Column vs No Index


Data Retrieval By ID vs By Indexed Column

Related Article:
Hoang Nhut Nguyen
Email: nhutcmos@gmail.com
Skype: hoangnhut.nguyen

Microsoft performed performance testing against Microsoft® SharePoint® Server 2007 and 2010to determine the performance characteristics of large SharePoint lists under different loads and modes of operation.

The same is true for calendars, contacts, and other interfaces. They are all just customized versions of the basic SharePoint list, also referred to as an SPList. The individual items in the list are referred to as list items generally, or an SPListItem in an SPListItemCollection from the Office SharePoint Server 2007 object model.

Each test consisted of retrieving a subset of data from the list using one of a number of different data access methods.
The code samples included in the following sections are intended to show the process used to conduct tests. The code may not com

1. By For/Each loop

Object model (OM) was used to retrieve the list into an SPList object. Each item in the list was then enumerated with a For/Each loop until items were found that matched the search criteria.

//get the site 

SPSite curSite = New SPSite(http://myPortal”) ;

//get the web

SPWeb curWeb = curSite.OpenWeb();

//get our list

SPList curList = curWeb.Lists(New Guid(“myListGUID”));

//get the collection of items in the list

SPListItemCollection curItems = curList.Items;

//enumerate the items in the list

foreach(SPListItemcurItem in curItems )

/////do some comparison in here to see if it’s an item we need


2. By SPQuery

The OM was used to create an SPQuery object that contained the query criteria. That object was then used to against an instance of the list in a SPList object. The results of the query were returned by calling the GetItems method on the SPList object.

//get the site 

SPSite curSite = New SPSite(http://myPortal”);

//get the web

SPWeb curWeb = curSite.OpenWeb();

//create our query

SPQuery curQry = New SPQuery();

//configure the query

curQry.Query = “<Where><Eq><FieldRef Name=’Expense_x0020_Category’/><Value Type=’Text’>


curQry.RowLimit = 100;

//get our list

SPList curList = curWeb.Lists(New Guid(“myListGUID”));

//get the collection of items in the list

SPListItemCollection curItems = curList.GetItems(curQry);

//enumerate the items in the list

foreach (curItem SPListItem in curItems)

/////do something with each match


3. By DataTable

This is one of two methods that test using a Microsoft ADO.NET DataTable to work with the data. In this case an instance of the list is obtained with an SPList object. The data from it is then retrieved into a DataTable by calling the GetDataTable() method on the Items property —for example, SPList.Items.GetDataTable(). The DataTable’s DefaultView has a property called RowFilter that was then set to find the items. To keep the methodology between data access methods consistent.

//get the siteSPSite curSite = New SPSite(http://myPortal&#8221;);

//get the web

SPWeb curWeb = curSite.OpenWeb();

//get our list

SPList curList = curWeb.Lists(New Guid(“myListGUID”));

//get the item in a datatable

DataTable dt = curList.Items.GetDataTable();

//get a dataview for filtering

DataView dv = dt.DefaultView;

dv.RowFilter = “Expense_x0020_Category=’Hotel'”;

//enumerate matches

for (int rowNum = 0; i<=dv.Count – 1; i ++)

////do something with each match


4. By Web Service

The Lists Web service was used to retrieve the data. A Collaborative Application Markup Language (CAML) query was created and submitted along with the list identifier, and an XML result set was returned from the Lists Web service

//create a new xml doc we can use to create query nodesXmlDocument xDoc = New XmlDocument();

//create our query node

XmlNode xQry = xDoc.CreateNode(XmlNodeType.Element, “Query”, “”) ;

//set the query constraints

xQry.InnerXml = “<Where><Eq><FieldRef Name=’Expense_x0020_Category’/><Value Type=’Text’>Hotel</Value></Eq></Where>”;

//create the Web service proxy that is mapped to Lists.asmx

using (ws = wsLists.Lists())


//configure it

ws.Credentials = System.Net.CredentialCache.DefaultCredentials;

ws.Url = http://myPortal/_vti_bin/lists.asmx&#8221;;

//create the optional elements

XmlNode xView = xDoc.CreateNode(XmlNodeType.Element, “ViewFields”, “”);

XmlNode xQryOpt = xDoc.CreateNode(XmlNodeType.Element, “QueryOptions”, “”);

//query the server

XmlNode xNode = ws.GetListItems(“myListID”, “”, xQry, xView, “”, xQryOpt, “”);

//enumerate returned items

for ( int nodeCount = 0; i<=xNode.ChildNodes.Count – 1; i++)

/////do something with each match



4. By Portal SiteMap Provider

One approach to retrieving list data in Office SharePoint Server 2007 that’s not very well known is the use of the PortalSiteMapProvider class. It was originally created to help cache content for navigation. However, it also provides a nice automatic caching infrastructure for retrieving list data. The class includes a method called GetCachedListItemsByQuery that was used in this test. This method first retrieves data from a list based on an SPQuery object that is provided as a parameter to the method call. The method then looks in its cache to see if the items already exist. If they do, the method returns the cached results, and if not, it queries the list, stores the results in cache and returns them from the method call.
The following sample code was used for this method. Note that it is different from all of the previous examples in that you cannot use the PortalSiteMapProvider class in Windows forms applications.

//get the current webSPWeb curWeb = SPControl.GetContextWeb(HttpContext.Current)

//create the query

Dim curQry AsNew SPQuery()

curQry.Query = “<Where><Eq><FieldRef Name=’Expense_x0020_Category’/><Value Type=’Text’>Hotel</Value></Eq></Where>”;

//get the portal map provider stuff

PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider

PortalWebSiteMapNode pNode = TryCast(ps.FindSiteMapNode(curWeb.ServerRelativeUrl), PortalWebSiteMapNode);

//get the items

pItems = ps.GetCachedListItemsByQuery(pNode, “myListName_NotID”, curQry, curWeb);

//enumerate all matches

foreach (PortalListItemSiteMapNodepItem in pItems)

/////do something with each match


Related Article:
Hoang Nhut Nguyen
Email: nhutcmos@gmail.com
Skype: hoangnhut.nguyen
%d bloggers like this: