Hii, The aggregation pipeline taking almost 20 to 25 sec to execute and give response.But inspite of creating indexes it is still taking time 20 to 22 sec.I guess these lookups takes more time ,Byt why and how I can solve this issue?
Note:It fetches mostly 30400 records. My MongoDB version is 5.0.20…
I have created Indexes,
createdAt: 1
businessUnitId: 1
commodityId: 1
commodityVariantId: 1
createdBy: 1
isDeleted: 1
isSLCMQcInspection: 1
commodityDetail.CIDNumber_text
And I am strange whenever I am using explain() with this pipeline getting “MongoInvalidArgumentError: Option “explain” cannot be used on an aggregate call with writeConcern” error.
My syntax for explain() was:
const result = await this.qcInspectionModel.aggregate([],{ explain: true })
Aggregation pipeline:
async getQCResultSLcm(
filters: GetAllQcInspectionWithFilterSlcmDto,
businessUnitId: string,
) {
let startDateQuery = {};
let endDateQuery = {};
let commoditySearchQuery = {};
let variantSearchQuery = {};
const statusQuery = {};
let cidNumberSearchQuery = {};
let lotNoSearchQuery = {};
const businessUnitFilterQuery = {};
let generalSearchQuery = {};
if (filters.startDate) {
startDateQuery = {
$expr: {
$gte: [
'$createdAt',
{
$dateFromString: {
dateString: filters.startDate,
timezone: '+05:30',
format: '%m-%d-%Y',
},
},
],
},
};
}
if (filters.endDate) {
endDateQuery = {
$expr: {
$lt: [
'$createdAt',
{
$dateAdd: {
startDate: {
$dateFromString: {
dateString: filters.endDate,
timezone: '+05:30',
format: '%m-%d-%Y',
},
},
unit: 'day',
amount: 1,
},
},
],
},
};
}
// if (filters.startDate) {
// startDateQuery = { createdAt: { $gte: DateTime.fromFormat(filters.startDate, "MM-dd-yyyy").setZone("+05:30").toJSDate() } }
// }
// if (filters.endDate) {
// endDateQuery = { createdAt: { $lt: DateTime.fromFormat(filters.startDate, "MM-dd-yyyy").plus({ days: 1 }).setZone("+05:30").toJSDate() } }
// }
if (filters.searchByCommodity) {
commoditySearchQuery = {
'commodityData.name': {
$regex: `${filters.searchByCommodity}`,
$options: 'i',
},
};
}
if (filters.searchByVariant) {
variantSearchQuery = {
'commodityVariantData.name': {
$regex: `${filters.searchByVariant}`,
$options: 'i',
},
};
}
if (filters.searchByStatus) {
statusQuery['status'] = filters.searchByStatus;
}
if (filters.searchByCIDNumber) {
cidNumberSearchQuery = {
$or: [
{
'commodityDetail.CIDNumber': {
$regex: `${filters.searchByCIDNumber}`,
$options: 'i',
},
},
// {
// 'businessUnitData.name': {
// $regex: `${filters.searchByCIDNumber}`,
// $options: 'i',
// },
// },
// {
// 'commodityDetail.LOTNumber': {
// $regex: `${filters.searchByLotNo}`,
// $options: 'i',
// },
// },
// {
// 'qcId': {
// $regex: `${filters.searchByCIDNumber}`,
// $options: 'i',
// },
// },
],
};
}
if (filters.searchByLotNo) {
lotNoSearchQuery = {
$or: [
{
'commodityDetail.LOTNumber': {
$regex: `${filters.searchByLotNo}`,
$options: 'i',
},
},
],
};
}
if (filters.searchByGeneralSearch) {
generalSearchQuery = {
$or: [
{
qcId: {
$regex: `${filters.searchByGeneralSearch}`,
$options: 'i',
},
},
{
'businessUnitData.name': {
$regex: `${filters.searchByGeneralSearch}`,
$options: 'i',
},
},
{
'userData.name': {
$regex: `${filters.searchByGeneralSearch}`,
$options: 'i',
},
},
],
};
}
if (businessUnitId) {
businessUnitFilterQuery['businessUnitId'] = new mongoose.Types.ObjectId(
businessUnitId,
);
}
// const startTime = Date.now();
const result = await this.qcInspectionModel.aggregate([
{
$match: {
$and: [
startDateQuery,
endDateQuery,
statusQuery,
businessUnitFilterQuery,
{ isDeleted: false },
{ isSLCMQcInspection: true },
],
},
},
{
$lookup: {
from: 'mastercommodities',
localField: 'commodityId',
pipeline: [
{
$project: {
name: 1,
},
},
],
foreignField: '_id',
as: 'commodityData',
},
},
{
$unwind: '$commodityData',
},
{
$lookup: {
from: 'commodityvariants',
localField: 'commodityVariantId',
pipeline: [
{
$project: {
name: 1,
},
},
],
foreignField: '_id',
as: 'commodityVariantData',
},
},
{
$unwind: '$commodityVariantData',
},
{
$lookup: {
from: 'businessunits',
localField: 'businessUnitId',
pipeline: [
{
$lookup: {
from: 'businesses',
localField: 'businessId',
foreignField: '_id',
as: 'businessClientName',
},
},
{
$unwind: '$businessClientName',
},
{
$project: {
name: 1,
businessClientName: '$businessClientName.displayName',
},
},
],
foreignField: '_id',
as: 'businessUnitData',
},
},
{
$unwind: {
path: '$businessUnitData',
preserveNullAndEmptyArrays: true,
},
},
{
$lookup: {
from: 'users',
localField: 'createdBy',
foreignField: '_id',
as: 'userData',
pipeline: [
{
$project: {
firstName: 1,
lastName: 1,
_id: 0,
name: { $concat: ['$firstName', ' ', '$lastName'] },
},
},
],
},
},
{
$unwind: {
path: '$userData',
preserveNullAndEmptyArrays: true,
},
},
{
$match: {
$and: [
commoditySearchQuery,
variantSearchQuery,
generalSearchQuery,
cidNumberSearchQuery,
lotNoSearchQuery
],
},
},
{
$sort: {
createdAt:
filters.sortOrder && filters.sortOrder != SortOrder.Ascending
? SortOrder.Descending
: SortOrder.Ascending,
},
},
{
$project: {
_id: 1,
status: 1,
commodityData: 1,
commodityDetail: 1,
commodityVariantData: 1,
createdAt: 1,
qcId: 1,
sampleName: 1,
businessUnitData: 1,
userData: 1,
location: 1,
middlewareStatus: 1,
},
},
{
$facet: {
records: [
{ $skip: (filters.pageNumber - 1) * filters.count },
{ $limit: filters.count * 1 },
],
total: [{ $count: 'count' }],
},
}
]);
// const endTime = Date.now();
// const executionTimeMs = endTime - startTime;
// console.log('Execution time:', executionTimeMs, 'ms');
return result;
}