我有一个复杂的聚合管道，但它的工作完美时，有较少的数据收集，如300，400，但在大量的数据，如20000，30000它需要更长的时间来获取和生成一个pdf，该pdf包括所有的30000数据，所以如何加快这一进程。只有通过索引或isthere任何其他方式来实现这一点？帮帮忙......
下面我提供了service.ts文件，
async getQCResultSLcmExcel(
filters: GetAllQcInspectionWithFilterSlcmDto,
businessUnitId: string,
) {
let startDateQuery = {};
let endDateQuery = {};
let commoditySearchQuery = {};
let variantSearchQuery = {};
const statusQuery = {};
let cidNumberSearchQuery = {};
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.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.searchByCIDNumber}`,
$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 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',
foreignField: '_id',
as: 'businessUnitData',
pipeline: [
{
$project: {
name: 1,
businessUnitCode: 1,
_id: 0,
},
},
],
},
},
{
$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,
],
},
},
{
$sort: {
createdAt:
filters.sortOrder && filters.sortOrder != SortOrder.Ascending
? SortOrder.Descending
: SortOrder.Ascending,
},
},
{
$project: {
_id: 0,
'QC ID': {
$cond: {
if: '$qcId',
then: '$qcId',
else: '',
},
},
'CID Number': {
$cond: {
if: '$commodityDetail',
then: '$commodityDetail.CIDNumber',
else: '',
},
},
Commodity: {
$cond: {
if: '$commodityData',
then: '$commodityData.name',
else: '',
},
},
Date: {
$cond: {
if: '$createdAt',
then: {
$dateToString: { format: '%d-%m-%Y', date: '$createdAt' },
},
else: '',
},
},
'Analyst Name': {
$cond: {
if: '$userData',
then: '$userData.name',
else: '',
},
},
'Business Unit': {
$cond: {
if: '$businessUnitData',
then: '$businessUnitData.name',
else: '',
},
},
'LOT No': {
$cond: {
if: '$commodityDetail',
then: '$commodityDetail.LOTNumber',
else: '',
},
},
Location: {
$cond: {
if: '$location',
then: '$location.name',
else: '',
},
},
'Middleware Status': {
$cond: {
if: '$middlewareStatus',
then: '$middlewareStatus',
else: '',
},
},
Status: {
$cond: {
if: '$status',
then: '$status',
else: '',
},
},
},
},
]);
if (result && result.length > 0) {
const payload = await generateExcel(result, 'slcm-qc-reports');
const excelData = await this.uploadService.fileUpload(payload);
return excelData.Location || '';
} else {
return ERROR_MESSAGE.NO_DATA_EXPORT;
}
}感谢您的评分
1条答案
按热度按时间chhqkbe11#
正则表达式总是很慢，你真的需要它们吗？
您可以通过使用第三方日期库使日期过滤器更有效，例如Luxon或moment.js或Day.js
我会这样做：
当输入数据根本没有时间信息时，使用时区看起来有点奇怪。注意，像Luxon这样的库提供了像
startOf('day')这样的方法，它们也知道时区。我假设大多数
$lookup只返回一个文档。在这种情况下，您不需要$unwind。预测，如可能会更快。
$match运算符也适用于数组，也许你甚至可以跳过$unwind/$set。把条件尽早，他们也可以$lookup的一部分。除此之外，四个
$lookup是相当多的，你可以检查你的数据模型。像MongoDB这样的NoSQL数据库并没有针对连接进行优化。考虑嵌入式文档。