Aggregate Faster Than Find

Anze_Kozole · 2022-02-07T11:42:03.252Z

I am trying to compare the performance of querries in Node.js and PHP. In PHP everything works fine. The problem is in Node.js where i am using a .aggregate() query with find and sort, and I have a simple .find() query.
The problem is that the simple .find() query is slower than .aggregate() query. Have looked through multiple documentations and answered questions but I couldn’t find a proper answer why is this so.
The .find() query is just used to get the image name. (images are saved on the local server. Only the name of the image is saved in the database).
The .aggregation() and lookup is used to get data from the forum collcetion and to get the author of the posted post from another collection.

I have the same query in PHP but there this simple find() query is faster than aggregate() as It should be ubt not in Node.js.

This is my first simple find() query

  const timeFunction2 = new Promise((resolve, reject) => {
    var startTime = performance.now();

    setTimeout(() => {
      for(var i = 0; i < 100000; i++){
      conn.collection('galery').find({ "user_id": req.session.userId}).toArray();

    };
      var endTime = performance.now();
      resolve(endTime - startTime);
    });
  });

  timeFunction2.then(time => {
    console.log(`${time} ms.`);
  });

And this is my aggregate(); query

  const timeFunction = new Promise((resolve, reject) => {
    var startTime = performance.now();

    setTimeout(() => {
      for(var i = 0; i < 100000; i++){
          conn.collection('forum').aggregate([{$lookup:
                 {
                    from: "user",
                    localField: "user_id",
                    foreignField: "_id",
                    as: "join"
                }},
                {
                  $unwind: "$join",
                },
                {
                  $sort: {"date": -1}
                },]).toArray();

    };
    var endTime = performance.now();
      resolve(endTime - startTime);
    });
  });

  timeFunction.then(time => {
    console.log(`${time} ms.`);
  });

Have also tried on lower samples and it is still the same. As i went through other questions and documentations find() should be faster but for my case it is not like that. Is there a mistake in my code or what is wrong?

Prasad_Saya · 2022-02-07T12:36:08.845Z

Hello @Anze_Kozole, welcome to the MongoDB Community forum!

Here are some information about measuring the performance of a query.

For a given collection and query (for example, filter, sort, projection), when you run the queries from the same client, you can assume that the performance will be the same.

Note that, to determine the performance of the query (using find or aggregate), you can use the db.collection.explain and get the query plan document which has the Explain Results. When you run the explain, use the "executionStats" verbosity (or mode), so that you can see the "executionTimeMillis" field in the Explain Result which gives the time of execution.

Each time the find or aggregate method id run, the client program (e.g., mongosh or NodeJS program) sends the request to the MongoDB database server, the query is executed on the server and the results are returned to the client. The time of execution, database performance, is generally measured in terms of the time it takes for the query to run on the database server. It doesn’t include the time it takes for the query to travel to the server over the network, and the time to bring the results back to the client over the network. So, use the Explain Results to measure the time it takes to run the query on the server.

In general, database performance and query performance are influenced by many factors. Feel free to search the documentation, the MongoDB Manual, for any specific topic.

Anze_Kozole · 2022-02-07T13:07:17.847Z

Thanks for reply and help. In my case the execution time of find() query is probably longer because of all the things the query needs to do on the server (with node.js code) in order to fully execute alongside the code. So the problem is probably not in the query but in the whole process if I am correct.

santimir · 2022-02-07T13:24:42.464Z

You could test what was previously said by @Prasad_Saya by adding the explain() method to your queries, and averaging the execution time.

It will give you a hint as to where is the bottleneck.

santimir · 2022-02-07T13:31:12.388Z

BTW, those are complete different queries to different collections so it will depend on the number of documents, indexes etc.

Doesn’t really tell anything about the methods, unless you’re cross comparing with php which is another story…

Anze_Kozole · 2022-02-07T13:35:07.093Z

Both queries have the same amount of documents and PHP has also the same data in databases and collections. But yes I will try what @Prasad_Saya suggested.

santimir · 2022-02-07T13:40:24.373Z

Do you mean Gallery and forums are exactly the same? Otherwise that’s the main difference…

Anze_Kozole · 2022-02-07T13:42:39.043Z

No they are not the same. And I know that there would be a difference in performance. But the problem is that the galery should be faster since its just a simple query but its not. In PHP for example the galery is faster.

Prasad_Saya · 2022-02-07T14:06:55.819Z

Anze_Kozole:

In my case the execution time of find() query is probably longer because of all the things the query needs to do on the server (with node.js code) in order to fully execute alongside the code.

Yes, the NodeJS code (e.g., using MongoDB API’s find) doesn’t execute on the database server. The NodeJS driver sends the query to the database server where the query is executed. If your NodeJS code has a for-loop it runs on the application / web server where the NodeJS program is running.

steevej · 2022-02-07T14:36:48.041Z

I want to add the following.

That kind of look is worthless when testing for performance.

for(var i = 0; i < 100000; i++){
      conn.collection('galery').find({ "user_id": req.session.userId}).toArray();

    };

That is not realistic of any use case. You are querying the same document over and over. So if the document has to be read from disk it is read only once. Then it sits in cache and it is very fast to retrieve. You should be query for many different user_id, not just one.

I concur with @santimir, 2 completely different queries on 2 different collections for which we have no sample documents, no stats about the number of documents in each, no information about any indexes, no any idea about the number of documents that matches the queries. You mentioned PHP but you do not share the code. Yes a simple find is supposed to be faster than an aggregate, but not if your find returns 10 thousand documents and your aggregate only a few.

That is why you really have to do what @Prasad_Saya mentioned about Explain Results.

Trying to help you is like shooting in the dark on a moving target.