Lazy Loading Faster than Eager loading?

[chrismeats]

Summary of problem or feature request

I have a simple product hasMany variants relationship. I can not figure out a slow performance issue. If I eager load my variants relationship: $query->with('variants'); Performance takes a serious hit and takes 3-4 seconds to load the page. Even though debug bar does not show any slow queries. and all queries execute in about 131ms

If I remove that eager load and just do lazy loading, page loads in 522ms even though there are 100 lazy loaded, essentially duplicate, queries! all queries execute in about 237ms

The slow down seems to come sometime during call to return $table->make(true);

I believe I have relevant code copied below. Thank you in advance for any help!

Code snippet of problem

$query = Product::with(['pc_variants'])->select(sprintf('%s.*', (new Product)->table));

$table = Datatables::of($query);
// --- Some other addColumn, editColumn, I have tried changing all my edit columns to just return 1 to ensure nothing there was slowing things down --- 
return $table->make(true); // This seems to be where the speed issue happens

System details

• Operating System -- Mac Ventura 13.2 using Valet
• PHP Version -- 8.1
• Laravel Version -- 10.4
• Laravel-Datatables Version -- 10.3.1

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[chrismeats]

bump

[ardavan-ansari]

Sorry to bump again, but I'm really struggling with this issue. Any updates/workarounds?

[yajra]

I think eager loading should be faster. Try reviewing the SQL generated by the package by inspecting the ajax request with APP_DEBUG=TRUE. You might be having an N+1 issue in your code.

[chrismeats]

@yajra If you take a look at my original post its actually the opposite of that. My queries are executing very quickly, however, when I eager load, my query still executes quickly, but total load time takes a major hit! Doing some additional debugging I determined the slow down occurs somewhere in: return $table->make(true);

[ardavan-ansari]

@yajra I have also checked the queries (and everything else I could think of) thoroughly, and I'm using eager loading to fix the N+1 issue.

However, as @chrismeats states, when I eager load the relationships, the queries are quick, but something else is taking a long time to load (the performance hit is sometimes worse than N+1 issue).

[yajra]

Encountered something similar but the slow response was due to a binary(blob) field being included in the response. Removing the blob field in select SQL fixes the issue. Do any of you have blob fields?

[ardavan-ansari]

@yajra No blobs for me, I only have bigint, varchar(255), longtext, char(36), enum & timestamp in my tables

Edit: added enum

[yajra]

Can you try removing the longtext in your select sql?

[chrismeats]

@yajra No blobs for me either I had a couple long text fields, I tried removing those from the select and still have the same issue.

[ardavan-ansari]

I tested all the column types I have, longtext doesn't seem to have an issue (performance cost was less than timestamp).

However it appears that the more columns we eager load, the worse the performance becomes.

[chrismeats]

@ardavan-ansari Have you been able to find any kind of fix or work around for this?