I ran out of memory when using find('all') with a large amount of data in CakePHP [I tried it but...]

This is Hase from the development team

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

Allowed memory size of 134217728 bytes exhausted

So I looked into various ways to reduce memory usage and retrieve nearly 100,000 rows of data

Current code

$data = $this->Model->find('all'); // Do something .........

Using query()

It seems that retrieving data using query() is lighter on processing than retrieving it using find('all').
So, I modified the code to retrieve data using query() instead of find.

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

result

Emotional processing has become easierI felt likebut,
Just like when I retrieved it using find('all'), an error occurred due to insufficient memory

Disable query() caching

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

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

result

In conclusion, there was no particular change

Use a loop to retrieve data in parts

The method described above still retrieves 100,000 data points at once, so it
was inevitable that it would run out of memory.
Therefore, I tried using limit and offset to split the data into smaller chunks using a loop.

// Get the number of data items $count = $this->Model->find('count'); // Retrieve 5000 items of data at a time $limit = 5000; // Number of loops ({number of data items ÷ number of items retrieved at one 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); // Do some processing ......... }

result

The results are fantastic!
There were no memory errors, and the data was retrieved correctly, so everything is fine!

So, if anyone is having trouble with the same issue, I hope this helps

That's all

However, with this method...
See here for more details.
I ran out of memory when using find('all') on a large amount of data in CakePHP [Solution] | Beyond Co., Ltd.