c#优化代码

Question

我有以下函数返回一个Model。这需要时间如果有2000 employees，它需要3-4 minutes to return data。实际上我想要optimize this function。我已经做了下面代码中包含的一些事情，但仍然花费了很多时间。

using (var ctx = new ApplicationDbContext(schemaName))
{
                List<Employee> list = new List<Employee>();
                Employee mod = new Employee();
                var data = ctx.Employee.Where(a => a.Company == comp && a.Status == Live)
                    .Select(a => new
                    {
                        Id = a.Id,
                        Code = a.Code,
                        FName = a.FName,
                        DateOfJoining = a.DateOfJoining,
                        Category = a.Category,
                        Department = a.Department,
                        Designation = a.Designation;
                    })
                    .ToList();
                var loadValues = ctx.CValue.Where(c => c.Company == comp).ToList();
                foreach (var item in data)
                {
                    mod = new Employee();
                    mod.Id = item.Id;
                    mod.Code = item.Code;
                    mod.FName = item.FName;
                    mod.DateOfJoining = item.DateOfJoining;
                    mod.Category = item.Category;
                    mod.Designation = item.Designation;
                    mod.Department = item.Department;
                    int designation = (item.Designation == null) ? 0 : item.Designation;
                    int department = (item.Department == null) ? 0 : item.Department;
                    if (designation != 0)
                        mod.DesignationString = loadValues.Where(c => c.CompanyId == comp && c.Id == designation).Select(c => c.ComboValue).FirstOrDefault();
                    if (department != 0)
                        mod.DepartmentString = loadValues.Where(c => c.Company == comp && c.Id == department).Select(c => c.ComboValue).FirstOrDefault();
                    list.Add(mod);
                }
                return list;
            }
}

我认为是foreach循环占用了时间。有什么解决办法吗?如何优化上述代码?

Answer 1

我已经将代码优化为一个linq查询。我想那会更快。

using (var ctx = new ApplicationDbContext(schemaName))
{
    var loadValues = ctx.CValue.Where(c => c.Company == comp).ToList();
    return ctx
        .Employee
        .Where(a => a.Company == comp && a.Status == Live)
        .Select(item => new Employee
        {
            Id = item.Id,
            Code = item.Code,
            FName = item.FName,
            DateOfJoining = item.DateOfJoining,
            Category = item.Category,
            Designation = item.Designation,
            Department = item.Department,
            DesignationString = loadValues.Where(c => c.CompanyId == comp && c.Id == item.Designation ?? 0).FirstOrDefault(c => c.ComboValue);
            DepartmentString = loadValues.Where(c => c.Company == comp && c.Id == item.Department ?? 0).FirstOrDefault(c => c.ComboValue);
        });
}

Answer 2

您可以通过使用快速查找数据结构来优化嵌套循环 (Linq的Where在foreach中):

var loadValues = (from c in ctx.CValues
                    where c.Company == comp
                    select c).ToLookup(x => Tuple.Create(x.CompanyId, x.ID), 
                                        x => x.ComboValue);
foreach (var item in data)
{
    // your same code goes here 
    // then
   if (designation != 0)
      mod.DesignationString = loadValues[Tuple.Create(comp, designation)].FirstOrDefault();
   if (department != 0)
      mod.DepartmentString = loadValues[Tuple.Create(comp, department)].FirstOrDefault();
    list.Add(mod);
}

我看不出还有什么地方可以优化。

值得注意的是，comp上的过滤器在if块中是冗余的，因为它已经是初始查询的一部分。

Answer 3

下面的剪辑可能会花费相当长的时间(请务必进行性能度量)

var loadValues = ctx.CValue.Where(c => c.Company == comp).ToList();
....
if (designation != 0) mod.DesignationString = loadValues.Where(c => c.CompanyId == comp && c.Id == designation).Select(c => c.ComboValue).FirstOrDefault();
if (department != 0) mod.DepartmentString = loadValues.Where(c => c.Company == comp && c.Id == department).Select(c => c.ComboValue).FirstOrDefault();

，因为loadValues是List，总是按顺序搜索。因此，根据loadValues的大小，这个列表中可能有很多搜索。此外，您不需要对CompanyId进行比较，因为您已经在loadValues的定义中通过CompanyId进行了过滤。

为了加快速度，你可以使用Lookup。

var loadValues = ctx.CValue.Where(c => c.Company == comp).ToLookup(x=> x.Id);
if (designation != 0 && loadValues.Contains(designation)) mod.DesigationString = loadValues[designation].Select(c => c.ComboValue).FirstOrDefault();
if (department != 0 && loadValues.Contains(department)) mod.DepartmentString = loadValues[department].Select(c => c.ComboValue).FirstOrDefault();

或者，当您搜索Id时，它应该是唯一的，您也可以创建一个简单的Dictionary<int, string>。

var loadValues = ctx.CValue.Where(c=>c.Company == comp).ToDictionary(x=> x.Id, y=> y.ComboValue);
 if (designation != 0) mod.DesigationString = loadValues.ContainsKey(designation) ? loadValues[designation] : String.Empty;
 if (department != 0) mod.DepartmentString = loadValues.ContainsKey(department) ? loadValues[department] : String.Empty;

Answer 4

我会这样写:

using (var ctx = new ApplicationDbContext(schemaName))
{
    var company = ctx.Companies.FirstOrDefault(e => e.ID == 42);
    if(company == null)
        throw new Exception();
    var empl = company.Employees.Where(e=> e.Status == Live).Select(e=>
        new EmployeeInfo{
            ID = e.ID,
            FName = e.FName,
            //TODO
            DesignationString = e.Designation != null ? e.Designation.ComboValue : string.Empty,
            //TODO
        });
    return empl.ToList();
}

但我假设你有DB结构与适当的外键和指定是列引用表CValue。没有嵌套循环，没有双倍的内存分配(正如我在评论中提到的)，所有东西都将使用Join获得，这比Where子句要快得多。

EDIT您需要返回与映射实体不同的模型列表。看看我的例子的第8行。如果您尝试运行.Select(e => new Employee())，您将得到您在注释中提到的错误。(更多信息:实体不能在LINQ to Entities查询中构造)

但是如果您返回List<EmployeeInfo>，您可以在应用程序的上层使用该列表的任何地方工作，您可以添加更多元素(它们可以是DB查询以外的其他方法的结果，例如XML导入)，并且您将独立于实际的DB映射类。

你可以在这里找到更多关于应用程序组成的信息:https://softwareengineering.stackexchange.com/questions/240704/confused-about-layered-application-development.