Eloquent is so slow ...

Level 88

You probably have something wrong in the query your created with Eloquent because Eloquent is really fast, I have millions of records and I don't have to wait longer the 2 seconds... Never

Level 11

No, i am sure, try eloquent query with "where id IN ( [array with 10.000 ids] )".

janareit

Level 4

@inyansuta I tested L5 before starting to learn Laravel further and I db:seed-ed 1,5 million blog posts with 1,5 million comments on them to test Laravel speed. I didn't do any modifications to my install and used simple MySQL InnoDB. Normal Eloquent queries with pagination to pages took ~600-800ms. So it must be something with your script...

1 like

bobbybouwmann

Level 88

@janareit exactly ;)

Level 11

Mayby yes... This is what I try:

Module Product (table products has 35894 rows):

public function prices()
   {
      return $this->hasMany('Prices');
   }

This query = 30 seconds

Product::select(['id'])->with('prices')->take(10000)->get();

If i log all Eloquent queries - here is sql queries output:

{
"query": "select `id` from `products` limit 10000",
"bindings": []
},
{
"query": "select * from `prices` where `prices`.`product_id` in (?, ?, ...,)", // 10.000x "?"
"bindings": [2, 8, ...] // 10.000x "id"
}

And returned data (38.486 rows):

{
"id": "1",
    "prices": [
    {
        "id": "165270",
        "product_id": "1",
        "price_group_id": "2",
        "price": "68.55"
    },
    {
        "id": "165270",
        "product_id": "1",
        "price_group_id": "3",
        "price": "52.55"
    }
    ]
},
{
"id": "2",
    "prices": [
    {
....

What is wrong?

And if I debug this in eloquent Connection class (i wrote in first topic), i am shure, that problem is here:

$statement = $this->getPdoForSelect($useReadPdo)->prepare ($query);
$statement->execute($me->prepareBindings($bindings));
$data = $statement->fetchAll (); // here is problem - this query takes 30 seconds

If at this point in the class Connection perform debug, and those same questions that are performed in the Eloquent done using their own pdo connections without using pdo prepare - execute - then the second part will take place in 0.1 seconds ...

// class Connection, original code
$statement = $this->getPdoForSelect($useReadPdo)->prepare ($query);
$statement->execute($me->prepareBindings($bindings));
$data = $statement->fetchAll (); //takes 30 seconds

// just added my code below
$dbh = new PDO('mysql:dbname=' . DB_DATABASE . ';dbhost=' . DB_HOST, DB_USER, '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->query(the_some_sql_query_with_10000_in_IN_without_prepare_and_execute);
$data = $stmt->fetchAll(); // takes 0.1 second

pmall

Level 56

Sure it is slow, first it fetch all the record in one time, then it instantiate an eloquent object for each lines. It is not made to retrieve 10 000 objects.

Also, please be sure you didn't forgot an index on prices.product_id ...

However you can try to do something with chunk :

If you need to process a lot (thousands) of Eloquent records, using the chunk command will allow you to do without eating all of your RAM:

User::chunk(200, function($users)
{
    foreach ($users as $user)
    {
        //
    }
});

2 likes

Level 11

Can someone please explain?

Eloquent - original code, class Connection / method select()

$bindings = ["32", "54", "67, ..."]; // 10.000 id in array
$query = "select * from `prices` where `prices`.`product_id` in (?, ?, ?)"

$statement = $this->getPdoForSelect($useReadPdo)->prepare($query);
$statement->execute($me->prepareBindings($bindings));
$data = $statement->fetchAll(); // - this is very SLOW (30 seconds)

Eloquent - my testing modification of the original code above

$bindings = ["32", "54", "67, ..."]; // 10.000 id in array
$query = "select * from `prices` where `prices`.`product_id` in (" . implode(',', $bindings) . ")"

$statement = $this->getPdoForSelect($useReadPdo)->query($query);
$data = $statement->fetchAll(); // - this is very FAST (0.1 seconds)

I love Eloquent, but throughout the day can not identify the cause.

Please help me, i dont want use the "old wy " with "select * from products left join prices ..." (this is fast without problem), but i want use Product::with('prices')... but i not understand problem above...

acasar

Level 13

@inyansuta The query executed by Eloquent runs equally fast as plain PDO. The problem occurs after that - Eloquent takes the results of the query (approx. 38000 rows) and converts each row into an object. So basically it creates 38000 objects, which is slow. When dealing with that amount of results, you should really use the query builder:

$results = DB::select( DB::raw($query), $bindings );

This will return a plain array and will be much faster.

RemiC

Level 8

@inyansuta : from what I see in your test, the slowness comes from prepared statement, not Eloquent itself. Such difference seems, indeed, anormal to me.

Level 11

@anzze Yes, but if I'm not mistaken Eloquent creates individual objects after the end of the line:

return $statement->fetchAll($me->getFetchMode());

Yes, transferring huge amounts of records to objects not solved yet, respectively. the subsequent conversion anymore so long ... The problem is that just above line takes so long (in case it is used in large quantities id). I do not understand that. Eloquent sets or somewhere something else starts with each pass fetchAll native PDO? I do not think it is possible, moreover, I searched and found nothing of the sort. Thanks for the additional tips and insights to the above problem.

marcoacm

Level 1

What was the solution for this? I have the problem... Please let me know how you fixed it, I can't follow the thread very well.

TheDarkITA

Level 1

Use subquery...

tylernathanreed

Level 14

You could also use ->toSql() before calling the Eloquent Query to see what it resolves to.

pmall