.NET 4.8 MVC薪资计算请求优化：需将500员工耗时从20-30分钟降至2分钟内

问题背景 #

我正在计算员工薪资，该请求包含多项计算逻辑，例如月度工时、总值班时长、总迟到时长、贷款余额、预支余额、加班计算、门禁时长计算等。针对500名员工，该请求耗时20至30分钟。

我拥有一个基于.NET 4.8、采用MVC架构且使用Oracle数据库的C# Web应用程序。请问我该如何将此请求优化至耗时少于2分钟？

现有实现代码 #

var items = dbMEMBERs.Where(m => {
  return
    (MemberType != 0 ? m.STATUS == MemberType : true) &&
    (MemberId != 0 ? m.ID == MemberId : true) &&
    (!string.IsNullOrEmpty(Gender) ? !string.IsNullOrEmpty(m.GENDER) && m.GENDER == Gender : true) &&
    (DEP_ID != 0 ? m.DEPT_ID == DEP_ID : true) &&
    ((IsDirectorSalary == 1 ? (m.STATUS == Constants.MemberTypes.Director ||
        m.STATUS == Constants.MemberTypes.Employee) : (m.STATUS == Constants.MemberTypes.Employee) || (m.STATUS == Constants.MemberTypes.Terminated && DbFunctions.TruncateTime(m.DischargeDate) >= firstDayOfMonth))) &&
        m.SALARY_TYPE == Constants.SalaryTypes.Monthely &&
    DbFunctions.TruncateTime(m.JOIN_DATE) <= DbFunctions.TruncateTime(lastDayOfMonth) &&
    (MemberStatus != 0 ? m.MemberStatus == MemberStatus : true)
});

ListMemSal = (from mem in items join dept in dbDEPARTMENTs on mem.DEPT_ID equals dept.DEP_ID
  //where dept.DEP_ID == DEP_ID
  from sal in dbSALARies.Where(m => m.MEMBER_ID == mem.ID && m.ISSUED_DATE == _date).DefaultIfEmpty() from Attendance in dbMemberAttendance.Where(m => m.MemberID == mem.MACHINEID && DbFunctions.TruncateTime(m.AttendanceDate) >= DbFunctions.TruncateTime(firstDayOfMonth) && DbFunctions.TruncateTime(m.AttendanceDate) <= DbFunctions.TruncateTime(lastDayOfMonth)).DefaultIfEmpty()
  //from overtime in dbOVERTIMEs.Where(m => m.MEMBER_ID == mem.ID && DbFunctions.TruncateTime(m.OVERTIME_DATE) >= DbFunctions.TruncateTime(firstDayOfMonth) && DbFunctions.TruncateTime(m.OVERTIME_DATE) <= DbFunctions.TruncateTime(lastDayOfMonth)).DefaultIfEmpty()
  // from gatepass in dbMEMBERS_GATEPASS.Where(m => m.MEMBER_ID == mem.ID && m.STATUS == 1 && m.PURPOSE == 2 && DbFunctions.TruncateTime(m.GatePassDate) >= DbFunctions.TruncateTime(firstDayOfMonth) && DbFunctions.TruncateTime(m.GatePassDate) <= DbFunctions.TruncateTime(lastDayOfMonth)).DefaultIfEmpty()
  select new ViewofSalary {
    MEMBER_ID = mem.ID,
      MACHINEID = mem.MACHINEID,
      MemberStatus = mem.STATUS,
      Full_Name = mem.FIRST_NAME + (!string.IsNullOrEmpty(mem.Relation) ? " " + mem.Relation + " " : " ") + mem.LAST_NAME,
      Salary = mem.CURRENT_SALARY ?? 0,
      Attendance_ID = Attendance.ID,
      Working_Hours = Attendance.PayableHours,
      ISSUED_DATE = Attendance.AttendanceDate,
      TAX = mem.TAX ?? 0,
      //OverTimeHours = overtime.OVERTIME_HOURS,
      //OverTime_Date = overtime.OVERTIME_DATE,
      // Rate = overtime.Rate ?? 0,
      // OverTimeID = overtime.ID,
      //OverTimeStatus = overtime.STATUS,                                       
      STATUS = Attendance.Status,
      SalaryStatus = sal.STATUS,
      AdvanceAmountSalary = sal.ADVANCE_AMOUNT ?? 0,
      LoanAmountSalary = sal.LOAN_AMOUNT ?? 0,
      SalaryAmountSalary = sal.AMOUNT,
      EOBI_AMOUNT = mem.EOBIAmount ?? 0,
      UNIFORM_EXPENCE_AMOUNT = sal.UNIFORM_EXPENCE_AMOUNT ?? 0,
      SAL_ID = sal.ID,
      // GatePassHours = gatepass.GatePassHours,
      // GatePassID = gatepass.ID,
      Date = Attendance.AttendanceDate,
      SavedSalary = sal.BASIC_SALARY ?? 0,
      Leaves = sal.LEAVES,
      Absents = sal.ABSENTS,
      workingDays = sal.WORKING_DAYS,
      WorkingHours = sal.WORKING_HOURS,
      Current_Sallary = sal.SALARY1 ?? 0,
      DeductionHours = sal.DeductionHours,
      deductamount = sal.DEDUCTION ?? 0,
      EOBI_AmountSalary = sal.EOBI_AMOUNT ?? 0,
      TaxSalary = sal.TAX ?? 0,
      OtherFineDeductionSalary = sal.OtherFineDeduction ?? 0,
      GatePassDeduction = sal.GatePassDeduction ?? 0,
      OverTimeHoursSalary = sal.OVERTIME_HOURS_SINGLE,

  }).OrderBy(m => m.AdvBonusDate).ToList();

