What is the difference between laravel cursor and laravel chunk method?

We have a comparison: chunk() vs cursor()

• cursor(): High Speed
• chunk(): Constant Memory Usage

10,000 records:

+-------------+-----------+------------+
|             | Time(sec) | Memory(MB) |
+-------------+-----------+------------+
| get()       |      0.17 |         22 |
| chunk(100)  |      0.38 |         10 |
| chunk(1000) |      0.17 |         12 |
| cursor()    |      0.16 |         14 |
+-------------+-----------+------------+

100,000 records:

+--------------+------------+------------+
|              | Time(sec)  | Memory(MB) |
+--------------+------------+------------+
| get()        |        0.8 |     132    |
| chunk(100)   |       19.9 |      10    |
| chunk(1000)  |        2.3 |      12    |
| chunk(10000) |        1.1 |      34    |
| cursor()     |        0.5 |      45    |
+--------------+------------+------------+

• TestData: users table of Laravel default migration
• Homestead 0.5.0
• PHP 7.0.12
• MySQL 5.7.16
• Laravel 5.3.22

Cursor()

• only single query
• fetch result by call PDOStatement::fetch()
• by default buffered query is used and fetch all result at once.
• turned only current row into eloquent model

Pros

• minimize eloquent model memory overhead
• easy to manipulate

Cons

• huge result leads to out of memory
• buffered or unbuffered is a trade-off

Chunk()

• chunk query in to queries with limit and offset
• fetch result by call PDOStatement::fetchAll
• turned results into eloquent models batchly

Pros

• controllable used memory size

Cons

• turned results in to eloquent models batchly may cause some memory overhead
• queries and memory usage is a traid-off

TL;DR

I used to think cursor() will do query each time and only keep one row result in memory. So when I saw @mohammad-asghari's comparison table I got really confused. It must be some buffer behind the scenes.

By tracking Laravel Code as below

/**
 * Run a select statement against the database and returns a generator.
 *
 * @param  string  $query
 * @param  array  $bindings
 * @param  bool  $useReadPdo
 * @return \Generator
 */
public function cursor($query, $bindings = [], $useReadPdo = true)
{
    $statement = $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
        if ($this->pretending()) {
            return [];
        }

        // First we will create a statement for the query. Then, we will set the fetch
        // mode and prepare the bindings for the query. Once that's done we will be
        // ready to execute the query against the database and return the cursor.
        $statement = $this->prepared($this->getPdoForSelect($useReadPdo)
                          ->prepare($query));

        $this->bindValues(
            $statement, $this->prepareBindings($bindings)
        );

        // Next, we'll execute the query against the database and return the statement
        // so we can return the cursor. The cursor will use a PHP generator to give
        // back one row at a time without using a bunch of memory to render them.
        $statement->execute();

        return $statement;
    });

    while ($record = $statement->fetch()) {
        yield $record;
    }
}

I understood Laravel build this feature by wrap PDOStatement::fetch(). And by search buffer PDO fetch and MySQL, I found this document.

https://www.php.net/manual/en/mysqlinfo.concepts.buffering.php

Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process.

so by doing PDOStatement::execute() we actually fetch whole result rows at ones and stored in the memory, not only one row. So if the result is too huge, this will lead to out of memory exception.

Though the Document shown we could use $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); to get rid of buffered query. But the drawback should be caution.

Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".

The cursor method uses Lazy Collections, but just runs the query once.

https://laravel.com/docs/6.x/collections#lazy-collections

However, the query builder's cursor method returns a LazyCollection instance. This allows you to still only run a single query against the database but also only keep one Eloquent model loaded in memory at a time.

Chunk runs the query multiple times, and loads each result of the chunk into Eloquent models at one time.

Assuming that you have a million record in db. Probably this would give the best result. You can use something like that. With that you will use chunked LazyCollections.

User::cursor()->chunk(10000);

Best is to take a look at the source code.

select() or get()

https://github.com/laravel/framework/blob/8.x/src/Illuminate/Database/Connection.php#L366

return $statement->fetchAll();

It uses fetchAll which loads all records into memory. This is fast but consumes a lot of memory.

cursor()

https://github.com/laravel/framework/blob/8.x/src/Illuminate/Database/Connection.php#L403

while ($record = $statement->fetch()) {
   yield $record;
}

It uses fetch, it loads only 1 record into memory from the buffer at a time. Note that it only executes one query though. Lower memory but slower, since it iterates one by one. (note that depending on your php configuration, the buffer can be either stored on php side or mysql. Read more here)

chunk()

https://github.com/laravel/framework/blob/8.x/src/Illuminate/Database/Concerns/BuildsQueries.php#L30

public function chunk($count, callable $callback)
{
    $this->enforceOrderBy();
    $page = 1;
    do {
        $results = $this->forPage($page, $count)->get();
        $countResults = $results->count();

        if ($countResults == 0) {
            break;
        }

        if ($callback($results, $page) === false) {
            return false;
        }

        unset($results);

        $page++;
    } while ($countResults == $count);

    return true;
}

Uses many smaller calls of fetchAll (by using get()), and tries to keep memory low by breaking a big query result into smaller queries using limit depending on the chunk size you specify. In a way its trying to use the benefit of both get() and cursor().

As a rule of thumb I would say go with chunk, or even better chunkById if you can. (chunk has bad performance on big tables since it uses offset, chunkBy id uses limit).

lazy()

In laravel 8 there is also lazy(), its similar to chunk but syntax is cleaner (uses generators)

https://laravel.com/docs/8.x/eloquent#streaming-results-lazily

foreach (Flight::lazy() as $flight) {
    //
}

In does the same as chunk(), just you dont need a callback, since it uses php Generator. You can also use lazyById() similar to chunk.