[SQL] Required suggestion to improve performance of C# methods

I am working on code optimization task below is detail code


public class UserPrivileges
{
#region Properties

public int UserId { get; set; }
public int NPSiteId { get; set; }
public string UserName{ get; set; }
public bool IsPlaybackPortalAdministrator { get; set; }
public Dictionary<int, LegacySitePrivileges> Permissions { get; set; }
public string LoginName { get; set; }
public string OSLogin { get; set; }
public int ITenantID { get; set; }

#endregion

#region Constructor

public UserPrivileges()
{
IsPlaybackPortalAdministrator = false;
Permissions = new Dictionary<int, LegacySitePrivileges>();
}

#endregion

#region Override Methods

public override string ToString()
{
StringBuilder sb = new StringBuilder();

sb.AppendLine(string.Format("User ID {0}, User Name {1} , NPSiteId {2}, Is Playback Portal Administrator {3}", UserId,
UserName,
0,
IsPlaybackPortalAdministrator));

if (Permissions.Count > 0)
{
foreach (KeyValuePair<int, LegacySitePrivileges> permission in Permissions)
{
if (permission.Value != null)
{
int dbId = permission.Value.DbId;
string dbName = permission.Value.DbName;

//Permissions
if (permission.Value.Permissions != null)
{
foreach (KeyValuePair<int, PrivilegesResource> per in permission.Value.Permissions)
{
if (per.Value.IsApproved)
{
sb.AppendLine(string.Format("DB Id {0} DB Name {1} " +
"- Permission Id {2} - Permission Value to display {3}",
dbId,dbName, per.Value.ResourceId, per.Value.DisplayValue));
}
}
}
}
}
}
else
{
sb.AppendLine("No Permissions");
}

return sb.ToString();

}

public string UsersPrivilegesToolTip
{
get
{
// User Login name and OS login
return string.Format("Login Name: {0}{1}OS Login: {2}", LoginName, Environment.NewLine, OSLogin);
}
}
#endregion

public void CopyPrivileges(Dictionary<int, LegacySitePrivileges> privileges)
{
foreach(KeyValuePair<int, LegacySitePrivileges> privilegeToCopy in privileges)
{
//Permissions
foreach (KeyValuePair<int, PrivilegesResource> perm in privilegeToCopy.Value.Permissions)
{
Permissions[privilegeToCopy.Key].Permissions[perm.Key].IsApproved = perm.Value.IsApproved;
}
}
}
}

public class LegacySitePrivileges
{
#region Properties

public string DbName { get; set; }
public int DbId { get; set; }

public PBPVersion Version { get; set; }

public string DBAdminConnectionString { get; set; }

public Dictionary<int, PrivilegesResource> Permissions { get; set; }

#endregion

#region Constructor

public LegacySitePrivileges()
{
Permissions = new Dictionary<int, PrivilegesResource>();
}

#endregion

public override string ToString()
{
return string.Format("DB Name {0} DB ID {1}", DbName, DbId);
}
}

public class PrivilegesResource
{
#region Properties
public int ResourceId{ get; set; }
public string DisplayValue { get; set; }
public string Description { get; set; }
public bool IsApproved { get; set; }
#endregion
}

public class PrivilegesResourcePermission : PrivilegesResource
{
#region Constructor
public PrivilegesResourcePermission()
{
IsApproved = false;
}
#endregion
}

public class LegacySitePrivilegesId
{
public int DBId { get; set; }
public int OriginalSiteId { get; set; }

public LegacySitePrivilegesId(int dbId, int siteId)
{
DBId = dbId;
OriginalSiteId = siteId;
}

public override string ToString(){
return DBId + "_" + OriginalSiteId;
}
}


Below are impacted methods

