I ran out of memory when finding ('all') a large amount of data with CakePHP [Solved]
This is Hase from the development team.
the previous article , I wrote about how to find ('all') a large amount of data without causing a memory error in CakePHP, but
I received a lot of criticism from within the company regarding the content of that article. So,
this time I would like to write some feedback on the previous content.
Therefore, I would appreciate it if you would view the previous article as a joke by a paper programmer. . .
current code
$data = $this->Model->find('all'); // Some processing ......
Using query() is ×
From the previous article
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.
This is wrong.
In the first place, the query method is used to execute complex queries, and
when used, it needs to be sanitized and validated, so it is better not to use it lightly.
In the first place, it is easier to use the framework's functions effectively by using find, so
there was no need to use the query method this time. .
It is better to obtain data while looping with a while statement instead of looping with a for statement.
Last time, I used a for loop to divide and retrieve data.
previous code
// 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 ... ...... }
There is nothing wrong with this method, but
In fact, I found that using a while statement to obtain it is a better method.
With the while statement, there is no need to take a count or calculate the number of loops.
Also, it's find('all') instead of query(). . .
So I tried rewriting it with a while statement.
Rewritten code
// Retrieve data for 5000 items at a time $limit = 5000; $params = array('limit' => $limit, 'offset' => 0); while ($data = $this->Model-> find('all', $params)){ // Some processing $params['offset']+= $limit; }
This is definitely better than using a for statement because there is no unnecessary processing.
I learned a lot!
That's it.