How to print out 150k record in CSV, without overloading the website

Question

In order to avoid overloading the server, I made a loop of queryen, I'll get 150k members up and stored in an array. This works fine, but when the loop has finished with its job, the array has to be printed out, but this takes a long time and it ends up, with the side crashes.

$development = array(
    'testing' => false,
    'testing_loops' => 1
    );

$settings = array(
    'times_looped' => 0,
    'members_at_a_time' => 2000,
    'print_settings' => true,
    'members_looped' => 0,
    'test' => 0,
);


function outputCSV($data) 
    {

    $outstream = fopen("php://output", 'w');

    array_walk($data, '__outputCSV', $outstream);

    fclose($outstream);
    }

function __outputCSV(&$vals, $key, $filehandler) 
    {
        fwrite($filehandler, implode(',',$vals). "\n");
    }


function getMembers(&$settings, $ee)
{

    // SQL FROM
    $sql_from = $settings['times_looped'] * $settings['members_at_a_time'];

    // SQL LIMIT
    $sql_limit = $sql_from . ', ' . $settings['members_at_a_time'];

    $settings['test'] = $sql_limit;

    // GET MEMBERS
    $query = $ee->EE->db->query("SELECT m.email,
    cr.near_rest_1_id, cr.near_rest_1_distance, 
    cr.near_rest_2_id, cr.near_rest_2_distance,
    cr.near_rest_3_id, cr.near_rest_3_distance
    from exp_members m
    left join 
    exp_menucard_closest_restaurants cr
    on m.member_id = cr.member_id
    where group_id = 8 or 14 limit ".$sql_limit."");      

    // Check if members found
    if($query->num_rows() == 0)
    {
        return $query->num_rows();
    }

    // Update number of members
    $settings['members_looped'] = $settings['members_looped'] + $query->num_rows();

    // Loop members
    foreach($query->result_array() as $row) {

       if($row['near_rest_1_distance'] > 1.0)
        {$near_rest_1_distance= number_format($row['near_rest_1_distance'], 2, ',', ',') ." ". 'km';}

        else
        {$near_rest_1_distance= number_format($row['near_rest_1_distance'], 3, ',', '')*1000 ." ". 'meter';}

        if($row['near_rest_2_distance'] > 1.0)
        {$near_rest_2_distance= number_format($row['near_rest_2_distance'], 2, ',', ',') ." ". 'km';}

        else
        {$near_rest_2_distance= number_format($row['near_rest_2_distance'], 3, ',', '')*1000 ." ". 'meter';}

        if($row['near_rest_3_distance'] > 1.0)
        {$near_rest_3_distance= number_format($row['near_rest_3_distance'], 2, ',', ',') ." ". 'km';}

        else
        {$near_rest_3_distance= number_format($row['near_rest_3_distance'], 3, ',', '')*1000 ." ". 'meter';}

         $nearest_rest_result_array[] = array(
        'email' =>  $row['email'],
        'near_rest_1_id' =>  $row['near_rest_1_id'],
        'near_rest_1_distance' => $near_rest_1_distance,
        'near_rest_2_id' =>  $row['near_rest_2_id'],
        'near_rest_2_distance' =>  $near_rest_2_distance, 
        'near_rest_3_id' => $row['near_rest_3_id'],
        'near_rest_3_distance' =>  $near_rest_3_distance
        ); 

    }

    // Loop again
    return $query->num_rows();
}


// Loop
$more_rows = true;
while($more_rows == true || $more_rows > 0) 
{

    // Test
    if($settings['times_looped'] >= $development['testing_loops'] && $development['testing'] == true){
        break;

    }
    // get members
    $more_rows = getMembers($settings, $this);
    $settings['members_looped'] = $settings['members_looped'] + $more_rows;
    $settings['times_looped']++;

    // Got last bunch of members
    if($settings['members_looped'] < $settings['members_at_a_time'])
    {
        break;

    }

}

When the loop has finished with its job, it will print all the array out

// Write to CSV
outputCSV($nearest_rest_result_array);

Are you really sure, that "printing" (writing to a file, is't it?) is the problem? Is your problem a timeout or a memory limitation? — BurninLeo, Aug 30 '13 at 11:54

score 3 · Answer 1 · answered Aug 30 '13 at 11:52

3

Don't use a foreach loop. Use a while-loop that reads a rows from the database and writes it to the CSV file. This way you're operating line-by-line which doesn't use as much memory.

If you're working with large data sets it's usually better to have some concept of iterators or streams, rather that trying to modify the whole in one big operation.

answered Aug 30 '13 at 11:52

Halcyon

57,230
10
89
128

arh okay, then there is no need for an array? – Zaz Aug 30 '13 at 11:59

score 2 · Answer 2 · answered Aug 30 '13 at 11:53

2

The mistake starts early, use an iterator instead of the array you currently do:

foreach($query->result_array() as $row)

PDO and Mysqli allow to iterate over the result. Create the output on the fly and stream it to the client, your webserver will chunk it normally, if not, set your PHP output buffer to 4096k or similar.

answered Aug 30 '13 at 11:53

hakre

193,403
52
435
836

@Zaz: That depends on the database client library. Which one are you using? – hakre Aug 30 '13 at 12:01
Uhh, I do not have much experience in this, I use the expression engine as my db – Zaz Aug 30 '13 at 12:04
That would require me to look into expression engine as well. Please provide it so that I can make use of it. – hakre Aug 30 '13 at 12:08
This is going to be to advanced @hakre. Is there not a simplere way that can be illustrated? – Zaz Aug 30 '13 at 12:21
Well, a PDO example is in http://stackoverflow.com/a/15386231/367456 but I don't know if that already helps you, take a look. – hakre Aug 30 '13 at 12:25

score 0 · Answer 3 · answered Aug 30 '13 at 11:55

Consider implementing pagination in your webpage.

Lets take an example. Suppose your database has 10,000 rows. There maybe no need for those 10,000 rows to be displayed at once. Instead we can display 100 records per page and have links 100 such pages.

Best Example can be https://www.google.co.in/?gws_rd=cr&ei=-HggUuXWBMj4rQeNr4CADw#q=pagination+in+php

Of 6,190,000 results they have shown only 11 per page.

How to print out 150k record in CSV, without overloading the website

3 Answers3