Import Excel into mySQL taking too long

Question

So, I have a form that allows users to upload an excel sheet and then the system will import that excel data into mySQL.

However, every time I submit the form using AJAX, it starts the process, saves about half of the data, and then gives me a 504 gateway error. I have already changed the PHP config timeout to 300 but it still gives in half way through. I do not think that an excel sheet with a little under 1000 rows should be taking 5+ minutes?

Here is my code:

 public function postImportGroup(Request $request)
{

    if($request->hasFile('import_numbers')) {
       $file = $request->file('import_numbers');
       $file_extension = Input::file('import_numbers')->getClientOriginalExtension();
       $supportedExt = array('csv', 'xls', 'xlsx');

    if (!in_array_r($file_extension, $supportedExt)) {
        return response()->json([
            'status' => 'error',
            'msg' => 'Please make sure that the uploaded file is a valid CSV, XLS, XLSX sheet.',
        ]);
    }

   }

    $results = Excel::load($file)->get();

    $results = json_decode($results[0], true);

    $class = new DailyGroup();
    $class->title = $request->group_name;
    $class->user_id = Auth::guard('client')->user()->id;
    $class->entries = count($results);
    $class->save();

    foreach ($results as $r => $value) {
      //$data = array_values($value);
        //return $value["employee_number"];
      $group = new DailyGroupLocations();

      $address = $value["address"] . ',' . $value["city"] . ',' . $value["state"] . ',' . $value["zip"];
      $c = $value["country"];

      $file_contents = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($address) . '&components=country:' . urlencode($c) .'&sensor=false&key=xxx');
      $json_decode = json_decode($file_contents);


      if (isset($json_decode->results[0])) {  
        $group->lat = $json_decode->results[0]->geometry->location->lat;
        $group->lng = $json_decode->results[0]->geometry->location->lng;
    }
      $phone = preg_replace('/\D+/', '', $value["ph"]);
      $phone = '1'.$phone;

      $group->user_id = Auth::guard('client')->user()->id;
      $group->group_id = $class->id;
      $group->employee_number = $value["employee_number"];
      $group->work_date = $value["work_date"]["date"];
      $group->first_name = $value["name"];
      $group->last_name = $value["lastname"];
      $group->phone = $phone;
      $group->email = $value["email"];
      $group->job_number = $value["job_number"];
      $group->address = $value["address"];
      $group->city = $value["city"];
      $group->state = $value["state"];
      $group->zip = $value["zip"];
      $group->country = $value["country"];
      $group->job_name = $value["job_name"];
      $group->location = $value["location"];
      $group->shift_description = $value["shift_description"];
      $group->shift_start = $value["shift_start_time"];
      $group->shift_end = $value["shift_end_time"];
      $group->post_hours = $value["post_hours"];
      $group->save();
}

        return response()->json([
            'status' => 'success',
            'msg' => 'All data uploaded successfully. Please wait for tables to refresh.',
            //'url' => '/user/location/location-areas'
        ]);
}

Is there anything I can do to optimize this? Am I running to many things in the foreach statement? Any tips or tricks I can use?

Where is the slow down? Code doesn't tell us much about database layer or how save() operates. Is it the geocoding calls to Google? 1000 rows, 1 second per request to google... adds up. Id run this as a job and not keep the user/client on the hook for the whole process. — ficuscr
– ficuscr, Commented Aug 8, 2018 at 22:23
Do you think it could be the calls to Google? I will disable the calls and test to see if it becomes faster. — Nathan Modern
– Nathan Modern, Commented Aug 8, 2018 at 22:27
@ficuscr okay so it is definitely the google calls. Just checked it. In terms of running it as a job, would I go through it by calling it every minute or every 10 minutes? Would you be able to guide me in the right direction? — Nathan Modern
– Nathan Modern, Commented Aug 8, 2018 at 22:29
There are many approaches to a job queue. RabbitMQ to cron firing off a script every so often. Yes, a simple approach would be fire a script off every 10 minutes, look for things to process. Add some logging in for user to have feedback. etc If you don't need super precise lat/lng data then base it off their zip code. Google keeps increasing rates anyway :) — ficuscr
– ficuscr, Commented Aug 8, 2018 at 22:35
Got it. Thank you for the suggestion going to see how well it will work. @ficuscr — Nathan Modern
– Nathan Modern, Commented Aug 8, 2018 at 22:51

netzding · Accepted Answer · 2019-05-16 10:59:58Z

2

its Not the excel file taking its time... its the google geocode call which is blocking your code from executing.

you can get yourself an google api key to speed up your process.

reference: https://developers.google.com/maps/documentation/javascript/get-api-key

you should also Check for the response status of your geocode calls

$status = json_decode->results[0]->status;

possible status values:

OK
ZERO_RESULTS
OVER_QUERY_LIMIT
INVALID_REQUEST
UNKNOWN_ERROR

if its possible in your case you could consider pre-geocoding your dataset, store the lat and lng values with the address so you do Not have to geocode on the fly if you expect or need a fast execution.

edited May 16, 2019 at 10:59

answered Aug 8, 2018 at 22:32

netzding

7775 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ficuscr Over a year ago

FYI: "All Google Maps Platform API requests must include an API key; we no longer support keyless access."

netzding Over a year ago

@ficuscr This statement from google was not for geocoding.

ficuscr Over a year ago

Link to source is in comment on FYI text: here, developers.google.com/maps/documentation/geocoding/… your link is out dated. Billing must now be enabled and API key in use.

netzding Over a year ago

@ficuscr you were totally right... until today everything worked on live... but now im in a hurry applying the auth keys!

ficuscr Over a year ago

That was me a few days ago :) Be sure to set daily quotas to avoid surprises.

|

Collectives™ on Stack Overflow

Import Excel into mySQL taking too long

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related