Fastly insert 200 data to the tables with relationship

Table: vacancies

id int unsigned Auto Increment code varchar(191) NULL
start_time timestamp NULL
end_time timestamp NULL
business_unit_id int unsigned
job_id int unsigned
on_date date
break int NULL
shift_id int unsigned
total_numbers int

Table: vacancy_details

id int unsigned Auto Increment vacancy_id int unsigned
day_id int unsigned
shift_id int unsigned
rate_type enum('flat_rate','hourly_rate') [hourly_rate]
start_time timestamp NULL
end_time timestamp NULL
break int NULL
total_hours decimal(10,2) NULL client_rate decimal(8,2) NULL

Table: vacancy_staff_rates

id int unsigned Auto Increment vacancy_id int unsigned
vacancy_detail_id int unsigned NULL
employment_type_id int unsigned
staff_rate decimal(8,2) NULL

public function store_multiple($subdomain, Request $request){
    if($request){
        $payloads = $request->data;
        foreach($payloads as $payload){
            $payload = (object) $payload;
            //insertion(3 tables) and all the calculations and logic are doing in this add_vacancy()
            $vacancy = Vacancy::add_vacancy($payload);
        }
    }
}

These are the 3 tables. When I'm adding 10 or 20 the insertion is fast but comes 100 or plus it's taking more time. Can anyone suggest a way to do it fatser?

CodePudding user response：

Suggestion:

Gather the 200 rows into a separate table (perhaps a TEMPORARY table). Then massage the values as needed while in this table. Some (all?) of the massaging can be done in bulk; that is, all 200 rows at the same time. If you need to "normalize" (turn some strings into ids) this is a good time to do that.

Then, as a final step do a single INSERT INTO real SELECT ... FROM temp to put them where they belong.