Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-11307

Large $or clauses are extremely slow when compared to other access methods

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Done
    • Affects Version/s: 2.5.3
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
    • Environment:
      nodejs driver
    • Backwards Compatibility:
      Fully Compatible

      Description

      I was building a joining utility, in order to retrieve subdocuments, I built a query of or clauses matching each sub document to retrieve. The results were not terribly surprising at first, the query was very slow to respond.

      What was surprising was when I began to page my actual queries, that is to say divide the entries in the $or clause into many separate db queries matching 50 of the joining documents at a time, the database began responding much much faster.

      A join query for 1000 document took a minute without paging. With a small page size of 5 documents per join query, that time went down to 177ms.

      Querying for one document at a time was still at 214ms, way faster than where I started.

      So what I mean to say is that I think there is something wrong with the $or clause if it basically takes longer to execute a single query rather that 1000 small queries.

      Perhaps there is a limitation I'm not aware and that's why I marked this as an improvement and not a bug

      Here are the results of my testing
      Documents Page Size Total Time (ms)
      1000 200 1236
      1000 100 733
      1000 50 407
      1000 25 271
      1000 10 192
      1000 5 177
      1000 1 214

      10000 1000 59929
      10000 500 30566
      10000 200 12918
      10000 100 6708
      10000 50 4082
      10000 25 2804
      10000 10 1892
      10000 5 1696
      10000 1 2550

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: