Why use withCount() over just with()?

So the reason i ask this is that in my current app withCount() almost triples the response time vs just fetching all the data for the relationship via with() I thought the point of using withCount() was to speed up the query but maybe i'm wrong?

for example:

::where('id', '>=', 1)
->where('id', '<=', 320)
->withCount('enrollments')
->get();

averages around 900ms response

but

::where('id', '>=', 1)
->where('id', '<=', 320)
->with('enrollments')
->get();

gets around 350ms

Model Relationship is defined as follows:

public function enrollments()
    {
        return $this->hasMany(EmployeeEnrollment::class)->where('dropped', '=', null);
    }

NOTE: already checked that if i remove the where clause it only speeds it up by 30ms

Employee Enrollment Table is around 11k rows and the table the model i'm running query on is around 2k rows

anyways im kinda clueless about db stuff so any help with this would be appreciated

BryceSharp

8 years ago

Level 6

withCount() is performing with() + counting foreach of the results, thus I'd expect it to produce a slower query. The reason you would use withCount() over with() is that withCount() will give you a single number (250), while with() will give you all of the data for the 250 rows.

with() might be 550ms faster, but it leaves you with all that data. If you still need the count you will have have to spend the time to count it yourself in your code versus letting your database do it in the 550ms. Also if you're passing it to a client, you will greatly increase the size of the data you are sending.

1 like

thomaskim

8 years ago

Level 41

@BryceSharp I don't think that's correct. I believe that withCount should be running a subquery to get the count. It shouldn't call the with() method.

@JeffBeltran Are you absolutely certain that the second query is only 350ms? Your second query runs 2 separate queries whereas the first runs a subquery inside a single query.

In addition, what are you trying to achieve? In your first example (withCount), you can access the number of enrollments easily because it will be appended as an attribute of the parent. Very simple and efficient if all you want is the number of enrollments. However, if you are also trying to get other attributes that belong to enrollment, you want the with method and may accidentally be running more queries by trying to access enrollments.

In your second example (with), you can also access the number of enrollments, but you are fetching the entire "enrollment" model. You are getting a collection of these and then essentially appending each enrollment model to its respective parent. You will have access to all enrollment attributes. In addition to this, in order to get the enrollment count, you will need to run the count() method on the collection instance. You are doing a lot more to have access to a lot more data.

JeffBeltran

8 years ago

Level 5

@BryceSharp so if it's that way then it seems the only real reason would be for API design where you don't want to share that info because that is a significant performance hit

@thomaskim 350ms is the average (ran it multiple times)

I'm on a different computer now but here is the "raw" results from debugbar

For withCount('enrollments'):

select `course_sessions`.*, (select count(*) from `employee_enrollments` where `course_sessions`.`id` = `employee_enrollments`.`course_session_id` and `dropped` is null and `employee_enrollments`.`deleted_at` is null) as `enrollments_count` from `course_sessions` where `id` >= '1' and `id` <= '320' and `course_sessions`.`deleted_at` is null

That averages around 850ms

For with('enrollments') it runs two queries...

select * from `course_sessions` where `id` >= '1' and `id` <= '320' and `course_sessions`.`deleted_at` is null

averages around 1.5ms

select * from `employee_enrollments` where `dropped` is null and `employee_enrollments`.`course_session_id` in ('1', '2', '3', ... '318', '319', '320') and `employee_enrollments`.`deleted_at` is null

averages around 20ms

Overall page loads are around 1.2s using withCount() and under 500ms for with()

so even with the overhead of the extra data it's still faster... all im after is the count of enrollments but at this point it seems the best thing to do is the simply get length via JS (vue app)

Am i doing something wrong or is this just the way it has to be due to the large size of the relationship table?

Please or to participate in this conversation.