[SERVER-11307] Large $or clauses are extremely slow when compared to other access methods Created: 22/Oct/13  Updated: 10/Dec/14  Resolved: 14/Jul/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.5.3
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Kyle Housley Assignee: Ramon Fernandez Marina
Resolution: Done Votes: 0
Labels: query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

nodejs driver


Attachments: Microsoft Word mongodb-paged-join-performance.xlsx    
Backwards Compatibility: Fully Compatible
Participants:

 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



 Comments   
Comment by Ramon Fernandez Marina [ 14/Jul/14 ]

khous, we haven't heard back from you for a while, so we're going to mark this ticket as resolved. If this is still an issue for you, feel free to re-open and provide additional information.

Regards,
Ramón.

Comment by Ramon Fernandez Marina [ 03/Jul/14 ]

Hi khous, is this still an issue for you? If you've upgraded to older versions and you're still seeing slow $or queries, could you please let us know? If the behavior you describe wasn't fixed in 2.5.5 we would like to investigate why.

Comment by Ramon Fernandez Marina [ 20/Jun/14 ]

khous, apologies for the late reply. Have you seen Asya's comment and try to reproduce in 2.5.5? If you upgrade to a version >= 2.5.5 or anything in the 2.6 series and continue to see the behavior you describe, can you please send us the actual query so we can try to reproduce and investigate on our end?

Comment by Asya Kamsky [ 26/Oct/13 ]

I didn't see your exact query, but it's possible that this is a duplicate of SERVER-1205 if it's an $or combined with a sort.

Generated at Thu Feb 08 03:25:26 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.