Export to CSV

Yii 1.1 General Discussion
1

I’m trying to get some data filtered to a CSV file. The problem is that yii limits my output file, so I cannot get all the records.

I did it this way:

  • In controller I call a model function to get all the records using findAllBySql.

  • Then I do a renderPartial of the data which has header functions for the csv to be downloaded and shows the data comma separated

I also tried to do it directly in the controller, but happens the same problem. Is that yoo is limiting the number of characters in the output? How can I avoid it?

Thank you very much

2

Found this error msg

<b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1572864 bytes) in <b>/yii/framework/db/CDbCommand.php</b> on line <b>115</b><br />

Why does it use a lot of memory? Shouldn’t view just reading data and showing it?

3

Every row returned by the sql server becomes a new CActiveRecord object. That needs a bit more memory than the plain data and all records will remain in memory. You should use a paging mechanism for reading to avoid this.

4

I don’t know, but it seems strange to me that some 1,5 MB should need to be allocated in this line (Yii 1.1.b+)




$this->_statement=$this->getConnection()->getPdoInstance()->prepare($this->getText());

Also check if disable debugging helps, as suggested here.

This thread may be related to your problem.

/Tommy

5

I think memory gets full and php cannot execute next line of code. I disabled debug and still it goes wrong. I’m using Postgresql 8.3

I made some modifications to the code and it throws following error in the line 923 of the CSV file:

<b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted (tried to allocate 79 bytes) in <b>/home/christian/public_html/yii/framework/db/ar/CActiveFinder.php</b> on line <b>382</b><br />

Controller code is like this:




        public function actionCsv() {

                $separator = ';';    

                $sql = 'SELECT t1.* FROM "Table1" t1

                        LEFT OUTER JOIN "Table2" t2 ON t1.t2_id=t2.id 

                        WHERE t2.filter=:fil AND t1.active='1'

                ';

                $sqla = array(':fil'=>$filter);


                $models = Table1::model()->findAllBySql($sql, $sqla);

                $attrs = Table1::model()->attributeLabels();

                

                $this->renderPartial('csv',array('models'=>$models, 'attrs'=>$attrs, 'separator'=>$separator));

        }

And the view is like this:




header("Content-type: application/vnd.ms-excel");

header("Content-Disposition: attachment; filename=output.csv" );

header("Expires: 0");

header("Cache-Control: must-revalidate, post-check=0,pre-check=0");

header("Pragma: public");


$tmp = array(

  $attrs['col1'],

  $attrs['col2'],

...

  $attrs['coln'],

);


echo join($separator, $tmp)."\n";


foreach ($models as $model) {

  $tmp = array(

    $model->col1,

    $model->col2,

...

    $model->coln,

  );

    

  echo join($separator, $tmp)."\n";

}

As you see, first 922 lines are ok, but in line 923 memory gets full, is there something wrong in my code or maybe it’s not a good idea to have the csv generated in the view?

6

Do you have a relation among the columns? It can take extra memory if it is fetched on the fly(while you are generating the csv).

The error message is different only because it runs out of memory later.

7

There are relations with other tables this way

$model->table->columnname

So, you say it’s faster if I get them in the first query with a join? Or there’s another way to get the relationed data in the view?

Shouldn’t it be handled by yii internally? I mean the joins.

8

In the foreach loop, add




echo "memory usage: " . memory_get_usage(true)."\n";

That should give some clues.

What is the expected memory usage per record?

Do you use behaviors as well?

/Tommy

9

On the first row it throws memory usage: 33292288 ~ 35Mb. It keeps growing until the php memory limit

So then I did what Roberto told before, and commented the lines where used data from related tables, and the result was the same.

I think that $model is getting all the rows and not a pointer to the rows, so it holds all the records in memory and send them from controller to view.

Is there a way to get data row by row, not holding all of it in memory?

10

So you obviously still have 100 MB left when the two joined tables are present in $models.

That’s strange since no additional lazy loading should take place. I guess output buffering would need at most the same amount. It’s possible the array handling will allocate memory each iteration until leaving the foreach context (not tested). At least I don’t think GC will take place to often (would degrade performance).

Perhaps one of the find methods (not findAll), pk > current_pk?

Or you may consider to use DAO instead of AR e.g

CDbDataReader. (not tested)

/Tommy

11

You may find a bit of memory relief if you "chunk" your select statement by using a limit / offset.

You would also need to ensure you unset() any records that were "rendered"

nz

12

An easier question:

I noticed what Roberto said, so I skip all $model->table->data relations. This made performance better.

Anyway, how could I make JOIN relations with AR and get the data from them? The column names are not in the model, so I can’t name them. For this should I use no model?

I’m now doing this

Model::model()->findAll($criteria);

But if I make joins by hand I can’t get the column names from the Model.

13

for outputting large csv files, I find it is easier just to call db directly and build a sql statement by hand. Of course its nice to be able to use the active record stuff to figure out what has to go into each row, but there are other ways of doing that. I have run into the memory problem before, and this is how I solved it.

Once I figured out what the query was going to be, I just looped through the results and wrote directly to a stream:




// inside of my csv function


$fiveMBs = 5 * 1024 * 1024; 

$fp = fopen("php://temp/maxmemory:$fiveMBs", 'w');                                                         

if ($includeColumnHeaders) {

   fputcsv($fp, $headers, $delimiter);

}                                   

fputcsv($fp, $values, $delimiter);                

rewind($fp);

return stream_get_contents($fp);

part of the problem is that I think since you are trying to render it in a view, ob_start(), which is what I believed is being using to capture all of the output, thats running out of memory too, making it impossible to finish. Don’t bother using a view to render a csv file, use the native php functions as they will have better memory management etc.

14

Many times the Yii leader said that active record are powerful objects, and so they are memory consuming.

Is deprecated to use them in context in wich there are lot of records (when you cannot limit by pagination).

In this case is suggested (as nsbucky said) to use DAO for direct access to database.

15

An alternative to direct access is to use the new query builder, introducted in the last revision of the framework.

It gives lot of method for build a query (so preserve the database independency) in a php ways, and return an array, wich avoid the memory overhead of AR

16

Dear christian, is this technique also available to generate pdf?

17

For pdf you can use some nice library like fpdf.

I used it some times ago without problem.

18

To fill and flatten pdfs with Yii I use a combination of pdftk and pdftk-php (by Andrew Heiss)

(I am not allowed to include links since this is my first post, but a simple google search will get you both)

so far it seems to work very well, and is relatively easy to use.