pluck() performance issues (specifically Query\Builder::pluck)

[vlakoff]

I encountered performance issues using Query\Builder::pluck(). This is because it uses Arr::pluck(), which calls data_get() on each row. data_get() easily becomes a bottleneck when used in loops.

On a tiny 5000 rows test database, Builder::pluck() takes 184 ms. Right now, I'm using a custom method which takes 40 ms.

// CustomQueryBuilder
public function fastPluck($column, $key = null)
{
    $results = $this->get($key === null ? [$column] : [$column, $key]);

    return object_column($results, $column, $key);
}

// custom helper
function object_column($data, $column, $index = null)
{
    $result = [];

    if ($index === null) {
        foreach ($data as $row) {
             $result[] = $row->$column;
        }
    } else {
        foreach ($data as $row) {
             $result[$row->$index] = $row->$column;
        }
    }

    return $result;
}

I can use this code, because I know:

• I'm manipulating an array of objects (config/database.php 'fetch' => PDO::FETCH_CLASS)
• There is no need for "dot access"
• There is no missing value

Maybe this could be of inspiration for implementing a general-purpose optimized method.

[franzliedke]

I like the idea, but can you really rely on your fetch mode? After all, it is configurable in config/database.php.

That said, maybe we can use either array_get or object_get depending on the fetch mode instead?

[vlakoff]

Arr::get() wouldn't add value (we don't need dot access nor support for ArrayAccess). Same for object_get().

Indeed, the code would have to support both array and object fetch modes.

My main concern is about the missing values. Could this only happen if the user asked for a nonexistent DB column, or could there be other cases?

[franzliedke]

I'm fairly positive this would only happen for invalid (nonexistent) column names, yeah...

[vlakoff]

So basically, the only question is: what should happen if the user queries a nonexistent column (which is clearly wrong). Currenty it would return an array of nulls… I suppose it would be okay if the user gets a shitload of "index/property undefined", so he would just fix its code nonetheless.

[vlakoff]

That's even simpler: querying a nonexistent column would throw a sql error beforehand…

So, I can't think of any technical limitation for optimizing Builder::pluck().

[vlakoff]

The main (and only) concern about implementing the optimization, is proper handling of PDO fetch mode.

As it may be of interest when working on this, refs laravel/framework#12171.

[vlakoff]

Just as a reminder, one case to keep in mind: if the user has set the connection to PDO::FETCH_CLASS, CustomClass and this class modifies the properties when building.

Maybe an edge case, but I suppose here we'd want to pluck the class-modified properties, not the original properties from DB.

[taylorotwell]

I'm open to improvements here if possible.

[franzliedke]

PR cross-reference: laravel/framework#23482

[manjuuu]

pluk is there for laravel 5

[manjuuu]

pluck is changed as value()