1

We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse. The record count is about ~4m for PurchaseOrder and each purchase order has about ~0-150 lines inside. The TSQL query would be something like this:

SELECT "PurchaseOrderLines.ProductID",
       SUM(SUM("$PurchaseOrderLines.ReceivingQty") - SUM("$PurchaseOrderLines.SendingQty")) TotalQty,
       "WarehouseTypeID"
FROM PurchaseOrder
WHERE (
          "Status" = 4
          OR "TransactionID" IN ( 16, 116 )
      )
      AND "PurchaseOrderLines.LedgerPostingStatus" IS NULL
      AND "TransactionID" NOT IN ( 18, 181 )
      AND "NowDate" >= 'from_date'
      AND "NowDate" <= 'to_date'
GROUP BY "WarehouseID",
         "PurchaseOrderLines.ProductID",
         "WarehouseTypeID";

and the fluent:

db.PurchaseOrder.aggregate(
  [{
      "$match": {
        "$and": [{
            "$and": [{
                "$and": [{
                    "$and": [{
                        "$or": [{
                            "Status": 4
                          },
                          {
                            "TransactionID": {
                              "$in": [
                                16,
                                116
                              ]
                            }
                          }
                        ]
                      },
                      {
                        "PurchaseOrderLines.LedgerPostingStatus": null
                      }
                    ]
                  },
                  {
                    "TransactionID": {
                      "$nin": [
                        18,
                        181
                      ]
                    }
                  }
                ]
              },
              {
                "NowDate": {
                  "$gte": "from_date"
                }
              }
            ]
          },
          {
            "NowDate": {
              "$lte": "to_date"
            }
          }
        ]
      }
    },
    { $unwind:{ path: "$PurchaseOrderLines", preserveNullAndEmptyArrays: true  }},
    {
      "$group": {
        "_id": {
          "WarehouseID": "$WarehouseID",
          "PurchaseOrderLines_ProductID": "$PurchaseOrderLines.ProductID",
          "WarehouseTypeID": "$WarehouseTypeID"
        },
        "TotalReceivingQty":{"$sum":"$PurchaseOrderLines.ReceivingQty"},
        "TotalSendingQty":{"$sum":"$PurchaseOrderLines.SendingQty"}
      }
    },
    {
        "$addFields":{
             "TotalQty": { "$subtract": ["$TotalReceivingQty", "$TotalSendingQty"] }
         }
    },
    {
      "$project": {
        "PurchaseOrderLines_ProductID": "$_id.PurchaseOrderLines_ProductID",
        "TotalQty": "$TotalQty",
        "WarehouseTypeID": "$_id.WarehouseTypeID"
      }
    }
  ],{ allowDiskUse: true })

executionStats

We have tried many indexes such as combined indexes

{PurchaseOrderLines.ProductID , PurchaseOrderLines.ReceivingQty , PurchaseOrderLines.SendingQty}

{"PurchaseOrderLines.ProductID": 1,"PurchaseOrderLines.ReceivingQty":1,"PurchaseOrderLines.SendingQty":1,"WarehouseTypeID":1,"WarehouseID":1}

and rebuilding the indexes but no luck.

It takes about ~4min for one month range, which is slow.

The hardware in use: 32G RAM and 4 core CPU running on windows server 2016 (virtual machine).

In addition we are planning to use spark and training the model, but for now we have to decrease the execution time.

So we need performance on this query, any suggestion? appreciate your help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.