none
Suggestion to make code more performant

    Question

  • Hi,

    The code below is very very slow on sharepoint foundation for around 100 sites and 1000 items. How can i make the same logic more performant, some better algorithm or better sharepoint api.

    Problem statement was to merge Customer List from all other sites into one view.

    Here is what its doing

    1 - Traverse  all the sites in a farm.

    2 - Checks for a list name "Customer" in it.

    3 - Take all the item from the list and add it in a datatable.

    4 - Pass the datatable to SPGridView as DataSource.

          private List<String> GetCustomerSites()
            {
                List<String> customerSites = new List<String>();
                SPFarm farm = SPFarm.Local;
                foreach (SPService objService in farm.Services)
                {
                    if (objService is SPWebService)
                    {
                        SPWebService oWebService = (SPWebService)objService;
    
                        foreach (SPWebApplication webApp in oWebService.WebApplications)
                        {
                            if (webApp.IsAdministrationWebApplication)
                                continue;
                            foreach (SPSite s in webApp.Sites)
                            {
                                customerSites.Add(s.Url);
                                s.Dispose();
                            }
                        }
                    }
                }
                return customerSites;
            }
    
         
         protected void LoadGrid()
            {
                try
                {
    				DataTable dt = new DataTable("table1");
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        List<String> listOfSiteUrl = GetCustomerSites();
    
                        foreach (String siteURL in listOfSiteUrl)
                        {
                            using (SPSite spSite = new SPSite(siteURL))
                            {
    
                                foreach (SPWeb spWeb in spSite.AllWebs)
                                {
    
                                    addLog("SPweb : " + spWeb.Url);
    
                                    SPList sourceList = null;
                                    try
                                    {
                                        sourceList = spWeb.Lists["CustomerList"];
                                    }
                                    catch { }
                                    
                                    if (sourceList == null)
                                        continue;
                                    SPView defView = sourceList.DefaultView;
                                    SPListItemCollection collListItems = sourceList.GetItems(
                                        "ID", "Title",  "Status", "Engineer");
    
                                    addLog("ItemCount : " + sourceList.ItemCount);
                                    try
                                    {
                                        DataTable dt1 = collListItems.GetDataTable();
                                        dt1.Columns.Add("Customer", typeof(string));
                                        dt1.Columns.Add("Temp", typeof(string));
                                        dt1.Columns["Temp"].ColumnMapping = MappingType.Hidden;
                                        foreach (DataRow r in dt1.Rows)
                                        {
    
                                            r["Temp"] = string.Format("{0}/{1}?ID={2}", spWeb.Url, sourceList.Forms[PAGETYPE.PAGE_DISPLAYFORM].Url, r["ID"]);
                                            r["Customer"] = spWeb.Title;
    
    
                                        }
                                        if (getCurrentLoggedInUserItemsOnly)
                                        {
                                            List<DataRow> toDel = new List<DataRow>();
                                            String userName = currentUser.Name.ToLower();//currentUser is currently Login user, the information from SPContext.Current
                                            string s = userName;
                                            int stop = s.IndexOf("\\");
                                            userName = (stop > -1) ? s.Substring(stop + 1, s.Length - stop - 1) : userName;
                                        
         
                                            foreach (DataRow dr in dt1.Rows)
                                            {
                                                string impEng = String.Empty;
                                                try
                                                {
                                                    impEng = dr["Engineer"].ToString();
                                                }
                                                catch { }
                                                if (!string.IsNullOrEmpty(impEng) && impEng.ToLower().Contains(userName))
                                                {
                                                    continue;
                                                }
                                                else
                                                    toDel.Add(dr);
    
                                            }
                                            for (int i = 0; i < toDel.Count; i++)
                                            {
                                                dt1.Rows.Remove(toDel[i]);
                                            }
    
                                        }
                                        if (getActiveStatusItemOnly)
                                        {
                                            List<DataRow> toDel = new List<DataRow>();
                                            
    
                                            foreach (DataRow dr in dt1.Rows)
                                            {
                                                //  bool isMyTicket = isMine(spWeb, dr, "Implementation_x0020_Engineer");
                                                string status = String.Empty;
                                                try
                                                {
                                                    status = dr["Status"].ToString();
                                                }
                                                catch { }
                                                if (!string.IsNullOrEmpty(status)
                                                    && (status.StartsWith("active")))
                                                {
                                                    continue;
                                                }
                                                else
                                                    toDel.Add(dr);
                                            }
                                            for (int i = 0; i < toDel.Count; i++)
                                            {
                                                dt1.Rows.Remove(toDel[i]);
                                            }
    
                                        }
    
                                        dt.Merge(dt1);
                                        addLog("DT Count = " + dt.Rows.Count);
                                    }
                                    catch { }
    
                                    spWeb.Close();
                                }
                            }
    
                            
                        }
                        dt.Columns["Temp"].ColumnMapping = MappingType.Hidden;
    
                        DataView dv = dt.DefaultView;
    
                        //dv.Sort = "title" + SortDirection.Ascending;
                        if (!string.IsNullOrEmpty(groupBy))
                            dv.Sort = groupBy;
    
                        grdResult.DataSource = dt.DefaultView;
    
                        BindDataGrid(grdResult, dt, groupBy);
    
                    });
                }
                catch (SPException ex)
                {
    
                    addLog(ex.Message);
                    addLog(ex.StackTrace);
    
                }
    
            }
            
            
            public void BindDataGrid(SPGridView grid, DataTable dt, string groupBy)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    if (c.ColumnName.ToLower().Equals("link title")
                        || c.ColumnName.ToLower().Equals("linktitle"))
                        continue;
    
    
                    if (c.ColumnName.ToLower().Equals("title"))
                    {
                        HyperLinkField linkField = new HyperLinkField();
    
                        linkField.HeaderText = c.ColumnName;
    
                        linkField.DataTextField = c.ColumnName;
    
                        linkField.DataNavigateUrlFields = new string[] { "Temp" };
    
    
                        grid.Columns.Add(linkField);
                        continue;
                    }
                    BoundField fldPropertyName = new BoundField();
                    fldPropertyName.HeaderText = c.ColumnName;
                    fldPropertyName.DataField = c.ColumnName;
                    fldPropertyName.HtmlEncode = false;
                    grid.Columns.Add(fldPropertyName);
                }
                if (!string.IsNullOrEmpty(groupBy) && !groupBy.ToLower().Equals("none"))
                {
                    grid.AllowGrouping = true;
                    grid.AllowGroupCollapse = true;
                    grid.GroupField = groupBy;
                    grid.GroupFieldDisplayName = groupBy;
    
                }
                grid.DataBind();
            }
    
       

    Thanks


    Nomi


    • Edited by itsnomihere Saturday, May 12, 2012 8:45 PM better Title
    Saturday, May 12, 2012 8:42 PM

Answers

  • Hi,

    SPSiteDataQuery returns no data when ViewFields property includes a multi-value field, when using ViewFields like:
    ViewFields = "<FieldRef Name='Title'/><FieldRef Name='taxonomy1' Nullable='true' Type='TaxonomyFieldTypeMulti'/>",

    The multi-value field will be empty in the query result. In this situation, you may need to use SPQuery or using FullTextSQLQuery object, here is a thread provide a code snippet:
    http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/059a597c-3aa9-4fc3-ac32-db5390cee784

    FullTextSQLQuery class:
    http://msdn.microsoft.com/en-us/library/microsoft.office.server.search.query.fulltextsqlquery.aspx

    Thanks,
    Qiao


    Qiao Wei

    TechNet Community Support

    Saturday, May 26, 2012 6:16 AM
    Moderator
  • I recommend Code like this :

    private List<SPSite> GetCustomerSites() { List<SPSite> customerSites = new List<SPSite>(); SPSecurity.RunWithElevatedPrivileges(delegate() { foreach (SPWebApplication wa in SPWebService.ContentService.WebApplications) { foreach (SPSite s in wa.Sites) { customerSites.Add(s); } } }); return customerSites; } protected void LoadGrid() { try { DataTable dt = new DataTable("table1"); List<SPSite> listOfSites = GetCustomerSites(); foreach (SPSite spSite in listOfSites) { foreach (SPWeb spWeb in spSite.AllWebs) { addLog("SPweb : " + spWeb.Url); SPList sourceList = null; try { sourceList = spWeb.GetList("/Lists/CustomerList");

    } catch { } if (sourceList == null) continue; SPQuery query = new SPQuery(); query.Query = "<Where>" + ((getCurrentLoggedInUserItemsOnly && getActiveStatusItemOnly) ? "<And>" : "") + (getCurrentLoggedInUserItemsOnly ? ("<Eq><FieldRef Name='Engineer' LookupId='TRUE'/><Value Type='User'>" + currentUser.ID + "</Value></Eq>") : "") + (getActiveStatusItemOnly ? "<BeginsWith><FieldRef Name='Status'/><Value Type='Text'>active</Value></BeginsWith>" : "") + ((getCurrentLoggedInUserItemsOnly && getActiveStatusItemOnly) ? "</And>" : "") + "</Where>"; query.ViewFields = string.Concat( "<FieldRef Name='ID' />", "<FieldRef Name='Title' />", "<FieldRef Name='Status' />", "<FieldRef Name='Engineer' />"); query.ViewFieldsOnly = true; SPListItemCollection collListItems = sourceList.GetItems(query); addLog("ItemCount : " + sourceList.ItemCount); try { DataTable dt1 = collListItems.GetDataTable(); dt1.Columns.Add("Customer", typeof(string)); dt1.Columns.Add("Temp", typeof(string)); dt1.Columns["Temp"].ColumnMapping = MappingType.Hidden; foreach (DataRow r in dt1.Rows) { r["Temp"] = string.Format("{0}/{1}?ID={2}", spWeb.Url, sourceList.Forms[PAGETYPE.PAGE_DISPLAYFORM].Url, r["ID"]); r["Customer"] = spWeb.Title; } dt.Merge(dt1); addLog("DT Count = " + dt.Rows.Count); } catch { } spWeb.Close(); } } dt.Columns["Temp"].ColumnMapping = MappingType.Hidden; DataView dv = dt.DefaultView; // continuation of your code .............




    Saturday, May 26, 2012 4:15 PM

All replies

  • Two things:

    1) You are now creating each site object twice, once to build the string collection of URLs and again to access the lists. Consider doing a single loop through the site collections.

    2) Take a look at SpSiteDataQuery. It can be used to get a list of lists from a single site collection in one request. Also do a general search on CAML Queries.

    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery


    Mike Smith TechTrainingNotes.blogspot.com

    • Proposed as answer by Huzefa Mala Sunday, May 13, 2012 5:42 AM
    • Marked as answer by itsnomihere Sunday, May 13, 2012 10:46 PM
    • Unmarked as answer by itsnomihere Thursday, May 17, 2012 11:13 PM
    Sunday, May 13, 2012 12:52 AM
  • Hi ,

                Use the SPSiteDataQuery class in your code ,It will increase performance . Please check below link

    http://milapshah.blogspot.com/2009/12/spsitedataquery-example.html

    Sunday, May 13, 2012 10:53 AM
  • Hi SPSiteDataQuery Experts,

    Kinda Stuck Here .. 

    How am I supposed to get multiuser field values using SPSiteDataQuery. Returning columns are empty ??

    Any hack ???

    Thanks


    Nomi


    • Edited by itsnomihere Thursday, May 17, 2012 11:01 PM format
    Thursday, May 17, 2012 11:00 PM
  • Hi,

    SPSiteDataQuery returns no data when ViewFields property includes a multi-value field, when using ViewFields like:
    ViewFields = "<FieldRef Name='Title'/><FieldRef Name='taxonomy1' Nullable='true' Type='TaxonomyFieldTypeMulti'/>",

    The multi-value field will be empty in the query result. In this situation, you may need to use SPQuery or using FullTextSQLQuery object, here is a thread provide a code snippet:
    http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/059a597c-3aa9-4fc3-ac32-db5390cee784

    FullTextSQLQuery class:
    http://msdn.microsoft.com/en-us/library/microsoft.office.server.search.query.fulltextsqlquery.aspx

    Thanks,
    Qiao


    Qiao Wei

    TechNet Community Support

    Saturday, May 26, 2012 6:16 AM
    Moderator
  • I recommend Code like this :

    private List<SPSite> GetCustomerSites() { List<SPSite> customerSites = new List<SPSite>(); SPSecurity.RunWithElevatedPrivileges(delegate() { foreach (SPWebApplication wa in SPWebService.ContentService.WebApplications) { foreach (SPSite s in wa.Sites) { customerSites.Add(s); } } }); return customerSites; } protected void LoadGrid() { try { DataTable dt = new DataTable("table1"); List<SPSite> listOfSites = GetCustomerSites(); foreach (SPSite spSite in listOfSites) { foreach (SPWeb spWeb in spSite.AllWebs) { addLog("SPweb : " + spWeb.Url); SPList sourceList = null; try { sourceList = spWeb.GetList("/Lists/CustomerList");

    } catch { } if (sourceList == null) continue; SPQuery query = new SPQuery(); query.Query = "<Where>" + ((getCurrentLoggedInUserItemsOnly && getActiveStatusItemOnly) ? "<And>" : "") + (getCurrentLoggedInUserItemsOnly ? ("<Eq><FieldRef Name='Engineer' LookupId='TRUE'/><Value Type='User'>" + currentUser.ID + "</Value></Eq>") : "") + (getActiveStatusItemOnly ? "<BeginsWith><FieldRef Name='Status'/><Value Type='Text'>active</Value></BeginsWith>" : "") + ((getCurrentLoggedInUserItemsOnly && getActiveStatusItemOnly) ? "</And>" : "") + "</Where>"; query.ViewFields = string.Concat( "<FieldRef Name='ID' />", "<FieldRef Name='Title' />", "<FieldRef Name='Status' />", "<FieldRef Name='Engineer' />"); query.ViewFieldsOnly = true; SPListItemCollection collListItems = sourceList.GetItems(query); addLog("ItemCount : " + sourceList.ItemCount); try { DataTable dt1 = collListItems.GetDataTable(); dt1.Columns.Add("Customer", typeof(string)); dt1.Columns.Add("Temp", typeof(string)); dt1.Columns["Temp"].ColumnMapping = MappingType.Hidden; foreach (DataRow r in dt1.Rows) { r["Temp"] = string.Format("{0}/{1}?ID={2}", spWeb.Url, sourceList.Forms[PAGETYPE.PAGE_DISPLAYFORM].Url, r["ID"]); r["Customer"] = spWeb.Title; } dt.Merge(dt1); addLog("DT Count = " + dt.Rows.Count); } catch { } spWeb.Close(); } } dt.Columns["Temp"].ColumnMapping = MappingType.Hidden; DataView dv = dt.DefaultView; // continuation of your code .............




    Saturday, May 26, 2012 4:15 PM