if (ListMemSal != null && ListMemSal.Count > 0) {
  //  ListMemSal = ListMemSal.Where(m => (MemberId != 0 ? m.MEMBER_ID == MemberId : true) && (DEP_ID != 0 ? m.DEP_ID == DEP_ID : true)).ToList();
  if (Type == 4) {
    ListMemSal = ListMemSal.Where(m => m.SAL_ID == 0).OrderBy(m => m.Member_Code).ToList();
  }
  if (Type == 2) {
    ListMemSal = ListMemSal.Where(m => m.SalaryStatus == 1).OrderBy(m => m.Member_Code).ToList();
  }
  if (Type == 3) {
    ListMemSal = ListMemSal.Where(m => m.SalaryStatus >= 2).OrderBy(m => m.Member_Code).ToList();
  }
}

var SelectedGroupByMember = ListMemSal.Where(m => m.SAL_ID == 0).GroupBy(m =>
  new {
    m.MEMBER_ID,
      m.Allowances,
      m.DeductionDetail,
      m.MemberDutyTiming,
      m.SAL_ID,
      m.Salary,
  }).ToDictionary(n => n.Key, n => n.ToList());
foreach(var mem in SelectedGroupByMember)
{
  IList < MetaDataCompany > OListAllowances = ReturnMemberAllowancesList(mem.Key.Allowances);
  IList < MetaDataCompany > OListDeductions = ReturnMemberDeductionsList(mem.Key.DeductionDetail);
  decimal DutyHours = !string.IsNullOrEmpty(mem.Key.MemberDutyTiming) ? (decimal) ReturnMemberDutyHours(mem.Key.MemberDutyTiming) : 0;

  if (mem.Key.SAL_ID == 0)
  {
    var IncAmount = HRFunctions.MemberIncrementByDate(mem.Key.MEMBER_ID, lastDayOfMonth.AddDays(1), DateTime.Now);
    var Advance = ReturnMemberExtraBalance(mem.Key.MEMBER_ID, Constants.ExtrasTypes.Advance, firstDayOfMonth, lastDayOfMonth);
    var LoanBalance = ReturnMemberExtraBalance(mem.Key.MEMBER_ID, Constants.ExtrasTypes.Loan, firstDayOfMonth, lastDayOfMonth);
    var Loan = PerMonthInstallment(mem.Key.MEMBER_ID);
    var OtherFineDeduction = ReturnMemberExtraBalance(mem.Key.MEMBER_ID, Constants.ExtrasTypes.OtherFineDeduction, firstDayOfMonth, lastDayOfMonth);
    var Bonus = ReturnMemberExtraBalance(mem.Key.MEMBER_ID, Constants.ExtrasTypes.Bonus, firstDayOfMonth, lastDayOfMonth);
    var Arrears = ReturnMemberExtraBalance(mem.Key.MEMBER_ID, Constants.ExtrasTypes.Arrears, firstDayOfMonth, lastDayOfMonth);
    double GPHours = ReturnGatePassHoursByDates(mem.Key.MEMBER_ID, FirstDay, endOfMonth).GPHours;
    TimeSpan GPTime = TimeSpan.FromHours(GPHours);
    var OvertimeDetail = ReturnOvertimeHoursByDates(mem.Key.MEMBER_ID, FirstDay, endOfMonth);
    TimeSpan OTTime = OvertimeDetail.OTTime;
    double OvertimeHours = OTTime.TotalHours;
    var OvertimeMinutes = OTTime;
    var OvertimeAmount = ReturnOvertimeAmount(_date, OvertimeMinutes, mem.Key.Salary, mem.Key.MEMBER_ID);
    var medInstallment = ReturnMedicalPerMonthInstallment(mem.Key.MEMBER_ID);
    var medBalance = ReturnMedicalSum(mem.Key.MEMBER_ID, Constants.ExtrasTypes.Medical, FirstDay, lastDayOfMonth, medInstallment);

    ListMemSal.Where(m => m.MEMBER_ID == mem.Key.MEMBER_ID).Select(c => {
      c.Salary = (c.Salary - IncAmount);
      c.LoanBalance = LoanBalance;
      c.AdvanceSum = Advance;
      c.LoanSum = Loan;
      c.BonusSum = Bonus;
      c.OtherFineDeductionSum = OtherFineDeduction;
      c.ArrearsSum = Arrears;
      c.MedicalSum = medBalance;
      c.oListAllDeductions = OListDeductions;
      c.oListAllowances = OListAllowances;
      c.DutyHours = DutyHours;
      c.GatePassHours = GPHours;
      c.OvertimeMinutes = OvertimeMinutes;
      c.OverTimeHoursTotal = OvertimeHours;
      c.OvertimeAmount = OvertimeAmount;
      return c;
    }).ToList();
  } else {
    ListMemSal.Where(m => m.MEMBER_ID == mem.Key.MEMBER_ID).Select(c => {
      c.oListAllDeductions = OListDeductions;
      c.oListAllowances = OListAllowances;
      c.DutyHours = DutyHours;
      return c;
    }).ToList();
  }
}

