eager loading and PHP (not MYSQL) performance issue

Best Answer

Level 56

Create indexes for your foreign keys. Even for 50 000 rows.

If you load 12 000 eloquent object + 4 relationship for each in memory there will be problems. Use the chunk method to treat them by smaller chunks.

Level 11

There is more than 10Go free RAM during process, so I don't think it's a memory issue. CPU is overloaded on the apache process.

Do you really think the foreign keys will have significant impact on request execution ?

I don't really understand how I can use chunk on my request, do you have more explicit example ?

Level 56

Trust me processing 12 000 eloquent object cant work.

I guess they are processed in a loop so instead of selecting them all at once you can slice it in smaller chunks :

YourModel::with('your.relationships')->chunk(100, function($your_models)
{
    foreach ($your_models as $your_model)
    {
        process($your_model);
    }
});

With the above they are processed 100 by 100

Level 11

I've updated this :

public function buildCsv($outputName, $data)
    {

        foreach($data as $row)
        {
            $this->insertRow($row);
        }

        return $this->csvManager->output($outputName . '.csv');
    }

public function buildCsv($outputName, $data)
    {

        $data->chunk(100, function($rows)
        {
            foreach ($rows as $row)
            {
                $this->insertRow($row);
            }
        });

        return $this->csvManager->output($outputName . '.csv');
    }

Where $data is always the result of the eloquent query.

But, after these 2 mins (and only after) I got :

production.ERROR: exception 'ErrorException' with message 'array_chunk() expects parameter 3 to be boolean, object given'

ps : The fact that the error is just displaying after 2 min seems to approve an eloquent/php issue or I'm again on the wrong way ? Because if the chunk is executed after 2 min, it means that it's not the biggest part of the request.

rikh

Level 3

If you can't identify exactly where the performance hit is coming from, then I would suggest stepping through the code in the debugger. When you hit a step that is taking a long time to complete, run it again and step into that function until you zero on the thing that is actually taking all your time.

If you have a profiler, this will do the hard work for you and identify exactly where all your performance is going. It is often something silly that is easy to avoid :-)

Level 56

Where $data is always the result of the eloquent query.

The problem is on the data selection. You have to use chunk to select the data. instead of get(). Here you are using the chunk method of the already selected collection.

Level 11

Exactly, I was testing this.

This reduce request time execution to 1 min. It's always too much but 50% save is really great. Thanks

Level 56

Still 1 min ?? Did you put indexes on foreign keys ?

Level 11

I'm preparing a migration to test this in production. I have to update all my repositories to include this chunk method when I need it. I'll give a feed back when all of this will deployed.

Level 11

So, after deploying code with indexes (migrations) and chunk, I've still won 50% time. So with or without indexes, it's 1min.

johanobergman

Level 6

Do you really have to use Eloquent? You would get a lot better performance if you stick with the regular query builder (like 10-30+ times faster depending on the size of the collection).

Level 11

Sure, it's a solution, but I don't thinks it's the easiest to maintain.

Level 56

Indexes should have speed up your queries, are you sure you are creating good indexes?