ContentIterator - Iterate SPListeItem

If you have large set of content in the list and while accessing it using the CAML query, you might get the SPQueryThrottleException error or throttling limitation issue . To avoid such kind of situation, ContentIterator class can be used. This class implement a callback patter to divide the query, to process a single item at a time.

protected void TestIterator(object sender, EventArgs args)

 {   

  TotalItems = 0;     TotalExceptions = 0;     

  ContentIterator objContentIterator = new ContentIterator();

    SPQuery objSPQuery = new SPQuery();

     
   objSPQuery.Query = objString;
SPList objList = SPContext.Current.Web.Lists["Employees"];   

  iterator.ProcessListItems(objList,objSPQuery,         ProcessItem,         ProcessError     ); }  

public    bool ProcessError(SPListItem item, Exception e)  {      // process the error      
TotalExceptions++; 
return true;  } 

public void ProcessItem(SPListItem item) {      
TotalItems++;
//process the item. } 

Resource Throttling SharePoint 2010 - To Increase SharePoint Performance

Here is the throttling definition from Microsoft SharePoint Evaluation Guide.

“Through throttling settings, SharePoint Server 2010 provides a way for administrators to determine the level at which the server will go into throttling mode. Every five seconds, a job runs that checks server resources compared to configured levels. By default, Server CPU, Memory, Request in Queue, and Request Wait Time are monitored. After three unsuccessful checks, the server enters a throttling period and will remain in this state until a successful check is completed. Requests that were generated prior to the server’s entering throttling mode will be completed. This will, in theory, keep users from losing any current work when the server begins to throttle requests. Any new HTTP GET and Search Robot requests will generate a 503 error message and will be logged in the event viewer. While the server is in a throttling period, no new timer jobs will be started.”
A new feature in SharePoint Server 2010, resource throttling provides options for monitoring and throttling server resources and large lists for Web applications. This enables you to control resource utilization during peak usage and prevent user activity from negatively affecting server performance.
Resource Throttling allows the farm administrator to control the overall performance of a SharePoint farm. The biggest performance bottleneck in SharePoint sites is usually large lists, which either have numerous items in a list or have very large items in a list. Here, the rule is very simple – the larger the SQL query the slower the performance is. If you have 100 simultaneous users in a site, and 2 of them are contributing to large lists, all 100 users will experience very slow performance. The solution for this is Resource Throttling.
Using SharePoint Resource Throttling you can set the query limits, so the farm will refuse to execute the problematic user actions at certain times. You can set the “happy hour” for those queries to any time you want and instruct your users to contribute with those large lists ONLY at specific time.
To access Resource Throttling settings for your SharePoint site/farm, go to
Central Administration > Application Management and select the web application (in our example – SharePoint – 80 application) and from the ribbon select General Settings – Resource Throttling.

Central Administration Ribbon - Resource Throttling Configuration

Setting the Object Overriding and Administrative threshold is important here. Why? You can easily throttle queries and therefore improve performance, but in most cases you will still need to access, read and modify some objects from the throttled resources. It’s necessary to have a setting that will allow some selected users to contribute to the throttled resources and override the settings entered. In this way you will avoid often made requests  to the throttled resources, because only a selection of users (from our example) will have rights to perform huge queries.
To set this, ensure that the Object Model Override option is set to “Yes” and that List View Threshold for Auditors and Administrators is set to the maximum number  capable of showing all the content from your portal. The default value of 20000 is a sensible starting point but you will probably need to fine tune this.

Now, set the daily time window for the rest of the users who will also need to access the throttled resources. This may be the time out of  your working hours, so most users will be offline , or early morning, where the traffic is still low. To set this, look at Daily Time Window for Large Queries setting.

In my example, I’ll set the time window from 7 AM to 9 AM, so all early birds will have access to throttled queries

Thanks.

Sharepoint 2007 performance tips : Part 2 : fetching data from large lists

Fetching data from large lists while maintaining good performance and response times can be a challenging task.
One of the common coding mistakes is extensive use of collections like 'list.Items' which selects all items in the list with all fields resulting in bad performance.The following alternatives can be used instead to improve the performance in sharepoint 2007 while fetching data from lists:

Alternatives to SPList.Items

Poor 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.

SPFolder.Files.Count                                                                      SPFolder.ItemCount

SharePoint 2010 List Throttling

While I was searching for content for data optimization in case of large list and libraries, I found a new feature incorporated into SharePoint 2010 for putting limit on the amount of data to be retrieved from the servers. The feature is called List Throttling. 

What exactly it does is allow you to set a limit for how many rows of data can be retrieved for a list or library at any one time. As an example, you can consider a situation where user is having a list with 50,000 records and he creates a view to show all items in a single page, this could put unnecessary load on servers. List throttling ensures that such a request would not be allowed to execute. The hit on the server is alleviated, and the user gets a nice little message that says sorry, we can’t retrieve all of the data you requested because it exceeds the throttle limit for this list.  

List throttling is managed through web application. Click on Application Management --> Manage Web Applications.  Select the web application you want to configure list throttling, then in the ribbon click on the General Settings drop down and select the Resource Throttling item. Below figure shows how to enable list throttling in a web application.

Below is the list of options and their details:

List View Threshold – Specifies the maximum number of items that a database operation can involve at one time. Operations that exceed this limit are prohibited.  The default value is 5,000 and minimum value is 2,000.

Object Model Override – If you choose to allow object model override, users to whom you grant sufficient permission can override the List View Threshold programmatically for particular queries. This option needs to be enabled in order to enable super users to retrieve items through the object model, up to the amount defined in the List query size threshold for auditors and administrators.

List View Threshold for Auditors and Administrators – Specifies the maximum number of items that an object model database query can involve at one time for users to whom you grant sufficient permissions through Security Policy. This is a special limit for “super users”.  It is important to understand that this DOES NOT allow these super users to see more items in a list view.  This property is used in conjunction with the Allow object model override property described below.  That means that if Object Model Override is set to “Yes”, then these super users can retrieve up to the number of items set in this property, but only via the object model.  The way you become a “super user” is a farm admin creates a web application policy for you that grants you rights to a permission level that includes either the Site Collection Administrator and/or Site Collection Auditor rights. 

List View Lookup Threshold – Specifies the maximum number of Lookup, Person/Group, or workflow status fields that a database query can involve at one time.

Daily Time Window for Large Queries – Specifies a daily time window when large queries can be executed. Specify a time outside of working hours for this window because large queries may cause excessive server load.

List Unique Permissions Threshold - Specifies the maximum number of unique permissions that a list can have at one time.

Backward-Compatible Event Handlers - Turn on or off backward-compatible event handlers for this Web application. If this is turned off, users cannot bind document libraries to backward-compatible event handlers.   HTTP Request Monitoring and Throttling - Turn on or off the HTTP request throttling job. This job monitors front-end Web server performance, and in the event of HTTP request overload, rejects (throttles) low priority requests. You can turn request throttling off to allow services such as Search to run uninterrupted on the farm, however, in an unthrottled farm experiencing overload, front-end Web servers become less responsive, and may stop working.

In order to retrieve information using the object model in order to retrieve up to the number of items specified in the List query size threshold for auditors and administrators property, there is a property you need to set in your query object.  The property is called QueryThrottleMode and it applies to the SPQuery and SPSiteDataQuery classes.  You simply set this property to override and then use your class instance to query. 

using (SPSite objSPSite = new SPSite("http://<Your Site>")) 

{

using (SPWeb objSPWeb = objSPSite.RootWeb)

{

SPList objSPList = objSPWeb.Lists["Test List"];

SPQuery objSPQuery = new SPQuery();

objSPQuery.QueryThrottleMode = SPQueryThrottleOption.Override;

            SPListItemCollection objColl = objSPList.GetItems(objSPQuery);

            //Your custom logic here for processed data

}

}

How to improve Sharepoint Performance

Regardless of the framework and methodology you use in trying to understand your SharePoint performance issues, isolate their root causes, and resolve the problems, you need baseline data that reflects acceptable performance against which you compare data that reflects poor performance. You can obtain a baseline set of performance data either right after your initial deployment, during periods of acceptable performance, or from best-practice recommendations. If you have no basis for comparison, then you can use Microsoft published performance recommendations for various measurements, available at 

http://technet.microsoft.com/en-us/library/cc262787.aspx

Issue	Possible Root Causes	Possible Resolutions
High disk I/O activity on SQL Server	Large list operations, timer jobs, SQL maintenance tasks, backup, indexing, inadequate RAM, high I/O databases such as temp tlog, search, and content, placed on same disk or slow disks.	Separate temp and search databases in multiple files across high I/O disk volumes, increase RAM, use dedicated disks for transaction logs, defragment, and re-index databases weekly.
SQL blocking/locking	NIC configuration, large list operations, indexing/crawling jobs.	Do not use SharePoint Team Services Administration (STSADM). Use SQL backup, DPM, Litespeed, or SQL 2008 with compression, ensure fill factor is set to 70% on content databases, enforce 100GB growth limit.
Overall slow page loads	Compression not enabled. Caching not enabled or not configured. Large pages. Redundant SQL trips, underlying network issues.	Enable caching and compression, check page load times, and examine SQL queries and round trips, check NIC for Broadcom 5708 Chimney issues.
Long time to load full page	Improper SharePoint object handling in custom code, slow links, SQL blocking, timer jobs, Web part caching not enabled.	Resolve back-end bandwidth and response issues, dispose of objects properly, use 64-bit hardware or configure memory pool limits, delay downloading core.js.
Poor list performance	Large lists >2,000–3,000 items in a level. No indexing on lists. Underlying SQL Server issues. Too many columns.	Index on one or more columns, ensure SQL Server performance, keep fewer than 2,000–3,000 items in a level.
Long crawl and index times or indexing causing sluggishness	Large data volumes require long index times, no dedicated index target.	Block with robots.txt, offload crawling/indexing to dedicated front-end server.
LDAP operations (such as authentication and user operations) causing usage spikes	Low bandwidth, remote domain controller, large profile imports.	Increase bandwidth, use Kerberos, optimize profile importing.
Backup taking too long	Using STSADM, other SQL conditions such as blocking.	Use Microsoft Data Protection Manager (DPM) or SQL 2008 with compression.
IIS out of memory conditions	Application pool and worker process recycling, improper object handling, not enough RAM, poor load balancing architecture.	Use IIS overlapped recycling, use 64-bit hardware.
Additional Resources
http://technet.microsoft.com/en-us/library/cc678870.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx
http://technet.microsoft.com/en-us/library/cc263261.aspx
http://technet.microsoft.com/en-us/library/cc262067(TechNet.10).aspx
http://technet.microsoft.com/en-us/library/cc901593.aspx
http://go.microsoft.com/fwlink/?LinkID=105623&clcid=0x409
http://support.microsoft.com/kb/271509
http://support.microsoft.com/kb/298475
http://technet.microsoft.com/en-us/library/cc298550.aspx
http://support.microsoft.com/kb/942861
http://code.msdn.microsoft.com/SPDisposeCheck
http://technet.microsoft.com/en-us/library/cc298550.aspx
http://technet.microsoft.com/en-us/library/cc678870.aspx
http://technet.microsoft.com/en-us/library/cc298466.aspx
http://go.microsoft.com/fwlink/?LinkID=105580&clcid=0x409
http://technet.microsoft.com/en-us/library/cc261810.aspx
http://support.microsoft.com/kb/827754
http://technet.microsoft.com/en-us/library/cc901593.aspx
http://msdn.microsoft.com/en-us/library/aa720391(VS.71).aspx
http://technet.microsoft.com/en-us/library/cc737679.aspx

Dump SharePoint Lists Data into SQL Server Tables using SqlBulkCopy

In one of our projects it is required to clean and normalize SharePoint Data and dump that data into SQL server so that any third party can consume that data for reporting purposes. For that we created a timer job that runs every night and does the job....  Here is the approach that we took for implementation


Create a SharePoint Timer job  and add these two references

using System.Data;
using System.Data.SqlClient;



class DatabaseDump : SPJobDefinition
    {
        // No Argument Constructor (required for the system internal operation)
        public DatabaseDump()
        {
        }

        public DatabaseDump(string JobName, SPWebApplication objWebApplication)
            : base(JobName, objWebApplication, null, SPJobLockType.Job)
        {

        }


        public override void Execute(Guid targetInstanceId)
        {

            SPWebApplication currentWebApplication = this.WebApplication;
            try
            {
                using (SPSite currentSite = currentWebApplication.Sites[0])
                {
                    //todo:call export program
                    ExportData(currentSite.Url);
                 }
            }
            catch (Exception ex)
            {
                //exception handling here
            }
        }
    }


Step 2 : Create a function for ExportData

public void ExportData(string SpSiteURL)
        {
            DataTable SourceTable;
            DataTable UserMappingTable;
            DataRow dr;
            DataRow drMapping;
            string tableName;
            string connectionString;
            SPQuery query;
            try
            {
                connectionString = "SQL SERV ER CONNECTION STRING";
                if (!string.IsNullOrEmpty(connectionString))
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        if (connection != null)
                        {
                            using (SPSite objSite = new SPSite(SpSiteURL))
                            {
                                using (SPWeb currentWeb = objSite.OpenWeb())
                               {
                                    //first of all get the data of all lists in the system
                                    foreach (SPList DFList in currentWeb.Lists)
                                    {
                                        try
                                        {
                                            if (!DFList.Hidden)
                                            {
                                                foreach (SPView view in DFList.Views)
                                                {
//we created a view for the data to be exported but default view can also be used here

                                                    if (view.Title =="Reports View")
                                                    {
                                                        SourceTable = DFList.GetItems(view).GetDataTable();
                                                        if (SourceTable != null)
                                                        {
                                                            //clean table name if required
                                                            tableName = CleanupSQLOBjectName(DFList.Title);
                                                        //drop and create new table first or if you don't want to do this then truncate it first                  
                                       CreateTable(connection, tableName, SourceTable);
                                                            BulkCopy(connection, tableName, SourceTable);
                                                        }
                                                        else
                                                        {
                                                            //no data in this list
                                                            //we need to truncate the existing data for this list now
                                                            tableName = CleanupSQLOBjectName(DFList.Title);
                                                            TruncateTable(connection, tableName);

                                                        }
                                                        break;
                                                    }
                                                }
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                          //handle exception here
                                        }
                                    }
        }




   private string CleanupSQLOBjectName(String SQLObjectName)
        {
            StringBuilder tempSb = new StringBuilder(SQLObjectName);
            tempSb = tempSb.Replace("/", "_");
            //these are two types of dashes
            tempSb = tempSb.Replace("–", "_");
            tempSb = tempSb.Replace("-", "_");
            tempSb = tempSb.Replace("(", "_");
            tempSb = tempSb.Replace(")", "_");
            tempSb = tempSb.Replace(" ", "_");
            tempSb = tempSb.Replace("_x0020_", "_");
            tempSb = tempSb.Replace("_x002f_", "_");
            tempSb = tempSb.Replace("__", "_");
            if (tempSb.Equals("group"))
            {
                tempSb.Replace("group", "group_1");//"Group" is a reserved word
            }
            return tempSb.ToString();
        }




private void CreateTable(SqlConnection connection, string tableName, string[] columns, string[] dataTypes)
        {
            StringBuilder strBuilder;
        
            SqlCommand cmd;

            //first drop this table
            DropTable(connection, tableName);


            //now we need to create table
            strBuilder = new StringBuilder();
            strBuilder.Append("CREATE TABLE " + tableName + " (");

            for (int i = 0; i < columns.Length; i++)
            {
                strBuilder.Append("[" + columns[i] + "] " + dataTypes[i] + ", ");
            }

            strBuilder.Append(")");


            cmd = new SqlCommand();

            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strBuilder.ToString();
            cmd.ExecuteNonQuery();
        }

   private void BulkCopy(SqlConnection connection, string tableName, DataTable SourceTable)
        {
            //now copy the data
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
            {
                //Set destination table name
                //to table previously created.
                bulkcopy.DestinationTableName = tableName;
                bulkcopy.WriteToServer(SourceTable);
            }
        }


This will update all the data of sharepoint lists in a SQL server database. You can add code to normalize lookup columns, multivalue columns etc and can also extend it to create child tables for lookup data..

Let us know if somebody require any assistance in extending this code.