namespace WebPortal.Client.Entities {
​

public class UsersPrivilegesDataManager
{

public Dictionary<int, UserPrivileges> LoadPrivileges(User user)
{
const string methodName = CLASS_NAME + ":" + "LoadPrivileges";
m_User = user;
m_log.InfoFormat("{0} . Start to load the privileges ", methodName);
var userPrivileges = LoadNimUsersWithPrivilegesToPlaybackPortal();
CreateUserPrivilegesObjects(userPrivileges);
ResolvePrivileges(userPrivileges);
m_OriginalUserEntity = ConvertUserPrivilegesToUserEntity(userPrivileges);
m_log.DebugFormat("{0} . Leaving the method with {1} number of user privileges", methodName, userPrivileges.Count);
return userPrivileges;
}


private Dictionary<int, UserPrivileges> LoadNimUsersWithPrivilegesToPlaybackPortal()
{
const string methodName = CLASS_NAME + ":" + "LoadNIMUsersWithPrivilegesToPlaybackPortal";
m_log.DebugFormat("{0} . Start to load the list of users who have access to the playback portal", methodName);

var playbackPortalAdministratorsIDs = new List<int>();
var playbackPortaNotTenantUserIDs = new List<int>();
var users = new Dictionary<int, UserPrivileges>();

//Call the query service to get all the privileges
m_log.DebugFormat("{0} . Calling the query service in order to get users who have privileges to the playback portal", methodName);
string error;
var userDataSet = m_queryServiceClient.GetWebPortalNPGroupAndUserData(out error);
var isBusinessUser = m_User.IsBusinessUser
&& !m_User.IsSuperUser
&& !m_User.IsWebPortalAdministrator;

//m_log.DebugFormat("{0} . m_User.IsBusinessUser: {1}. m_User.IsSuperUser: {2}. m_User.IsWebPortalAdministrator: {3}. isBusinessUser: {4} ",methodName
// m_User.IsBusinessUser, m_User.IsSuperUser, m_User.IsWebPortalAdministrator, isBusinessUser, );

int tenantID = 0;
int tenantIdFotBussinessTenant=0;
int userId;
bool isTenant = false;

if (m_User.IsBusinessUser)
{
foreach (DataRow row in userDataSet.Tables[0].Rows)
{
userId = int.Parse(row["iUserID"].ToString());
tenantID = int.Parse(row["iTenantID"].ToString());
//find the tenantId for Business Tenant user
if ((userId == m_User.UserId) && (tenantID != -1))
{
tenantIdFotBussinessTenant = tenantID;
isTenant = true;
}
}
}

if (userDataSet != null && userDataSet.Tables.Count > 0)
{
foreach (DataRow row in userDataSet.Tables[0].Rows)
{
var isPlaybackPortalAdministrator = false;
userId = int.Parse(row["iUserID"].ToString());
tenantID = int.Parse(row["iTenantID"].ToString());

//Handle playback portal administrators / business users
var privilegeId = int.Parse(row["iPrivilegeId"].ToString());


m_log.DebugFormat("{0} . Calling the query service in order to get users who have privileges to the playback portal", methodName);
if (privilegeId == 2 ||
(isBusinessUser && privilegeId == 3))
{
isPlaybackPortalAdministrator = true;
}


//Add the user to the list
if (!users.ContainsKey(userId))
{
users.Add(userId, new UserPrivileges
{
UserId = int.Parse(row["iUserId"].ToString()),
UserName = row["FormattedName"].ToString(),
NPSiteId = int.Parse(row["iSiteId"].ToString()),
LoginName = row["nvcLoginName"].ToString(),
OSLogin = row["nvcOsLogin"].ToString(),
ITenantID = int.Parse(row["iTenantID"].ToString())

});
m_log.InfoFormat("{0} . Adding User ID {1} UserName {2} NPSiteID {3} Is Admin {4} LoginName {5} OSLogin {6} Tenant ID {7}",
methodName,
int.Parse(row["iUserId"].ToString()),
row["FormattedName"],
int.Parse(row["iSiteId"].ToString()),
isPlaybackPortalAdministrator,
row["nvcLoginName"],
row["nvcOsLogin"],
int.Parse(row["iTenantID"].ToString()));
}
if (isTenant == true)
{
//remove all users without tenantID
if ((tenantID != tenantIdFotBussinessTenant)&& (users.ContainsKey(userId)))
{
playbackPortaNotTenantUserIDs.Add(userId);
}
}

//Manage the list of playback portal administrators
if (users.ContainsKey(userId))
{
if (isPlaybackPortalAdministrator)
{
playbackPortalAdministratorsIDs.Add(userId);
}
}
}
}

//Remove the administrators from this list since they have privileges to the entire playback portal
foreach (int administrator in playbackPortalAdministratorsIDs)
{
m_log.DebugFormat("{0} . Removing the User ID {1} from the list of users since he's playback portal administrator or business user", methodName, administrator);
users.Remove(administrator);
}

foreach (var notTenantUserID in playbackPortaNotTenantUserIDs)
{
m_log.DebugFormat("{0} . Removing the User ID {1} from the list of users since he's not tenant", methodName, notTenantUserID);
users.Remove(notTenantUserID);
}
m_log.DebugFormat("{0} . Leaving the method", methodName);

return users;
}


private void CreateUserPrivilegesObjects(Dictionary<int, UserPrivileges> users)
{
const string methodName = CLASS_NAME + ":" + "CreateUserPrivilegesObjects";
m_log.DebugFormat("{0} . Start to create the UserPrivileges object according to the permissions the user have", methodName);

//Foreach user from the users who have privilegs to playback portal
foreach (KeyValuePair<int, UserPrivileges> user in users)
{
foreach (LegacySitePrivileges sitePrivilege in m_legacySites)
{

int legacySitePrivId = sitePrivilege.DbId;
if (!users[user.Key].Permissions.ContainsKey(legacySitePrivId))
{
users[user.Key].Permissions.Add(legacySitePrivId, new LegacySitePrivileges
{
DBAdminConnectionString = string.Empty,//sitePrivilege.DBAdminConnectionString,
Version = sitePrivilege.Version,
DbName = sitePrivilege.DbName,
DbId = sitePrivilege.DbId
});
}

foreach (KeyValuePair<int, PrivilegesResource> permissions in sitePrivilege.Permissions)
{
bool isApprove = user.Value.IsPlaybackPortalAdministrator;

legacySitePrivId = sitePrivilege.DbId;
if (!users[user.Key].Permissions[legacySitePrivId].Permissions.ContainsKey(permissions.Value.ResourceId))
{
users[user.Key].Permissions[legacySitePrivId].Permissions.Add(permissions.Value.ResourceId, new PrivilegesResource
{
DisplayValue = permissions.Value.DisplayValue,
Description = permissions.Value.Description,
ResourceId = permissions.Value.ResourceId,
IsApproved = isApprove
});
}
}

m_log.DebugFormat("{0}. Created {1} User Objects for LegacySite.Count = {2} ", methodName,users.Count,m_legacySites.Count);
}
}



m_log.DebugFormat("{0} . Leaving the method", methodName);
}

private void ResolvePrivileges(Dictionary<int, UserPrivileges> users)
{
const string methodName = CLASS_NAME + ":" + "ResolvePrivileges";
m_log.DebugFormat("{0} . Load the playback portal privileges each user have", methodName);
//Call the query service in order to get the list of users and their permissions to the playback portal
m_log.DebugFormat("{0} . Calling the query service in order to get the list of users and their permissions to the playback portal", methodName);
string error;
DataSet userDataSet = m_queryServiceClient.GetWebPortalUserConfiguration(out error);

//Go over the results
if (userDataSet != null && userDataSet.Tables.Count > 0)
{
foreach (DataRow row in userDataSet.Tables[0].Rows)
{
int userId = int.Parse(row["iUserID"].ToString());
if (users.ContainsKey(userId))
{
int dbID = int.Parse(row["iDbID"].ToString());
int resourceId = int.Parse(row["iResourceID"].ToString());

LegacySitePrivileges outLegacySitePrivileges;

if (users[userId].Permissions.TryGetValue(dbID, out outLegacySitePrivileges))
{
PrivilegesResource outPrivilegesResource;

if (outLegacySitePrivileges.Permissions.TryGetValue(resourceId, out outPrivilegesResource))
{
outPrivilegesResource.IsApproved = true;
m_log.DebugFormat("{0} . Adding Permission to User ID {1}. Resource ID {2}",
methodName,
userId,
resourceId);
}
}
else
{
m_log.WarnFormat("{0} . no legacySiteId:{1} for user Id{2}", methodName, dbID, userId);
}
}
else
{
m_log.DebugFormat("{0} . User ID {1} doesn't exits in the list of users that have permissions to the playback portal. He's permissions won't be added",methodName, userId);
}

}
}
m_log.DebugFormat("{0} . Leaving the method ", methodName);
}

private List<UserEntity> ConvertUserPrivilegesToUserEntity(Dictionary<int, UserPrivileges> updatedUsersPrivilegs)
{
const string methodName = CLASS_NAME + ":" + "ConvertUserPrivilegesToUserEntity";
m_log.DebugFormat("{0} . Convert the user privileges into the user entity object in order to save it in the DB", methodName);

var usersEntity = new List<UserEntity>();

//Foreach user from the user privileges list
foreach (KeyValuePair<int, UserPrivileges> user in updatedUsersPrivilegs)
{
//Make sure we're not working on Playback portal administrator
if (!user.Value.IsPlaybackPortalAdministrator)
{
var per = new List<SitePermissions>();
//For each site
foreach (var legacySite in user.Value.Permissions.Values)
{
var permissions = new List<Permissions>();

if (legacySite.Permissions != null && legacySite.Permissions.Count > 0)
{
var permissionList = legacySite.Permissions.Values;

foreach (var permission in permissionList)
{
if (permission.IsApproved)
{
permissions.Add((Permissions) permission.ResourceId);
}
}
}

var sitePermissions = new SitePermissions
{
DbName = legacySite.DbName,
DbId = legacySite.DbId,
Permissions = permissions.ToArray()
};

per.Add(sitePermissions);

m_log.InfoFormat("{0} . Adding site permission to User ID {1} Site ID {2} Site Name {3} Version {4}",
methodName,
user.Key,
sitePermissions.DbId,
legacySite.DbName,
legacySite.Version == PBPVersion.Nim
? "Nim" : legacySite.Version == PBPVersion.NicePerform
? "Np" : "Cls");
}

var userEntity = new UserEntity{
UserId = user.Value.UserId,
UserName = user.Value.UserName,
NpSiteId = user.Value.NPSiteId,
IsFullAccessToAllLegacyDatabases = user.Value.IsPlaybackPortalAdministrator,
SitePermissions = per.ToArray()
};
usersEntity.Add(userEntity);
m_log.Debug(string.Format("{0} . {1}",methodName,PrintUserEntity(userEntity)));
}
}

m_log.DebugFormat("{0} . Leaving the method", methodName);
return usersEntity;
}

private List<UserEntity> ConvertUserPrivilegesToUserEntityForSite(int dbId, Dictionary<int, UserPrivileges> updatedUsersPrivilegs, List<int> usersWithGroups, List<int> usersWithoutGroups)
{
const string methodName = CLASS_NAME + ":" + "ConvertUserPrivilegesToUserEntity";
m_log.DebugFormat("{0} . Convert the user privileges into the user entity object in order to save it in the DB", methodName);

var usersEntity = new List<UserEntity>();

//Foreach user from the user privileges list
foreach (KeyValuePair<int, UserPrivileges> user in updatedUsersPrivilegs)
{
//Make sure we're not working on Playback portal administrator
if (!user.Value.IsPlaybackPortalAdministrator)
{
var per = new List<SitePermissions>();
var canSeeTabResourceId = (int) Permissions.CanSeeTab;
var legacySite = user.Value.Permissions.FirstOrDefault().Value;
var permissionList = legacySite.Permissions;
var permissions = (from permission
in permissionList.Values.Where(p => p.ResourceId != canSeeTabResourceId)
where permission.IsApproved
select (Permissions) permission.ResourceId).ToList();

legacySite = user.Value.Permissions[dbId];
permissionList = legacySite.Permissions;

var originalCanSeeTab = permissionList.ContainsKey(canSeeTabResourceId)
&& permissionList[canSeeTabResourceId].IsApproved;
var toAssignCanSeeTab = (originalCanSeeTab && !usersWithoutGroups.Contains(user.Key))
|| usersWithGroups.Contains(user.Key);
if (toAssignCanSeeTab)
{
permissions.Add(Permissions.CanSeeTab);
}

SitePermissions sitePermissions = new SitePermissions
{
DbId = legacySite.DbId,
Permissions = permissions.ToArray()
};

per.Add(sitePermissions);
m_log.InfoFormat("{0} . Adding site permission to User ID {1} Site ID {2} Site Name {3} Version {4}",
methodName,
user.Key,
sitePermissions.DbId,
legacySite.DbName,
legacySite.Version == PBPVersion.Nim
? "Nim" : legacySite.Version == PBPVersion.NicePerform
? "Np" : "Cls");


var userEntity = new UserEntity
{
UserId = user.Value.UserId,
UserName = user.Value.UserName,
NpSiteId = user.Value.NPSiteId,
IsFullAccessToAllLegacyDatabases = user.Value.IsPlaybackPortalAdministrator,
SitePermissions = per.ToArray()
};
usersEntity.Add(userEntity);
m_log.Debug(string.Format("{0} . {1}", methodName, PrintUserEntity(userEntity)));
}
}

m_log.DebugFormat("{0} . Leaving the method", methodName);
return usersEntity;
}

#endregion
}


}

Currently we have around 5000+ users and 5 million of privilege records exist. ResolvePrivileges() method dealing 5 million records and its took around 6000ms to execute. I have tried refracting this method my Parrallel.Foreach loop but its come around 4500ms to execute.I have tried to club all 3 methods LoadNimUsersWithPrivilegesToPlaybackPortal(),CreateUserPrivilegesObjects(),ResolvePrivileges() into store procedure but there are 2 systems are hosted on different server so its not possible as only way to reach its by WCF so that option also not possible. Only ResolvePrivileges() is directly deals with db which has under my authority. I am thinking of converting Dictionalty "Dictionary<int, UserPrivileges> users" into json or xml and try to operate from store procedure.Please suggest a better idea if you can have.

Continue reading...