Uploaded image for project: 'C# Driver'
  1. C# Driver
  2. CSHARP-1717

The Aggregation Query is taking a long time to finish

    • Type: Icon: Task Task
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 1.11
    • Component/s: Performance
    • Environment:
      62 GB memory on the host, Linux 7.2, VM Host, 4 cpu
      The Database has 2 collection Call Log and ErrorLog.
      The CallLog is Indexed on CreateDateTime and is the one that we are having performance issues with.

      The Following Query is taking almost 5 - 10 minutes to execute when date range of 1 day is specified. The MongoDB has around 19,000 transactions per hour.

      Here is the MongoDB Query in C#

                 string funcList = ConfigurationManager.AppSettings["FilterTransactionFunctionList"] as string;
                     var filterList = funcList.Split(',');
      
                     var wclLogCursor = wclMongoDB.GetCollection<CallLog>("CallLog").Find(
                          Query<CallLog>.Where(p => p.CreateDateTime >= start.ToUniversalTime() && p.CreateDateTime <= end.ToUniversalTime())
                          );
      
                     var match = new BsonDocument 
                      {
                          { "$match",
                             new BsonDocument {{ "CreateDateTime", new BsonDocument {
                                                  {"$gte", start.ToUniversalTime() },
                                                  {"$lte",  end.ToUniversalTime()  }
                                              }}
                             }
                             
                      }};
      
                     var group = new BsonDocument
                      { 
                          { "$group", 
                              new BsonDocument
                              { 
                                  { "_id", new BsonDocument {{"FunctionSessionId", "$FunctionSessionId" }}}, 
                                  {"CallLogKey", new BsonDocument {{"$first", "$CallLogKey" }}}, 
                                  {"CallSessionId", new BsonDocument {{"$first", "$CallSessionId" }}}, 
                                  {"CreateDateTime", new BsonDocument {{"$first", "$CreateDateTime" }}}, 
                                  {"LastCreateDateTime", new BsonDocument {{"$last", "$CreateDateTime" }}}, 
                                  {"FunctionName", new BsonDocument {{"$first", "$FunctionName" }}}, 
                                  {"Duration", new BsonDocument {{"$first", "$Duration" }}}, 
                                  {"Direction", new BsonDocument {{"$first", "$Direction" }}}, 
                                  {"Target", new BsonDocument {{"$first", "$Target" }}}, 
                                  {"UICode", new BsonDocument {{"$first", "$UICode" }}}, 
                                  {"UserName", new BsonDocument {{"$first", "$UserName" }}}, 
                                  {"IsSucceed", new BsonDocument {{"$first", "$IsSucceed" }}}, 
                                  {"FunctionCount", new BsonDocument {{"$sum", 1}}},
                                  {"Request", new BsonDocument {{"$first", "$CallParameters" }}}, 
                                  {"Response", new BsonDocument {{"$last", "$CallParameters" }}}
                              } 
                          } 
                        };
      
                     var sort = new BsonDocument
                      {
                          {
                              "$sort",
                              new BsonDocument
                              {
                                  {"CreateDateTime", 1} , {"CreateDateTime.getTime()", 1}, 
                              }
                          }
                      };
                     var filterFunctions = new BsonDocument 
                      {
                          { "$match",
                           new BsonDocument {{ "FunctionName", new BsonDocument {
                                                  {"$nin", new BsonArray(filterList) }
                                              }}}
                             }
                             
                      };
      
                     var project = new BsonDocument
                      { 
                          { 
                              "$project", 
                              new BsonDocument
                                  { 
                                      {"_id", 0}, 
                                      {"CallLogKey", "$CallLogKey"},
                                      {"CallSessionId", "$CallSessionId"},
                                      {"CreateDateTime","$CreateDateTime"}, 
                                      {"FunctionSessionId","$_id.FunctionSessionId"}, 
                                      {"FunctionName","$FunctionName"},
                                      {"Duration", new BsonDocument(
                                                      "$subtract",
                                                      new BsonArray() {"$LastCreateDateTime","$CreateDateTime"}
                                                   )}, 
                                      {"Direction","$Direction"}, 
                                      {"Target","$Target"}, 
                                      {"UICode","$UICode"}, 
                                      {"UserName","$UserName"}, 
                                      {"IsSucceed","$IsSucceed"},
                                      {"FunctionCount","$FunctionCount"}, 
                                      {"Request","$Request"}, 
                                      {"Response","$Response"}, 
                                  } 
                          } 
                      };
      
                     var limit = new BsonDocument("$limit", 1000);
                     var pipeline = new AggregateArgs()
                     {
                         AllowDiskUse = true,
                         OutputMode = AggregateOutputMode.Cursor,
                         Pipeline = new[] { match, sort, group, filterFunctions, project }
                     };
      
                     var result = wclLogCursor.Collection.Aggregate(pipeline);
      

            Assignee:
            Unassigned Unassigned
            Reporter:
            jkhan Jawad Ahmed Khan
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: