C# .NET Core 3.1 - Driver (2.11.1) - Slow ToList() data manifestation

Hello,
I have a performance issue that I can’t ignore.
I have a collection with 1000 document. This collection will not grow so the number is stable.
Each document weight 1kb. A single document has one array of subdocument and some other properties.

What I do is:

var result = db.collection("A")
            .Aggregate()
            .Match(Builders<T>.Filter.Eq("<ArrayName>.<PropertyName>", "name"))
            .ToList();

The result of the query operation in MongoDbCompass is 1ms for 200 document and its super ok.
The result of the ToList() of the documents is 10 seconds.

This happen even with property not inside an array.

What can I do ?
Thanks

Edit
I have indexes in place for the properties I need to search for.
Also this result are from localhost:27017, so all in local.

I tested with the FindSync() and FindAsync() and I get the same results.
I tried .Skip(n) and Limit(n + 20) and the performance for 20 documents are 1.5 second. A lot.

Can you run the same query in the Mongo shell, mongo and see if you get similar results? You should also try and run the explain() for the query and post it here.

the explain docs are here.

Hello Joe,
thanks for the reply.

I run ‘db.collection.find(…)’ and got the results. The performance was strictly bettere than the ToList()

I also run the ‘db.collection.find().explain()’ and this is the result:

{
queryPlanner: {
plannerVersion: 1,
namespace: 'FirstCollection.Pokemon',
indexFilterSet: false,
parsedQuery: { 'moves.name': { '$eq': 'thunder' } },
winningPlan: {
  stage: 'FETCH',
  inputStage: {
    stage: 'IXSCAN',
    keyPattern: { 'moves.name': 1 },
    indexName: 'MovesName',
    isMultiKey: true,
    multiKeyPaths: { 'moves.name': [ 'moves' ] },
    isUnique: false,
    isSparse: false,
    isPartial: false,
    indexVersion: 2,
    direction: 'forward',
    indexBounds: { 'moves.name': [ '["thunder", "thunder"]' ] }
  }
},
    rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 232,
executionTimeMillis: 0,
totalKeysExamined: 232,
totalDocsExamined: 232,
executionStages: {
  stage: 'FETCH',
  nReturned: 232,
  executionTimeMillisEstimate: 0,
  works: 233,
  advanced: 232,
  needTime: 0,
  needYield: 0,
  saveState: 0,
  restoreState: 0,
  isEOF: 1,
  docsExamined: 232,
  alreadyHasObj: 0,
  inputStage: {
    stage: 'IXSCAN',
    nReturned: 232,
    executionTimeMillisEstimate: 0,
    works: 233,
    advanced: 232,
    needTime: 0,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    keyPattern: { 'moves.name': 1 },
    indexName: 'MovesName',
    isMultiKey: true,
    multiKeyPaths: { 'moves.name': [ 'moves' ] },
    isUnique: false,
    isSparse: false,
    isPartial: false,
    indexVersion: 2,
    direction: 'forward',
    indexBounds: { 'moves.name': [ '["thunder", "thunder"]' ] },
    keysExamined: 232,
    seeks: 1,
    dupsTested: 232,
    dupsDropped: 0
  }
  }
 },
serverInfo: {
host: 'DESKTOP-CS3UEH8',
port: 27017,
version: '4.4.0',
gitVersion: '563487e100c4215e2dce98d0af2a6a5a2d67c5cf'
 },
 ok: 1
}

Can you post the output of .explain("executionStats"). This will give a more detailed report. I can see you are using an index stage: 'IXSCAN' which is good. What we are trying to do here is eliminate the possibility that the database is the bottleneck. the executionStats parameter will give us a lot more detail about where the time is spent.

In the previous post if you scroll down the section code you can see the “executionStats”.
Sorry I didn’t specify that I used the .explain("executionStats")

What I see slowing down it is the actual manifestation of the data.
I know I am trying to retrieve a List<BsonDocument> where each document has all the properties, array etc.
I can of course set a $projection to limit what to retrieve (and I will do eventually) but because I want to learn how mongodb works I am trying to understand why this is happening.

I see it now. So the actual query time is effectively 0. So your delay is not in the query or the database engine. Could it be n/w or client delays?

I don’t think so. I am running all locally (mongodb is hosted in localhost:27017). All C# in a basic Console Application project with no client side.
I am doing like this just to understand the MongoDb C# Driver.

So I don’t really know.

Put a time stamp either side of the query on the client. It may just take a while to start your program. How long does it take from the program side to run the query.

So, as I wrote I am doing this in a ConsoleApplication NetcoreApp 3.1.
I executed 2 different ways of querying, one with FindSync() the other with Aggregate().Match().

The first one (FindSync):

The second one (Aggregate):

I am going to ask my C# colleague @yo_adrienne to take a look. She is based in Nevada so it will be a few hours.

1 Like

Hi @Andrea_Zanini!

Can you try your queries but with ToListAsync()?

And if possible, can you also log out the execution stats?

Hello, sorry for the late answer.

I tried the same thing with the await query.ToListAsync(); but even with that the time it takes for manifest the data is the same.

For the execution stats do you mean what @Joe_Drumgoole asked me? Or I can, with C# code, somehow use the execution stats for the ToListAsync() operation?