核心优化措施 #

1. 消除N+1查询问题 #

代码中foreach循环内的ReturnMemberExtraBalance、ReturnGatePassHoursByDates等方法，若每个方法单独查询数据库，500名员工会产生至少500*N次数据库请求，这是性能瓶颈的核心：

• 将批量计算逻辑迁移到数据库层面：编写Oracle存储过程或视图，一次性计算所有员工的预支余额、贷款余额、门禁时长、加班时长等数据，返回批量结果。
• 或在EF中使用Include/ThenInclude预加载关联数据，通过Join一次性查询所有需要的汇总信息，避免循环内多次查询。

2. 优化数据库查询性能 #

• 避免对日期字段使用函数：原代码中DbFunctions.TruncateTime(m.AttendanceDate)会导致Oracle无法使用AttendanceDate上的索引，改为直接比较日期范围：

  // 替换TruncateTime写法，确保firstDayOfMonth为当月第一天00:00:00，lastDayOfMonth.AddDays(1)为下月第一天00:00:00
  m.AttendanceDate >= firstDayOfMonth && m.AttendanceDate < lastDayOfMonth.AddDays(1)
  

• 添加必要复合索引：针对查询中频繁使用的过滤字段和关联字段创建索引，例如：
  • MEMBERs(STATUS, SALARY_TYPE, JOIN_DATE, DEPT_ID)
  • SALARIES(MEMBER_ID, ISSUED_DATE)
  • MemberAttendance(MemberID, AttendanceDate)
• 提前过滤数据：将Type对应的过滤条件（如SAL_ID ==0、SalaryStatus ==1）整合到EF查询中，而非先拉取所有数据再在内存中过滤，减少传输到应用程序的数据量。

3. 优化内存处理逻辑 #

• 减少不必要的ToList()调用：多次调用ToList()会导致频繁内存分配和数据拷贝，尽量延迟到最后一步执行，让EF尽可能在数据库层面完成计算。
• 简化分组和更新逻辑：SelectedGroupByMember的分组可合并到EF查询中，或直接在数据库层面完成分组汇总；更新ListMemSal对象属性时，直接遍历分组后的员工数据更新，避免重复Linq操作开销。

4. 异步化处理 #

将数据库查询和计算逻辑改为异步执行，利用async/await提高资源利用率，避免线程阻塞：

var items = await dbMEMBERs.Where(...).ToListAsync();
var ListMemSal = await (from mem in items...).ToListAsync();

5. 批量计算替代循环 #

将循环中单个员工的计算改为批量处理：

• 一次性获取所有需计算的员工ID集合，调用一次数据库查询获取所有员工的增量、预支、贷款等数据，再通过内存映射关联到对应员工对象。
• 对于ReturnMemberAllowancesList这类基于配置的计算，一次性加载所有配置数据，在内存中通过员工ID匹配，避免多次调用方法查询。

6. 数据库层面迁移核心计算 #

薪资计算的核心逻辑（如加班时长汇总、门禁时长统计、贷款余额计算）适合放在数据库中完成，数据库擅长批量数据处理：编写存储过程接收月份参数，直接返回计算好的薪资明细，应用程序仅需调用存储过程并展示结果，大幅减少数据传输量。

内容的提问来源于stack exchange，提问作者Touba chouhan