In my project there is a function that fills the database as follows: 31 employees are created for each employee (well, sometimes not 31, depending on how many days are in the month that is filled), some data is assigned to the day (start time, duration, etc.). ).

Experimentally, I found out that the problem is precisely in the "filling out" of the database with "days". Function code:

public function actionCreateDays($month) { for($i = 1; $i <= cal_days_in_month(CAL_GREGORIAN, $month->month, $month->year); $i++) { $date = $month->year . '-' . sprintf("%02s", $month->month) . '-' . sprintf("%02s", $i); $day = Calendar::model()->findByPk($date); $type = $day->day_type; if ($type == 'Суббота' || $type == 'Воскресенье' || $type == 'Праздник') { // НУЖНО ПОДГРУЖАТЬ ПРАЗДНИКИ!!!! (ВРОДЕ ПОДГРУЖАЮТСЯ) $type = 'В'; } else { $type = 'РАБ'; } $rate_start_time = $date . ' 08:00:00'; $rate_end_time = $date . ' 17:00:00'; $rate_long_time = 8; $rate_lunch_time = 1; if ($month->rate == "0,5") { $rate_start_time = $date . ' 17:15:00'; $rate_end_time = $date . ' 21:15:00'; $rate_long_time = 4; $rate_lunch_time = 0; } elseif ($month->rate == "0,25") { $rate_start_time = $date . ' 21:30:00'; $rate_end_time = $date . ' 23:3.:00'; $rate_long_time = 2; $rate_lunch_time = 0; } $day = new GraphDays(); $day->month_id = $month->id; $day->day = $i; $day->type = $type; $day->start_time = $rate_start_time; $day->end_time = $rate_end_time; $day->long_time = $rate_long_time; $day->lunch_time = $rate_lunch_time; $day->save(false); $day = new TimesheetDays(); $day->month_id = $month->id; $day->day = $i; $day->type = $type; $day->start_time = $rate_start_time; $day->end_time = $rate_end_time; $day->long_time = $rate_long_time; $day->lunch_time = $rate_lunch_time; $day->save(false); } }

Any advice on how to optimize this horror is accepted = (

I will add the answer . Here is the function of creating a month in the database, which calls the function above (creating days).

 public function actionCreateMonth($arg_year, $arg_month) { $depart = Departs::model()->find(array('condition' => 't.name = "' . Yii::app()->user->getId() . '"')); $start = microtime(true); $s = new Soap(); $all_posts = $s->getPosts(); $all_divisions = $s->getDivisions(); $all_rates = $s->getRelatedActualyPersons($depart['uid']); $rates_time = microtime(true) - $start; $start = microtime(true); $cache = array(); foreach ($all_rates as $rate) { $month = Months::model()->findByAttributes(array( 'person_post' => $all_posts[$rate['post']], 'person_uid' => $rate['uid'], 'type' => $rate['empType'], 'rate' => $rate['rate'], 'month' => $arg_month, 'year' => $arg_year )); if ($month == null) { $month = new Months(); $month->depart_uid = $rate['depart']; $month->depart_name = $all_divisions[$rate['depart']]['name']; $month->person_uid = $rate['uid']; $month->person_tab = $rate['tabNum']; $month->person_fio = $rate['fio']; $month->person_post = $all_posts[$rate['post']]; $month->rate = $rate['rate']; $month->type = $rate['empType']; $month->year = $arg_year; $month->month = $arg_month; $month->graph = 8; $month->categorie = "Служащие"; $month->save(false); $this->actionCreateDays($month); // Подсчет времени if (!array_key_exists($month->rate, $cache)) { $cache[$month->rate] = array( "graphInfo" => $this->calculateTotal($month, 'graph'), "tabelInfo" => $this->calculateTotal($month, 'tabel') ); } $time = new TotalTimeGraph(); $time->month_id = $month->id; $time->days = $cache[$month->rate]['graphInfo']['total_days']; $time->hours = $cache[$month->rate]['graphInfo']['total_hours']; $time->night_hours = $cache[$month->rate]['graphInfo']['night_hours']; $time->normal_hours = $cache[$month->rate]['graphInfo']['normal_hours']; $time->deviation_hours = $cache[$month->rate]['graphInfo']['deviation_hours']; $time->save(false); $time = new TotalTimeTimesheet(); $time->month_id = $month->id; $time->days = $cache[$month->rate]['tabelInfo']['total_days']; $time->hours = $cache[$month->rate]['tabelInfo']['total_hours']; $time->night_hours = $cache[$month->rate]['tabelInfo']['night_hours']; $time->normal_hours = $cache[$month->rate]['tabelInfo']['normal_hours']; $time->deviation_hours = $cache[$month->rate]['tabelInfo']['deviation_hours']; $time->holiday_hours = $cache[$month->rate]['tabelInfo']['holiday_hours']; $time->days_15 = $cache[$month->rate]['tabelInfo']['days_15']; $time->hours_15 = $cache[$month->rate]['tabelInfo']['hours_15']; $time->night_hours_15 = $cache[$month->rate]['tabelInfo']['night_hours_15']; $time->holiday_hours_15 = $cache[$month->rate]['tabelInfo']['holiday_hours_15']; $time->save(false); } } $bd_time = microtime(true) - $start; return array( '1c_exec_time' => $rates_time, 'bd_exec_time' => $bd_time ); }

The logic of her work is simple - we create an entry for the employee, call the function to create days for the newly created entry.

  • and what's the problem? How long does it work? Or wrong? Or what? If it is long, you can save portions (for example, 100 pieces at a time), then there will be no extra time to process the request - BOPOH
  • Of course, it works for a long time. Portions are not an option to save, because Work is possible only after full data storage. - Ilya Bizunov
  • There is such a value - NULL. That is an empty set. Typical information, for example, employee1 worked on a working day, just as employee2 also worked on this working day — it is a waste of disk space. With the same success you can fill the database with solid NULL. Is not it so? - Vanyamba Electronics
  • You reuse the connection there, I hope? Or for every $day->save(false); is a new connection being created? - Suvitruf ♦
  • @Suvitruf honestly, I don't know. Now I will try to deal with the connection. - Ilya Bizunov

1 answer 1

Your problem is that you use ActiveRecord where you don’t need it. To create records inside actionCreateDays it is better to first create an array of necessary data, and then place it into the database with one insert query.

Also in this function $day = Calendar::model()->findByPk($date); .

Problems in this call:

  • Request is not cached
  • In fact, you use only day_type , which means that there is no need for ActiveRecord, but a rather simple query
  • The day is searched for each iteration, although it is possible to request the entire day_type for the all available Calendar by one request with the condition where id in (...) .

The result is that you save the number of requests (1 instead of 30 for a 30-day month), memory (dao instead of activerecord) and extra milliseconds due to the failure of activerecord.

Similar optimization can be done in the actionCreateMonth function.

PS: Instead of measuring time through microtime, yii has built-in simple profiling : Yii::beginProfile('MyLongOperations'); // тут какие-нибудь долгие операции Yii::endProfile('MyLongOperations'); Yii::beginProfile('MyLongOperations'); // тут какие-нибудь долгие операции Yii::endProfile('MyLongOperations');

PPS: In terms of optimizing this task, you can go even further and use MySQL stored procedures, as well as do the calculation of coefficients on the fly.