-
Type:
Task
-
Resolution: Done
-
Priority:
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.
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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);