[DOCS-12985] Too slow $facet with index Created: 26/Aug/19  Updated: 26/Aug/19  Resolved: 26/Aug/19

Status: Closed
Project: Documentation
Component/s: None
Affects Version/s: 3.6.14
Fix Version/s: None

Type: Task Priority: Major - P3
Reporter: Murilo Kunze Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: facet
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows


Attachments: File db.stats.json     File indices.json     File with $facet.js     PNG File with facet.PNG     File without facet-1.js     File without facet-2.js     PNG File without facet.PNG    
Participants:
Days since reply: 4 years, 24 weeks, 2 days ago

 Description   

Description

I noticed that using $facet with large amount of data is a lot slow than making multiple calls to database. In my example, my collection has 160000 records, 56000 is ChannelCode "dtm".

Using $facet it takes 600ms to complete:

db.getCollection('application_view').aggregate(
[
{
"$match":

{ "ChannelCode": "dtm" }

},
{
"$facet": {
"results": [
{ "$sort":

{ "ApplicationDate": 1 }

},

{ "$skip": 0 }

,

{ "$limit": 10 }

],
"total": [

{ "$count": "count" }

]
}
}
]
)

Making 2 calls to db takes 600ms too, but when I add an index

{StatusCode: 1, ApplicationDate: 1}

it decreases to 54ms, but the example with $facet continues the same:

db.getCollection('application_view').aggregate([
{$match: {"ChannelCode": "dtm"}},
{$sort: {ApplicationDate: 1}},
{$skip: 0},
{$limit: 10}
])

db.getCollection('application_view').aggregate([
{$match: {"ChannelCode": "dtm"}},
{$count: "count"}
])

 Why this is happening? How can I improve the time with $facet?
Thanks in advance.

Scope of changes

Impact to Other Docs

MVP (Work and Date)

Resources (Scope or Design Docs, Invision, etc.)



 Comments   
Comment by Ravind Kumar (Inactive) [ 26/Aug/19 ]

Hi murilokunze,

 The best place for you to issue your question is on our MongoDB Community Support Forum. The MongoDB Community Support forum is a community forum where experienced MongoDB users and MongoDB Community Support Engineers participate in discussions. I would strongly recommend including the data you have here to help with diagnostics and discussions.

The MongoDB Documentation Team cannot provide product support or diagnostics. However, if your post to the google group results in a documentation change request, we would be happy to assist at that time.

Regards,

Ravind Kumar
Senior Technical Writer, Server 

Generated at Thu Feb 08 08:06:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.