Aggregation very slow

About the Community Welcome
1

Hello,
I have a aggreation very slow :

[
  {
    $project: {
      _id: 0,
      XXX: 1,
      AAA: 1,
      BBB: 1,
      VVV: 1,
      CCC: 1,
      HHH: 1,
      EEE: 1
    }
  },
  {
    $lookup: {
      from: "COL2",
      let: {
        XXX: "$XXX",
        AAA: "$AAA",
        BBB: "$BBB",
        VVV: "$VVV"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$XXX", "$$XXX"]
                },
                {
                  $eq: ["$AAA", "$$AAA"]
                },
                {
                  $eq: ["$BBB", "$$BBB"]
                },
                {
                  $eq: ["$VVV", "$$VVV"]
                }
              ]
            }
          }
        }
      ],
      as: "HG"
    }
  },
  {
    $match: {
      "HG": { $exists : true, $not : { $size: 0 } }
    }
  },
  {
    $unwind: {
      path: "$HG"
    }
  },
  {
    $lookup: {
      from: "COL3",
      let: {
        OOO: "$HG.OOO",
        UUU: "$HG.UUU"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$OOO", "$$OOO"]
                },
                {
                  $eq: ["$UUU", "$$UUU"]
                }
              ]
            }
          }
        }
      ],
      as: "HA"
    }
  },
  {
    $match: {
      "HA": { $exists : true, $not : { $size: 0 } }
    }
  },
  {
    $unwind: {
      path: "$HA"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          {
            QQQ: "$HG.QQQ",
            OOO: "$HG.OOO",
            UUU: "$HG.UUU",
            FFF: "$HA.FFF",
            UUU: "$HA.UUU",
            OOO: "$HA.OOO"
          }
        ]
      }
    }
  },
  {
    $project: {
      XXX: 1,
      AAA: 1,
      BBB: 1,
      CCC: 1,
      FFF: 1,
      HHH: 1,
      EEE: 1,
      QQQ: 1,
      UUU: 1,
      OOO: 1
    }
  },
  {
    $out: "temp_col"
  }
]

Some can say me what’s wrong with my query ?
Many thanks

2

[quote=“Espinoza_Jean bcbsfl, post:1, topic:280856, full:true”]
Hello,
I have a aggreation very slow :

[
  {
    $project: {
      _id: 0,
      XXX: 1,
      AAA: 1,
      BBB: 1,
      VVV: 1,
      CCC: 1,
      HHH: 1,
      EEE: 1
    }
  },
  {
    $lookup: {
      from: "COL2",
      let: {
        XXX: "$XXX",
        AAA: "$AAA",
        BBB: "$BBB",
        VVV: "$VVV"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$XXX", "$XXX"]
                },
                {
                  $eq: ["$AAA", "$AAA"]
                },
                {
                  $eq: ["$BBB", "$BBB"]
                },
                {
                  $eq: ["$VVV", "$VVV"]
                }
              ]
            }
          }
        }
      ],
      as: "HG"
    }
  },
  {
    $match: {
      "HG": { $exists : true, $not : { $size: 0 } }
    }
  },
  {
    $unwind: {
      path: "$HG"
    }
  },
  {
    $lookup: {
      from: "COL3",
      let: {
        OOO: "$HG.OOO",
        UUU: "$HG.UUU"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$OOO", "$OOO"]
                },
                {
                  $eq: ["$UUU", "$UUU"]
                }
              ]
            }
          }
        }
      ],
      as: "HA"
    }
  },
  {
    $match: {
      "HA": { $exists : true, $not : { $size: 0 } }
    }
  },
  {
    $unwind: {
      path: "$HA"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$ROOT",
          {
            QQQ: "$HG.QQQ",
            OOO: "$HG.OOO",
            UUU: "$HG.UUU",
            FFF: "$HA.FFF",
            UUU: "$HA.UUU",
            OOO: "$HA.OOO"
          }
        ]
      }
    }
  },
  {
    $project: {
      XXX: 1,
      AAA: 1,
      BBB: 1,
      CCC: 1,
      FFF: 1,
      HHH: 1,
      EEE: 1,
      QQQ: 1,
      UUU: 1,
      OOO: 1
    }
  },
  {
    $out: "temp_col"
  }
]

Some can say me what’s wrong with my query ?
Many thanks
[/quote]

Hello, @Espinoza_Jean

To optimize your MongoDB aggregation, consider the following:

Index Fields: Index fields used in $match and $lookup.
Early $match: Use $match early to filter data.
Project Necessary Fields: Only include essential fields in $project.
Restructure Data: Avoid $lookup if it slows down the query.
Combine Stages: Merge adjacent stages for efficiency.
Use $unwind Sparingly: Apply $unwind after reducing documents.
Examine Execution Plan: Use explain to identify slow stages.
Upgrade Hardware: If hardware is a limitation, consider upgrading.
Shard Collection: For large datasets, consider sharding.
Optimize Schema: Adjust schema for better query performance.
Test changes to ensure they improve your pipeline’s efficiency.

I hope this step is helpful for you.

Best Regard,
Diana Hill

3

@Diana_Hill, congratulations for your second GAIP. I did not see your first as I do NOT read out of town MUG’s post. This time you were able to include SPAM. Now I will have to follow you to catch your next attempt faster.

1 Like
4

Thank Diana for your answer :slight_smile: