This project has moved. For the latest updates, please go here.
2

Closed

Slow Manage Users page with 1000's of users

description

The user management list and search is very slow when there are 1000's of users in the membership database. See what changes can be done to enhance the performance.
Closed Aug 4 at 3:59 PM by ccoulson
Fixed in 1.3.6

comments

Peter111 wrote Aug 27, 2014 at 4:56 PM

I have this code for FABUsersView.cs that makes it faster:
using System;
using System.Collections;
using System.Data;
using System.Web.UI;
using System.Web.Security;
using Microsoft.SharePoint;
using System.Collections.Generic;

namespace Visigo.Sharepoint.FormsBasedAuthentication
{
    /// <summary>
    /// Specific data of a user in SharePoint
    /// </summary>
    class SPUserData
    {
        public string title { get; set; }
        public DateTime? modified { get; set; }
        public DateTime? created { get; set; }
    }

    /// <summary>
    /// Data source for the User Management user display view. Gets all FBA users and adds basic membership info 
    /// </summary>
    class FBAUsersView : DataSourceView
    {
        private FBADataSource _owner;

        public FBAUsersView(FBADataSource owner, string viewName) : base(owner, viewName) 
        {
            _owner = owner;
        }

        protected override IEnumerable ExecuteSelect(DataSourceSelectArguments selectArgs)
        {
            if (!Utils.IsProviderConfigured())
                return null;

            SPSite site = SPContext.Current.Site;
            string provider = Utils.GetMembershipProvider(site);
            if (provider == null)
                return null;

            string yes = LocalizedString.GetString("FBAPackFeatures", "Yes");
            string no = LocalizedString.GetString("FBAPackFeatures", "No");

            SPWeb web = site.RootWeb;
            SPList list = web.SiteUserInfoList; //web.Lists[SPUtility.GetLocalizedString("$Resources:userinfo_schema_listtitle", "core", web.Language)];

            SPQuery query = new SPQuery();
            query.Query = string.Format(
                "<Where>" +
                    "<And>" +
                        "<Eq><FieldRef Name='ContentType' /><Value Type='Text'>Person</Value></Eq>" +
                        "<Contains><FieldRef Name='Name' /><Value Type='Text'>{0}</Value></Contains>" +
                    "</And>" +
                "</Where>", provider);
            query.ViewFields = "<FieldRef Name='Name' /><FieldRef Name='LinkTitle' /><FieldRef Name='Email' /><FieldRef Name='Modified' /><FieldRef Name='Created' />";

            Dictionary<string, SPUserData> sp_users = new Dictionary<string,SPUserData>();
            try
            {
                var items = list.GetItems(query);
                if (items != null)
                {
                    foreach (SPListItem item in items)
                    {
                        var baseusername = item["Name"].ToString();
                        var decodedusername = Utils.DecodeUsername(baseusername);
                        if (baseusername != decodedusername)
                        {
                            sp_users.Add(
                                decodedusername.ToLower(),
                                new SPUserData
                                {
                                    created = (DateTime)item["Created"],
                                    modified = (DateTime)item["Modified"],
                                    title = (string)item["Title"]
                                });
                        }
                    }
                }
            }
            catch (Exception ex) 
            {
                Utils.LogError(ex);
                return null;
            }

            DataTable users = new DataTable();
            users.Columns.Add("Name", typeof(string));
            users.Columns.Add("Modified", typeof(DateTime));
            users.Columns.Add("Created", typeof(DateTime));
            users.Columns.Add("Title", typeof(string));
            users.Columns.Add("Email", typeof(string));
            users.Columns.Add("Active", typeof(string));
            users.Columns.Add("Locked", typeof(string));
            users.Columns.Add("LastLogin", typeof(DateTime));
            users.Columns.Add("IsInSharePoint", typeof(string));
            users.Columns.Add("NonProviderName", typeof(string));

            int totalRecords = 0;
            int sp_users_count = sp_users.Count;
            int sp_users_found = 0;

            users.BeginLoadData();
            foreach (MembershipUser memberuser in Utils.BaseMembershipProvider(site).GetAllUsers(0,100000, out totalRecords))
            {
                DateTime? modified = null;
                DateTime? created = null;
                string title = null;
                string isinsharepoint = no;
                if (sp_users_found < sp_users_count)
                {
                    if (sp_users.ContainsKey(memberuser.UserName.ToLower()))
                    {
                        var sp_user = sp_users[memberuser.UserName.ToLower()];
                        modified = sp_user.modified;
                        created = sp_user.created;
                        title = sp_user.title;
                        isinsharepoint = yes;
                        sp_users_found++;
                    }
                }
                var row = users.LoadDataRow(new object[]
                {
                    memberuser.UserName,
                    modified,
                    created,
                    title,
                    memberuser.Email,
                    memberuser.IsApproved ? yes : no,
                    memberuser.IsLockedOut ? yes : no,
                    memberuser.LastLoginDate,
                    isinsharepoint
                }, false);
            }
            users.EndLoadData();

            DataView dataView = new DataView(users);
            if (selectArgs.SortExpression != String.Empty)
                dataView.Sort = selectArgs.SortExpression;

            dataView.RowFilter = "";
            if (_owner.SearchText.Length > 0)
                dataView.RowFilter = string.Format("Name LIKE '%{0}%' OR Email LIKE '%{0}%' OR Title LIKE '%{0}%'", _owner.SearchText);

            return (IEnumerable)dataView;
        }      
    }
}

ccoulson wrote Aug 28, 2014 at 4:24 AM

Thanks Peter! I'll look at adding this to the next release!

netzenos wrote Dec 4, 2014 at 4:00 PM

This works perfect to me. Thanks

bakjular wrote Dec 16, 2014 at 10:46 AM

Hi, may I ask if this is related to Sharepoint adding automatically the columns "modified by" or "created by" ? My problem is that it takes an enormous time to add a person to the specific row - I usually copy about 1000 lines of Excel into a SharePoint list. With the old SharePoint 2010, this was a question of 5 minutes at most. With SharePoint 2013, it takes about a minute to add 20 lines...it is incredibe. The company I work for has, however, thousands of employees. Would changing of the code as suggested above help to fix our problem too? Thank you!

Regards
Bob

Peter111 wrote Dec 16, 2014 at 12:19 PM

Hi Bob, no, this code just reads data.
To make inserts faster you can try to use the Access approach. See a post here:
http://sharepointroot.com/2013/10/18/quick-data-load-for-sharepoint-2013-custom-list/
Regards, Peter

rw5207 wrote Jan 12, 2016 at 8:03 PM

We have successfully implemented this code in our environments and it fixes the performance problem with UsersDisp.aspx quite nicely. It doesn't address all the issues, but we get 2-3 seconds response time on 40K test users in our aspnetdb.

We use a SQL procedure to load the aspnetdb users from our legacy system, so we can load 35K users in about 2 minutes, should anyone need to do this.

Russ

bparker wrote Jan 30 at 6:57 PM

Thanks Peter. A note for others, this code replaces file "FBAUsersView.cs", not FABUsersView.cs :)