Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-12985

Too slow $facet with index

    XMLWordPrintableJSON

Details

    • Icon: Task Task
    • Resolution: Won't Do
    • Icon: Major - P3 Major - P3
    • None
    • 3.6.14
    • None
    • Windows

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

      Attachments

        1. db.stats.json
          0.3 kB
        2. indices.json
          0.4 kB
        3. with $facet.js
          6 kB
        4. with facet.PNG
          with facet.PNG
          21 kB
        5. without facet.PNG
          without facet.PNG
          50 kB
        6. without facet-1.js
          5 kB
        7. without facet-2.js
          4 kB

        Activity

          People

            Unassigned Unassigned
            murilokunze Murilo Kunze
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              4 years, 24 weeks, 2 days ago