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.)
|