Loading data takes too much time by using pipeline in lookup in mongodb

129 views Asked by At

I have database with more than 5000 students and 300 employees with 1 MB document size each and I am using below lookup code in order to get certificates of students and employees.

$step1 =  ['$lookup' => [
        'from' => 'EmployeesTbl',
        'let' => ['certificateTypeEmployee' => '$_id'],
        'pipeline' => [
            ['$unwind' => '$CertificatesDetails'],
            ['$match' => ['$expr' => ['$eq' => ['$CertificatesDetails.documentType', '$$certificateTypeEmployee']]]],
            ['$project' => ['_id' => 1.0, 'FirstName' => 1.0, 'LastName' => 1.0, 'EmployeeNumber' => 1.0, 'CertificatesDetails' => 1.0]]
        ],
        'as' => 'employeeArray'
    ]];        


    $step2 =  ['$lookup' => [
      'from' => 'studentTbl',
      'let' => ['certificateTypeStudent' => '$_id'],
      'pipeline' => [
        ['$unwind' => '$certificates_details'],
        ['$match' => ['$expr' => ['$eq' => ['$certificates_details.documentType', '$$certificateTypeStudent']]]],
        ['$project' => ['_id' => 1.0, 'first_name' => 1.0, 'last_name' => 1.0, 'uploadsFolder' => 1.0, 'certificates_details' => 1.0, 'registration_temp_perm_no' => 1.0]]
      ],
      'as' => 'studentArray'
    ]];               
            $step3 =  [
                '$project' => [
                  "_id" => 1.0,
                  "UsedFor" => 1.0,
                  "Type" => 1.0,
                  "Code" => 1.0,
                  "Description" => 1.0,
                  "schoolId" => 1.0,
                  "employeeArray" => 1.0,
                  "studentArray" => 1.0
                ]
             ];

    $step4 =  array(
        '$match' => [
            'schoolId' => new MongoDB\BSON\ObjectID($this->schoolId),
            '$or' => [
                ['employeeArray' => ['$not' => ['$size' => 0]]],
                ['studentArray' => ['$not' => ['$size' => 0]]]
            ]
        ]
    ); 

    $step5 = [];

     ....

    $cursor = iterator_to_array($this->db->certificateTypeTbl->aggregate(array($step1, $step2, $step3, $step4, $step5, $step6, $step7, $step8, $step9, $step10, $step11, $step12, $step13, $step14, $step15, $step16, $step17, $step18, $searchCommand, $sortCommand, $skipCommand), $allowDiskCommand));

The above code is fetching data correctly but the lookup in the pipeline is causing too much time to load data... Please suggest some alternating ways in order to speed up loading time.

0

There are 0 answers