Analyze Query Performance


When we start programming, one thing comes in our mind, which is performance. There are many factors of performance, but query optimization/analyzing and database structure are more important.

In this article, we will evaluate the performance of the query, Analyzing query is an aspect of measurement of database and indexing effectiveness.

db.collection.explain("executionStats")  methods provide statistics about the performance of the query. This data output can be useful in measuring if and how a query uses an index.

Using $explain
It provides statistics about the performance of the query.

Evaluate the performance of query


{ "_id" : 1, "ISBN" : "22345654562349", type: " Paper", quantity: 500 }
{ "_id" : 2, " ISBN " : "3345678765678", type: " Paper ", quantity: 100 }
{ "_id" : 3, " ISBN " : "1232234543551", type: " Paper ", quantity: 200 }
{ "_id" : 4, " ISBN " : "5545533344555", type: " Paper ", quantity: 150 }
{ "_id" : 5, " ISBN " : "9988844545457", type: " Paper ", quantity: 300 }


Query With No Index


db.Book.find(
   { quantity: { $gte: 100, $lte: 200 } }
)


Query Returns result


{ "_id" : 2, " ISBN " : "3345678765678", type: " Paper ", quantity: 100 }
{ "_id" : 3, " ISBN " : "1232234543551", type: " Paper ", quantity: 200 }
{ "_id" : 4, " ISBN " : "5545533344555", type: " Paper ", quantity: 150 }



$explain on the following query:


db.Book.find(
   { quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")


The above explain() query returns the following analyzed result:


{
   "queryPlanner" : {
         "plannerVersion" : 1,
         ...
         "winningPlan" : {
            "stage" : "COLLSCAN",
            ...
         }
   },
   "executionStats" : {
      "executionSuccess" : true,
      "nReturned" : 3,
      "executionTimeMillis" : 0,
      "totalKeysExamined" : 0,
      "totalDocsExamined" : 5,
      "executionStages" : {
         "stage" : "COLLSCAN",
         ...
      },
      ...
   },
   ...
}

The fields in this result set:

queryPlanner.winningPlan.stage : COLLSCAN, which indicates a collection is scanned.

executionStats.nReturned : 3 which indicates that the query matches and returns three documents.

executionStats.totalDocsExamined : 5, which indicates that MongoDB had to scan five documents (i.e., all documents in the collection) to find the three matching documents.


Now we indexing field quantity.


db.book.createIndex( { quantity: 1 } )


To view the query plan statistics, use the explain("executionStats") method:


db.Book.find(
   { quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")



The explain() method returns the following results:


{
   "queryPlanner" : {
         "plannerVersion" : 1,
         ...
         "winningPlan" : {
               "stage" : "FETCH",
               "inputStage" : {
                  "stage" : "IXSCAN",
                  "keyPattern" : {
                     "quantity" : 1
                  },
                  ...
               }
         },
         "rejectedPlans" : [ ]
   },
   "executionStats" : {
         "executionSuccess" : true,
         "nReturned" : 3,
         "executionTimeMillis" : 0,
         "totalKeysExamined" : 3,
         "totalDocsExamined" : 3,
         "executionStages" : {
            ...
         },
         ...
   },
   ...
}


queryPlanner.winningPlan.stage : IXSCAN which indicates a the collection is scanned.

executionStats.nReturned : 3 which indicates that the query matches and returns three documents.

executionStats.totalDocsExamined : 3 which indicate that MongoDB had to scan three documents (i.e., all documents in the index) to find the three matching documents.


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...