如何优化数据表中10行的加载？ [英] How to optimize the loading of 10 rows in a datatable?
问题描述
我正在服务器端处理数据表，它从数据库中获取大约1000个伪/通用用户数据，并且每页加载10行。我现在编写代码的方式，显示前10行至少需要2分钟。
我调试了它以找到它的原因花了这么长时间是因为我得到了每个用户的角色并在数据表上显示它。当我注释掉获取角色部分并使用空字符串和List时，数据表加载速度很快。但我必须得到角色，而我却很难弄清楚如何去做。有人可以帮我解决这个问题吗？到目前为止，我一直在努力研究SQL部分并且没有成功。
我尝试过的事情：
I am working on a server side processing datatable which gets about 1000 fake/generic users data from the database and loads 10 rows per page. The way I have written my code right now, it takes at least 2 minutes just to show the first 10 rows.
I have debugged it to find the reason it is taking so long is because I am getting the Roles for each user and showing it on the datatable. When I comment out the getting the roles part and use an empty string and List, the datatable loads fast. But I have to get the Roles and I having a hard time to figure out how to do it. Can someone please help me out in this matter? I have been struggling with the SQL part for the longest time and no success so far.
What I have tried:
private async Task<IEnumerable<UserViewModel>> GetUserData(ApplicationDbContext db)
{
var users = db.Users;
var list = await GetUsers(db, users);
var vmlist = list.Select(item => new UserViewModel()
{
ID = item.ID,
Username = item.Username,
FirstName = item.FirstName,
LastName = item.LastName,
Email = item.Email,
TaxID = item.TaxID,
TaxIdHash = item.TaxIdHash,
IV = item.IV,
TaxIDEncrypted = item.TaxIDEncrypted,
//RoleList = new List<string>(),
//Roles = "",
// This is the part giving me issues
RoleList = RoleRepository.GetRolesByUserId(UserManager, item.ID, false),
Roles = string.Join(",", RoleRepository.GetRolesByUserId(UserManager, item.ID, false).ToArray()),
});
return vmlist;
}
我在这个UserGrid方法中调用GetUserData方法。
I am calling the GetUserData method in this UserGrid method.
public async Task<ActionResult> UserGrid(DataTablesViewModel param)
{
using (var db = new Infrastructure.Data.ApplicationDbContext())
{
db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
var data = await GetUserData(db);
IQueryable<UserViewModel> filteredUsers = data.AsQueryable();
List<UserViewModel> displayedTasks = filteredUsers.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
int count = filteredUsers.Count();
foreach (var item in displayedTasks){
if (!String.IsNullOrEmpty(item.TaxID) && item.IV != null && item.TaxIDEncrypted != null)
{
if (item.RoleList.Any(x => x == UserRoles.Applicant))
{
string ssndecrypt = GenericHelper.DecryptPortalTaxID(item.IV, item.TaxIDEncrypted);
string ssn = (ssndecrypt.Length == 11 ? ssndecrypt.Substring(7, 4) : ssndecrypt);
item.TaxID = (ssndecrypt.Length == 11 ? String.Join("", item.TaxID.Substring(0, 7), ssn) : ssn);}
}
else
{
item.TaxID = String.Empty;
}
item.Delete = item.RoleList.Intersect(UserRoles.CanDelete).Count() > 0 ?
"<a class=\"delete\" data-url=\"" + Url.Action("DeleteUser", "Users") + "\" data-userid=\"" + item.ID + "\"data-username=\"" + item.Username + "\" href=\"#\">Delete User</a>"
: String.Empty;
}
var result = GridHelper.GetData(displayedTasks);
var json = Json(new
{
sEcho = param.sEcho,
iTotalRecords = count,
iTotalDisplayRecords = count,
aaData = result
},
JsonRequestBehavior.AllowGet);
return await Task.FromResult(json);
}
我将需要RoleList，因为我稍后会使用它来解密加密的SSN并显示其最后4个数字。
如果需要，这是UserViewModel。
I will need the RoleList because I use it later for decrypting the encrypted SSN and showing its last 4 numbers.
This is the UserViewModel if needed.
public UserViewModel(IList<string> roles, ApplicationUser user)
{
ID = user.Id;
Username = user.UserName;
Email = user.Email;
RoleList = roles.ToList(); ;
Roles = string.Join(",", RoleList.ToArray());
IsActive = user.IsActive;
AllowRoleChanges = !RoleList.Intersect(UserRoles.FinalConsumers).Any();
AssignableRoles = HousingServices.Core.Models.UserRoles.CanCreate.Select(x => new SelectListItem { Text = x, Value = x }).ToList();
if (RoleList.Contains(UserRoles.Caseworker))
{
AssignableRoles.Add(new SelectListItem { Value = UserRoles.Caseworker, Text = UserRoles.Caseworker });
}
RoleIDs = RoleList.Select(x => x).ToList();
..........
}
}
private async Task<IEnumerable<dynamic>> GetUsers(ApplicationDbContext db, IDbSet<ApplicationUser> users)
{
IQueryable<ApplicationUser> userlist = users;
<pre>var hsuserlist = userlist.Join(db.EliteHousingServicesUsers, u => u.Id, hu => hu.UserId, (u, hu) => new
{
ID = u.Id,
Username = u.UserName,
FirstName = hu.FirstName,
LastName = hu.LastName,
Email = u.Email,
TaxID = "XXX-XX-XXXX",
Active = (u.IsActive ? "Yes" : "No"),
TaxIdHash = hu.TaxIDHash,
IV = hu.IV,
TaxIDEncrypted = hu.TaxIDEncrypted,
IsActive = u.IsActive,
Status = (u.AccessFailedCount >= MaxFailedAccessAttemptsBeforeLockout ?
"<a class=\"unlock\" data-url=\"" + unlockUrl + "\" data-userid=\"" + u.Id + "\"data-username=\"" + u.UserName + "\" href=\"#\">Unlock</a>"
: "<a class=\"reset\" data-url=\"" + resetUrl + "\" data-userid=\"" + u.Id + "\"data-username=\"" + u.UserName + "\" href=\"#\">Reset Password</a>"),
});
推荐答案
好吧，我不知道Roles与你的代码有什么关系，但你可以通过重构数据库来节省大量的时间。必须解密SSN才能获得最后4位数字，这是字符串中最不安全的部分。所以，不要解密它。将最后4列作为单独的列存储并检索它而不是完整的SSN。您可以跳过整个解密操作，节省大量时间。
Well, I don't know what the Roles have to do with your code, but you can save a ton of time by restructuring your database a bit. The SSN has to be decrypted to get the last 4 digits, the least secure part of the string. So, don't decrypt it. Store the last 4 as a separate column and retrieve that instead of the full SSN. You get to skip the entire decrypt operation, saving yourself a ton of time.
这篇关于如何优化数据表中10行的加载？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
