How to solve memory leak in Yii framework or PHP, aside from setting the directive of memory limit?

Question

I have written this function to generate a CSV file using the Yii framework command line the problem now is I have more than 300,000 rows data to be written in the CSV file. So what I did is, I split the data into 10,000 for each CSV file.

  public function Targets2($offset,$limit,$batch){

    if($offset == ''){
        echo 'provied an offset!';
        exit;
    }
    if($limit == ''){
        echo 'provide a limit';
        exit;
    }
    if($batch == ''){
        echo 'you wil overwrite the 1st batch!';
        exit;
    }





    $dataProvider=new CActiveDataProvider('users',
            array(
                    'criteria' => array(
                            'order' => 'userid ASC',
                            'offset'=>$offset,
                            'limit' => $limit,
                    ),
                    'pagination' => false
            )
    );

    $datas = $dataProvider->getData();



    $userdatas = array();

    foreach($datas as $data){
        $h = '"D"';
        $ssn = '""';
        $email = '';
        $mobile_no = '';
        $specialtargetname = '"special target"';

        if(!is_null($data->USEREMAILADR) && !empty($data->USEREMAILADR)){
            $email = '"'.$data->USEREMAILADR.'"';
        } else {
            $email = '""';
        }

        if(!is_null($data->USERCT) && !empty($data->USERCT)){
            $mobile_no = '"'.$data->USERCT.'"';
        } else {
            $mobile_no = '""';
        }

        $userdatas[] = array('"'.$data->USERID.'"',$h,'""',$email,$mobile_no,$specialtargetname);

    }


    $headers = array(
            '"USERID"',
            '"H"',
            '"SSN"',
            '"EMAIL"',
            '"MOBILE_NO"',
            '"SPECIAL_TARGET_NAME"'
    );

    $date = date('Ymd',strtotime('today'));

    $totaldata = count($userdatas);

    $file = 'C:\xampp\htdocs\yii\branch\protected\data\Target_'.$date.'_'.$batch.'.csv';
    $fp = fopen($file, 'w+');

    fputcsv($fp,$headers,','," ");

    if($totaldata > 0){

    foreach($userdatas as $line){
        fputcsv($fp,$line,','," ");
    }
    $end = array('"T"');
    $fp2 = fopen($file,'a+');
    fputcsv($fp2,$end,','," ");
    echo "offset = $offset with limit $limit contacts file generated batch  " . $batch ."\n";       
    fclose($fp);
    fclose($fp2);
    $offset = $limit;
    $limit += 10000;
    $batch += 1;    
    $this->Targets2($offset,$limit,$batch);
    }

The problem now is, before the script even write the 2nd batch of CSV file, the error occurs

Fatal error: Allowed memory size of 1744830464 bytes exhausted (tried to allocat
e 48 bytes) in C:\xampp\htdocs\yii1.1.10\framework\db\CDbCommand.php on line 506

Even if I set the memory_limit to 1664M like e.g

ini_set('memory_limit','1664M');

I am still getting a fatal memory leak error.

If I set it to "-1" it crashes the server and I don't want to push that code in production. It's so scary.

How to solve or optimize?

Answer 1

It's better to avoid using CActiveRecord instances when processing lot's of data, each instance will use addional memory, using plain arrays can help. Also you need to query data in chunks as follows:

class ImportCommand extends CConsoleCommand
{
  public function run($args)
  {
      $db  = Yii::app()->db;

      $limit     = 1000;  
      $totalRows = $db->createCommand()
                      ->from("users")
                      ->select("count(*)")->queryScalar();

      $n = ceil($totalRows / $limit);
      $k = 0;

      // $fp = fopen(...)

      for($i = 0; $i < $n ; $i++)
      {
          $offset = $i*$limit;
          $list  = $db->createCommand()->from("users")
                   ->offset($offset)
                   ->limit($limit)->queryAll();

          foreach($list as $user)
          {
              //process $user here
          }
      }
      // fclose($fp)
  }
}

Also you can save more memory if you specify only fields you need by using select method on query builder.