mysql results-to-csv writing function execution doesn't finish on production

Question

I have this function on my controller (Im using CodeIgniter) that reads the database, then produces a comma-delimited string that I feed on print() so the user can save/open the file.

On development, the export of about 2000+ records takes about 20-30 seconds. The problem is, on production, the request doesn't finish/goes to a blank page (no save/open dialog box is displayed by the browser).

Do I need to tweak some other php.ini settings? Currently on production memory_limit is at 256mb, max_execution_time is at 300. (On development max_execution_time is at 600).

If it helps, here are the codes (stripped down versions):

Model (kidsprofile_model):

function get_masterlist() {
    $this->db->select(kid_id,name, date_created, date_modified, birthdate)
    ->from('kids_profile');
}

The controller function that process the above to a csv:

function export_to_csv() {
        $kids = $kid_model->get_master_list();
     $out = "";
     $out .= "Date Created,Date Modified,Kid Id,Kid Name,Age".
             "Father Name,Mother Name,Guardian 1 Name,Guardian 2 Name,\n";
 foreach ($kids->result() as $kid) {
  $date_created = ($kid->date_created)?date('F j Y',$kid->kid_date_created):'';
  $date_modified = ($kid->date_modified)?date('F j Y',$kid->date_modified):'';
  $out .= $date_created.",".$date_modified.",".$kid->kid_name.",".getAge($kid->birthdate).","
  .get_detail_of_parent($kid->kid_id,'Mother','lname').",".get_detail_of_parent($kid->kid_id,'Mother','fname').",".get_detail_of_parent($kid->kid_id,'Mother','contact_no').","
     .get_detail_of_parent($kid->kid_id,'Father','lname').",".get_detail_of_parent($kid->kid_id,'Father','fname').",".get_detail_of_parent($kid->kid_id,'Father','contact_no').","
     .get_detail_of_guardian($kid->kid_id,0,'fname').",".get_detail_of_guardian($kid->kid_id,0,'lname').",".get_detail_of_guardian($kid->kid_id,0,'contact_no').","
     .get_detail_of_guardian($kid->kid_id,1,'fname').",".get_detail_of_guardian($kid->kid_id,1,'lname').",".get_detail_of_guardian($kid->kid_id,1,'contact_no').",";
  $out .="\n";
 }

 header("Content-type: text/x-csv");
 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 header("Content-Disposition: attachment; filename=master_list.csv");
 header("Pragma: no-cache");
 header("Content-Length: " . strlen($out));
 header("Expires: 0");

 print($out);
}

The get_detail_of_guardian() you see above inside the for loop queries another table that gets the guardian information for a kid. (To those who might say why not just left join it together with the query for the kids above, I tried it and it's much slower since I need 4 left joins (2 parents, 2 guardians) for 1 kid.)

Answer 1

Can you alter the query so it gets less data or has to process less? If that works then you know its a timeout or memory issue

Answer 2

The sheer number of calls you make to get_detail_of_parent and get_detail_of_guardian leads me to believe you're running out of memory. 4 LEFT JOINS should not drastically be reducing your query speed unless you don't have proper indexing on the tables or you are dealing with very large datasets and not enough free memory.

Could you not condense your calls to retrieve fname, lname, and contact_no into a single query? It appears you may be re-querying the database for each of those, leading to 12 queries for each kid result you iterate over.