php - Laravel 减慢了上千个数据
问题描述
Laravel 减慢了上千个数据，如果我helper在循环中调用上千个数据的函数foreach，它会变慢并且需要大量时间来加载，我尝试分块数据仍然是同样的问题。
我的helper代码如下，如果我helper在控制器中调用该函数，加载数据有时需要 20 分钟。它真的放慢了速度。代码对于编辑器来说非常大，所以我将代码粘贴到下面的 pastebin url 中。
控制器代码如下。
<?php
public function cal(Request $request){
$start_date = $request->start_date;
$end_date = $request->end_date;
$start_date =\Carbon\Carbon::parse($start_date);
$end_date = \Carbon\Carbon::parse($end_date);
$expenses = GeneralHelper::total_expenses(\Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$other_expenses = GeneralHelper::total_savings_interest(\Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$payroll = GeneralHelper::total_payroll(\Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$other_income = GeneralHelper::total_other_income(\Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$interest_paid = GeneralHelper::loans_total_paid_item('interest', \Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$fees_paid = GeneralHelper::loans_total_paid_item('fees', \Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$penalty_paid = GeneralHelper::loans_total_paid_item('penalty', \Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$loan_default = GeneralHelper::loans_total_default(\Carbon\Carbon::parse($request->start_date), \Carbon\Carbon::parse($request->end_date));
$operating_expenses = $expenses + $payroll;
$operating_profit = $fees_paid + $interest_paid + $penalty_paid + $other_income;
$gross_profit = $operating_profit - $operating_expenses - $other_expenses;
$net_profit = $gross_profit - $loan_default;
//build graphs here
$monthly_net_income_data = array();
$monthly_operating_profit_expenses_data = array();
$monthly_other_expenses_data = array();
if (isset($request->end_date)) {
$date = \Carbon\Carbon::parse($request->end_date);
} else {
$date = date("Y-m-d");
}
$start_date1 = date_format(date_sub(date_create($date),
date_interval_create_from_date_string('1 years')),
'Y-m-d');
$start_date2 = date_format(date_sub(date_create($date),
date_interval_create_from_date_string('1 years')),
'Y-m-d');
$start_date3 = date_format(date_sub(date_create($date),
date_interval_create_from_date_string('1 years')),
'Y-m-d');
for ($i = 1; $i < 14; $i++) {
$d = explode('-', $start_date1);
//get loans in that period
$o_profit = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$o_profit = $o_profit + GeneralHelper::loan_paid_item($key->id, 'interest',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'fees',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'penalty', $key->due_date);
}
$o_profit = round($o_profit + OtherIncome::where('year', $d[0])->where('month',
$d[1])->sum('amount'), 2);
$o_expense = Expense::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->sum('amount');
foreach (Payroll::where('year', $d[0])->where('month',
$d[1])->get() as $key) {
$o_expense = $o_expense + GeneralHelper::single_payroll_total_pay($key->id);
}
$o_expense = round($o_expense, 2);
$ot_expense = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$ot_expense = $ot_expense + ($key->principal - GeneralHelper::loan_total_paid($key->id));
}
$ot_expense = round($ot_expense, 2);
if ($i == 1 or $i == 13) {
$ext = ' ' . $d[0];
} else {
$ext = '';
}
$n_income = round(($o_profit - $o_expense - $ot_expense), 2);
array_push($monthly_net_income_data, array(
'month' => date_format(date_create($start_date1),
'M' . $ext),
'amount' => $n_income
));
//add 1 month to start date
$start_date1 = date_format(date_add(date_create($start_date1),
date_interval_create_from_date_string('1 months')),
'Y-m-d');
}
for ($i = 1; $i < 14; $i++) {
$d = explode('-', $start_date2);
//get loans in that period
$o_profit = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$o_profit = $o_profit + GeneralHelper::loan_paid_item($key->id, 'interest',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'fees',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'penalty', $key->due_date);
}
$o_profit = round($o_profit + OtherIncome::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->sum('amount'), 2);
$o_expense = Expense::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->sum('amount');
foreach (Payroll::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->get() as $key) {
$o_expense = $o_expense + GeneralHelper::single_payroll_total_pay($key->id);
}
$o_expense = round($o_expense, 2);
$ot_expense = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$ot_expense = $ot_expense + ($key->principal - GeneralHelper::loan_total_paid($key->id));
}
$ot_expense = round($ot_expense, 2);
if ($i == 1 or $i == 13) {
$ext = ' ' . $d[0];
} else {
$ext = '';
}
$n_income = round(($o_profit - $o_expense - $ot_expense), 2);
array_push($monthly_operating_profit_expenses_data, array(
'month' => date_format(date_create($start_date2),
'M' . $ext),
'profit' => $o_profit,
'expenses' => $o_expense
));
//add 1 month to start date
$start_date2 = date_format(date_add(date_create($start_date2),
date_interval_create_from_date_string('1 months')),
'Y-m-d');
}
for ($i = 1; $i < 14; $i++) {
$d = explode('-', $start_date3);
//get loans in that period
$o_profit = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$o_profit = $o_profit + GeneralHelper::loan_paid_item($key->id, 'interest',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'fees',
$key->due_date) + GeneralHelper::loan_paid_item($key->id, 'penalty', $key->due_date);
}
$o_profit = round($o_profit + OtherIncome::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->sum('amount'), 2);
$o_expense = Expense::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->sum('amount');
foreach (Payroll::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month',
$d[1])->get() as $key) {
$o_expense = $o_expense + GeneralHelper::single_payroll_total_pay($key->id);
}
$o_expense = round($o_expense, 2);
$ot_expense = 0;
foreach (Loan::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('status',
'disbursed')->get() as $key) {
$ot_expense = $ot_expense + ($key->principal - GeneralHelper::loan_total_paid($key->id));
}
foreach (SavingTransaction::where('branch_id', session('branch_id'))->where('year', $d[0])->where('month', $d[1])->where('type',
'interest')->get() as $key) {
$ot_expense = $ot_expense + $key->amount;
}
$ot_expense = round($ot_expense, 2);
if ($i == 1 or $i == 13) {
$ext = ' ' . $d[0];
} else {
$ext = '';
}
$n_income = round(($o_profit - $o_expense - $ot_expense), 2);
array_push($monthly_other_expenses_data, array(
'month' => date_format(date_create($start_date3),
'M' . $ext),
'expenses' => $ot_expense
));
//add 1 month to start date
$start_date3 = date_format(date_add(date_create($start_date3),
date_interval_create_from_date_string('1 months')),
'Y-m-d');
}
$monthly_net_income_data = json_encode($monthly_net_income_data);
$monthly_operating_profit_expenses_data = json_encode($monthly_operating_profit_expenses_data);
$monthly_other_expenses_data = json_encode($monthly_other_expenses_data);
$start_date = $request->start_date;
$end_date = $request->end_date;
return view('report.profit_loss',
compact('expenses', 'payroll', 'operating_expenses', 'other_income',
'interest_paid', 'fees_paid', 'penalty_paid', 'operating_profit', 'gross_profit', 'start_date',
'end_date', 'loan_default', 'net_profit', 'monthly_net_income_data',
'monthly_operating_profit_expenses_data', 'monthly_other_expenses_data', 'other_expenses'));
}
解决方案
所以有很多事情会导致代码运行缓慢，我将从您最初关于该loan_paid_item方法的问题中修复一些问题。这样，您可以尝试在其余代码上实现这些技术。
请记住，我对什么是关系和什么不是关系做了一些假设，我建议遵循一些代码约定，因为这使每个人都更具可读性。
我改变了一个小东西的loan_paid_item方法。
- 第一个参数现在实际上接受一个 App\Models\Loan 实例，这将允许传递预先加载的关系。
//determine paid principal
public static function loan_paid_item(Loan $loan, $item = 'principal', $date = '')
{
}
现在我也改变了你loan_paid_item在控制器中使用方法的方式，看我的小例子：
- 我没有引入局部变量以使代码更具可读性
- 为了速度；我渴望加载两个关系，现在在每个 forloop 中这将减少 3 个查询。由于您 forloop 14 次，这将导致总共 42 个查询。
$loans = Loan::where('branch_id', session('branch_id'))
->with(['schedules', 'loan_product'])
->where('year', $d[0])
->where('month', $d[1])
->where('status','disbursed')->get();
foreach ($loans as $loan) {
$interest = GeneralHelper::loan_paid_item($loan, 'interest', $loan->due_date)
$fees = GeneralHelper::loan_paid_item($loan, 'fees', $loan->due_date)
$pentaly GeneralHelper::loan_paid_item($loan, 'penalty', $key->due_date);
$o_profit = $o_profit + $interest + $fees + $penalty
}
请记住，只有这样不会加速您的代码，您需要查看改进并将它们添加到整个控制器和辅助方法中。
希望这对您有所帮助，还请务必阅读以下内容：
推荐阅读
- reactjs - 在反应中更新复杂的对象数组
- java - Java中的静态参数化方法
- python - 如何旋转图像以对齐文本以进行提取？
- javascript - Openseadragon 错误消息无法隐藏
- python - Jupyter notebook 中的 Plotly Dash 给出“gaierror: [Errno -2] Name or service not known”
- c - ncurses窗口关闭触发键盘箭头
- json - Hive 3.x 导致外部表中压缩 (bz2) json 的错误
- javascript - 修剪和重用 RegExp 匹配来更新字符串
- serial-port - 我应该如何测试 UART 端口是否工作？
- swiftui - SwiftUI 2.0：如何使用没有标题的toolbar() 自定义iOS 14 导航栏？