Mongo query is taking far too long even with an appropriate index

This query is taking minutes to run:

        IMongoQuery query = Query.EQ(MongoChildData.ItemIdProperty, objectId);
        List<long> dbChildDataIdsAboveThreshold = childDataCollection.Find(query).SetSortOrder(SortBy.Ascending(MongoChildData.ItemIdProperty, MongoChildData.ChildIdProperty)).SetFields(Fields.Include(MongoChildData.ChildIdProperty)).Select(a => a.ChildId).ToList();

I have an index on the collection:

m_childDataCollection.CreateIndex(IndexKeys.Ascending(MongoChildData.ItemIdProperty).Ascending(MongoChildData.ChildIdProperty));

The collection contains a few million entries but I would expect this query to take seconds. Any ideas what I am doing wrong?

@Ian_Hannah Welcome to the forum!

Can you provide some more details to help investigate this:

  • Specific version of MongoDB server you are using.
  • Type of deployment (standalone, replica set, or sharded cluster).
  • Output of explaining the equivalent query in the mongo shell with explain("allPlansExecution") verbosity.
  • Clarification on how you are measuring the query time (for example, is this just execution time, or also including time to convert results into a list).

Thanks,
Stennie

1 Like

We are using Mongo 3.6.13 .

I am trying to use this query in mongo:

db.childdata.find({ itemId : “5c097bfe8c0e481ab8a82b6d” }).explain(“allPlansExecution”)

but it just seems to hang.

I should have said in the previous comment only a 101 ids in the ids list.

FYI I have tried this as well (using a cursor):

        List<long> ids = new List<long>();
        ObjectId objectId = new ObjectId("5c097bfe8c0e481ab8a82b6d");
        IMongoQuery query = Query.EQ(MongoChildData.ItemIdProperty, objectId);
        MongoCursor<MongoChildData> cursor = childDataCollection.Find(query).SetSortOrder(SortBy.Ascending(MongoChildData.ItemIdProperty, MongoChildData.ChildIdProperty)).SetFields(Fields.Include(MongoChildData.ChildIdProperty));
        cursor.SetFlags(QueryFlags.NoCursorTimeout);
        foreach (MongoChildData cd in cursor)
        {
            ids.Add(cd.ChildId);
        }

And it never completes. If I break the execution there are only 101 ids?

Hi Stennie,

It was the ToList() that as making the query slow.

However, if I do this:

        List<long> ids = new List<long>();
        ObjectId objectId = new ObjectId("5c097bfe8c0e481ab8a82b6d");
        IMongoQuery query = Query.EQ(MongoChildData.ItemIdProperty, objectId);
        var v = childDataCollection.Find(query).SetSortOrder(SortBy.Ascending(MongoChildData.ItemIdProperty, MongoChildData.ChildIdProperty)).SetFields(Fields.Include(MongoChildData.ChildIdProperty)).Select(a => a.ChildId);
        foreach (var b in v)
        {
            ids.Add(b);
        }

Then the query hangs after the first 100 ids. Doing a ToList() never seems to return. I estimate that there are around 700,000 child ids to return.

How can it take so long?