Ufdjrw

I've found great example written in php pdo, which helps to iterate huge amount of data without actually allocating memory for whole set of results:

 $sql = 'SELECT * from playlists limit 50000';
 $statement = $pdo->prepare($sql);
 $statement->execute();

 while (($result = $statement->fetch(PDO::FETCH_ASSOC)) !== false) 
 //do something
 

I've done an investigation and this approach uses 18mb of memory.

If I fetch all results like this $results = $statement->fetchAll(PDO::FETCH_ASSOC); memory usage upraises to 35mb.

Using laravel's illuminate/database component and very similar approach DB::table('playlists')->limit(50000)->get(); also uses 35mb of memory.

• How can I achieve first approach using Laravel's eloquent or DB facade?


• Could you suggest some articles how this difference in memory usage develops?

Thanks

When you execute an SQL query with php (either mysql functions or PDO) all data returned from query loads in to memory as a "result set".

In order to use data in "result set" you have to fetch them in regular php arrays/objects.

PDOStatement::fetch - fetches one row from the result set in to memory.

PDOStatement::fetchAll - fetches all rows from result set to memory thus doubling the memory usage.

Eloquent has ability to chunk result sets. This is equivalent to performing "X times fetch" in PDO.

However, if you are working with very large result sets consider using SQL limits.

The Laravel approach to processing large data sets like this is to use chunking.

DB::table('playlists')->chunk(1000, function($playlists) use($count) 
 foreach($playlists as $playlist) 
 // do something with this playlist
 
);

This ensures that no more than the chunk size (in my example, 1000 rows) is loaded into RAM at once. 1k is arbitrary; you could chunk 1, 100, 253, etc.