Working with SharePoint Large List – Part 2 – Performance Review

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:

• Working with SharePoint Large List – Part 3 – How wonderful about SharePoint full text search
• Working with SharePoint Large List – Part 2 – Performance Review
• Working with SharePoint Large List – Part 1

Hoang Nhut Nguyen

Email: nhutcmos@gmail.com

Skype: hoangnhut.nguyen

Working with SharePoint Large List – Part 1

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&#8221;); //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’> Hotel</Value></Eq></Where>”; 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:

• Working with SharePoint Large List – Part 3 – How wonderful about SharePoint full text search
• Working with SharePoint Large List – Part 2 – Performance Review
• Working with SharePoint Large List – Part 1

Hoang Nhut Nguyen

Email: nhutcmos@gmail.com

Skype: hoangnhut.nguyen