Why $let is so slow?

Working with Data
,
1

Hello

I did a small benchmark with 8 $let made the query 4x slower.

Seems that every nested $let, we add makes the query 50% slower, for example i tested
also with 2 nested $let and query took double time, which is very big difference.

[0 ... 1000] array
map 1 function, that adds 7 times the number 1
[7 8 .... 1007]

The first time i used
(+ .. (+ (+ n 1) 1)) 7x
The second time i used
n1=n+ 1, n2=n1+1, n3=n2+1 7x in a nested $let

{
  "$let": {"vars": {"n1": {"$add": ["$$n",1]}},
    "in": { "$let": {"vars": {"n2": {"$add": [ "$$n1",1]}} ... 7x

Times were

728msec  $let
180msec  $add

Query became 4x slower.

I tried exactly the same in a programming language and i got zero difference, nested let didn’t cost anything.

Why $let in MQL costs so much, and is there a way to ask for it to be fixed?
Using variables with $let is very important, escpecially in data processing.

Thank you

2

Can you paste the exact aggregation you used the first time? I understand the second test, but not quite the first one, where you say

(+ .. (+ (+ n 1) 1)) 7x

Although I just ran a test with your second pipeline and 8 levels of $let and I cannot get things to be slower than 1ms so clearly something is different in our tests. Could you provide both complete pipelines and describe the data that is in your test collection?

3

Data(10.000 documents like the bellow)

{ "myarray"  [0 1 2 ... 999]}        //array with 1000 members

Query1 ($add)

[ {
    "$addFields" : {
      "myarray" : {
        "$map" : {
          "input" : "$myarray",
          "as" : "n",
          "in" : {
            "$add" : [ {
              "$add" : [ {
                "$add" : [ {
                  "$add" : [ {
                    "$add" : [ {
                      "$add" : [ {
                        "$add" : [ {
                          "$add" : [ "$$n", 1 ]
                        }, 1 ]
                      }, 1 ]
                    }, 1 ]
                  }, 1 ]
                }, 1 ]
              }, 1 ]
            }, 1 ]
          }
        }
      }
    }
  } ]

Query2 ($let)

[ {
    "$addFields" : {
      "myarray" : {
        "$map" : {
          "input" : "$myarray",
          "as" : "n",
          "in" : {
            "$let" : {
              "vars" : {
                "n1" : {
                  "$add" : [ "$$n", 1 ]
                }
              },
              "in" : {
                "$let" : {
                  "vars" : {
                    "n2" : {
                      "$add" : [ "$$n1", 1 ]
                    }
                  },
                  "in" : {
                    "$let" : {
                      "vars" : {
                        "n3" : {
                          "$add" : [ "$$n2", 1 ]
                        }
                      },
                      "in" : {
                        "$let" : {
                          "vars" : {
                            "n4" : {
                              "$add" : [ "$$n3", 1 ]
                            }
                          },
                          "in" : {
                            "$let" : {
                              "vars" : {
                                "n5" : {
                                  "$add" : [ "$$n4", 1 ]
                                }
                              },
                              "in" : {
                                "$let" : {
                                  "vars" : {
                                    "n6" : {
                                      "$add" : [ "$$n5", 1 ]
                                    }
                                  },
                                  "in" : {
                                    "$let" : {
                                      "vars" : {
                                        "n7" : {
                                          "$add" : [ "$$n6", 1 ]
                                        }
                                      },
                                      "in" : "$$n7"
                                    }
                                  }
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  } ]

Results

"Elapsed time: 1884.386048 msecs"    $add
"Elapsed time: 8507.002225 msecs"    $let (4x)

Thank you

1 Like
4

Ok, I can reproduce the slowdown, but it doesn’t have anything (or at least much) to do with embedded $let fields. You can see similar slowdown using a single $let with 10 vs 1 variable.

I suspect the slowdown is because for each variable we have to allocate some memory and we probably don’t consolidate that work. Can you confirm that you see the same thing when having a single $let with many variables?

Asya

1 Like
5

Yes it doesn’t have to do with the nested $let i tried 1 $let with 7 variables it was equaly slow.Its the number of variables i guess.

"Elapsed time: 1631.026483 msecs"     $add
"Elapsed time: 7485.867392 msecs"      1 $let 7 variables
"Elapsed time: 7143.430496 msecs"     7 nested $let 1 variable each

I hope its fixable, thank you for your reply.

1 Like