Data Access Objects - Fetch Array

Yii 2.0 General Discussions
1

How do we execute mysql_fetch_array() using Data Access Objects?

If I have a large dataset to process I most certainly cannot use queryAll() as we will run out of memory.

2

Check out this section in the guide -

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#batch-query

It has an example. Not sure how well it will work for your but worth a shot.

HTH

Sandy

3

I found it, weird that none of this is in the docs:




        $command = Yii::$app->db3->createCommand($sql);

	$result = $command->query();

	foreach($result as $val) {

           // do stuff

        }

If you use queryAll() it grabs everything in one hit, where as query() will fetch by row as you loop the result.

4

I’ve found that using the ActiveQuery objects works extremely well for handling large amounts of data in batches. It feels like you’re just looping through one by one, but it’s querying for the objects in batches of 100 (or whatever you set your batch size to).




$finder = MyObj::find()->active()->byFavoriteOrder();   // note no all or one, etc.

foreach( $finder->each() as $model )

{

    // do stuff 

}
5

Have you found that you can process a lot of data without running out of memory? I have found that Active record uses up a lot more memory when running though data vs. doing things with the query builder, and still that will run out of memory while processing complex data.

What I have found is that memory control in PHP is not so great in that it doesn’t seem to release memory even with unset() and unsetting things in the foreach() loops and everywhere else that I could.

Have you tried the Batch method that I posted a link to the docs?

Sandy

6

Yea, what Dana posted is similar to yours except that it generates ActiveRecord models instead of plain arrays. If your dataset and/or your batch size is large enough, then definitely use the plain array one.

In terms of memory, I’ve found php to stay pretty consistent when iterating over batches of array data. The real problem I’ve noticed tends to be something rather subtle: the query log. If you’re running lots of sql queries, then that log gets big quickly and will steadily increase your memory usage.

7

Thanks for the comments

Yeah, I ran into out of memory issues much faster if I had any debugging enabled. I will have to revisit with some testing again at some point, another project coming up with lots of batch processing and would be nice to have a good handle on memory usage…

Sandy