Details
-
Task
-
Status: Closed
-
Major - P3
-
Resolution: Community Answered
-
1.11
-
None
-
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.
Description
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);
|