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

Recently, I tried
to retrieve nearly 100,000 pieces of data 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 query() is faster than find('all').
So I changed what I was getting with find to query() instead.

$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

According to the official documentation, query() caches queries by default.
To disable query caching,
you can specify false as the second argument, like this:
So, I tried specifying false as the second argument to 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

In the first place, with the above method, 100,000 pieces of data are still retrieved at once, so it
was natural that there would be insufficient memory.
So I tried to divide the data and retrieve it in a loop using limit and offset.

// 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 were excellent.
There were no memory errors and the data was retrieved properly, so there are no problems!

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

That's all

However, this method…
Read more here:
CakePHP ran out of memory when using find('all') to process a large amount of data [Solution] | Beyond Co., Ltd.