When finding ('all') a large amount of data with CakePHP, I ran out of memory [I tried...edit]

This is Hase from the development team.

The other day,
when I tried to use find('all') to retrieve nearly 100,000 pieces of data using CakePHP, I ran out of memory and
got the following error. .

Allowed memory size of 134217728 bytes exhausted

So, I researched various ways to save memory and be able to retrieve nearly 100,000 rows of data.

current code

$data = $this->Model->find('all'); // Some processing ......

Use query()

It seems that retrieving with query() is easier to process than retrieving with find('all').
So, I modified it to use query() to retrieve what was being retrieved using find.

$data = $this->Model->query("SELECT * FROM hogehoge;"); // Some processing ......

result

Emotional processing became easierI felt likebut,
Same as when I retrieved it using find('all'), there was not enough memory and an error occurred.

Disable cache for query()

When I looked at the official version, it seems that query() has a mechanism to cache queries by default.
To disable caching this query,
specify false as the second argument like query($query, $cachequeries = false).
So, I tried specifying false as the second argument of query().

$data = $this->Model->query("SELECT * FROM hogehoge;", $cachequeries = false); // Some processing ......

result

In conclusion, there was no particular change. .

Use a loop to divide and retrieve data

In the first place, with the above method, 100,000 pieces of data were acquired at once, so
it was natural that the memory would run out.
So I tried using limit and offset to divide the data and obtain it using loop processing.

// Get the number of data items $count = $this->Model->find('count'); // Get 5000 pieces of data at a time $limit = 5000; // Number of times to loop ({data number of items ÷ number of items retrieved per time} rounded up) $loop = ceil($count / $limit); for ($i = 0; $i < $loop; $i++){ // Offset $offset = $limit * $i; $data = $this->Model->query("select * from hogehoge as limit {$limit} offset {$offset};", $cachequeries = false); // Some processing ... ...... }

result

It was a great result.
There were no memory errors and the data was properly retrieved, so there is no problem! !

So, if there is someone who is having trouble with the same phenomenon, I would appreciate it if you could use this as a reference.

That's it.

However, with this method...
Read more.
When I found ('all') a large amount of data with CakePHP, I ran out of memory [Solved] | Beyond Co., Ltd.