Proper way to get queries execution time

Hi !
I was comparing execution time between indexed and non-indexed values on a local database with a Java program. To get the query execution time I used the explain function and I also tried to get the execution time directly in my program to compare the values. The execution times from the explain function are much lower than the value returned by the Mongo shell for a query (btw: when executing queries from the Mongo shell and the Java program, I add the DBQuery.shellBatchSize = 10000 command to endure that all documents are returned). Is there another proper way to get the execution time of a query while using the Mongo functions?
Thanks for your help!

Hi @Steroux

Did you set the explain verbosity to executionStats this is needed to run the optimizer and the run the winning plan to completion.

3 Likes

Yes, the explain verbosity is set to executionStats, I also clear the planCache before each query execution in order to get full execution times.

With executionStats the entire query will run to completion on the server but the results ar not transmitted to the client. This could account for the discrepancy you are seeing.

Can you quantify the difference between the explain and actual execution?

Yes, the execution times obtained in milliseconds are listed in the following tab. On the left side: the explain results and on the right side: the actual execution observed.

To be more precise: the right side of the previous tab is obtained with the java program coded this way.

long start = System.currentTimeMillis();
AggregateIterable aggregateIterable = collection.aggregate(pipeline).batchSize(1000000).maxTime(Const.MAX_EXECUTION_TIME, TimeUnit.MILLISECONDS);
for (Object o : aggregateIterable) {
    //get execution time using java
    executionTimeMillis = (int) (System.currentTimeMillis() - start);
    break;
}

Here is the tab including the shell values :

avg explain time avg java time shell time
0.00 2.00 84
12.00 114.80 531
251.40 125.20 406
666.20 656.60 725
671.00 708.40 45
676.20 139.00 14
0.00 3.40 15
206.00 123.80 18
682.60 101.80 24
964.60 932.00 1008
963.40 198.60 24
946.40 104.20 13
0.00 0.80 8
0.00 0.80 10
0.00 1.60 3
8.00 94.00 28
226.60 111.80 34
659.00 656.00 724
672.80 710.80 104
678.00 163.20 13
0.00 2.40 4
190.80 116.60 20
700.00 106.60 18
970.60 951.20 1152
975.60 216.00 17
938.60 1036.20 15
0.00 0.80 1
0.00 0.80 2

Hi!
I am still looking for a solution on this topic. If anyone has an idea feel free to share! :slight_smile:
Thanks for your help!