1

I'm trying to figure out how to do pagination on a large collection with MongoDB using the C# driver. I tried using skip and limit. It works, but gets slower the more documents I'm skipping, which is an expected result after searching Google about this.

The C# driver also supports cursors, but I could not find anything on how to use cursors inside a REST-API to serve the frontend with data.

Is it possible to have a REST-API use cursors for pagination so that if I click "Next page" I get the next X results?

Edit:

As @ĐĵΝιΓΞΗΛψΚ mentioned, I may be missing indexes. So here is what the query looks like:

db.collection.find({
    "start": {
         "$gte": ISODate("2018-03-31T22:00:00Z"),
         "$lte": ISODate("2018-04-30T22:00:00Z")
     },
    "mat": { "$in": ["XYZ"] },
    "serial": { "$in": ["72398472"] },
    "code": { "$in": ["5345"] },
    "status": { "$in": ["OK"] }
})
.limit(500)
.explain("executionStats")

This is very fast because it used the index {serial: 1} and only returns 21 results. But start, mat, serial, code and status are optional, so it could also look like this:

db.collection.find({
    "start": {
         "$gte": ISODate("2018-03-31T22:00:00Z"),
         "$lte": ISODate("2018-04-30T22:00:00Z")
     },
    "mat": { "$in": ["XYZ"] },
    "status": { "$in": ["OK"] }
})
.limit(500)
.explain("executionStats")

I was trying to figure out indexes to make these queries faster, so I currently have the following:

  • { _id_: 1}
  • {serial: 1}
  • {mat: 1}
  • {start: 1}
  • {code: 1}
  • {status: 1}
  • {start: 1, mat: 1, serial: 1, code: 1, status: 1}
  • {start: 1, mat: 1} (which should be covered by the before, so I think I could remove it)
  • {start: 1, code: 1}
  • {start: 1, serial: 1}

Also, I tested the following query:

db.collection.find({
    "start" : {
        "$gte" : ISODate("2018-05-01T00:00:00.000Z"),
        "$lte" : ISODate("2018-05-31T00:00:00.000Z")
    },
    "mat": { "$in" : ["XYZ"] } 
})

which took 757549 ms because it used the index {mat: 1} instead of {start: 1, mat: 1} (5725ms).

Edit 2: Heres the output of .explain("executionStats"):

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "db.runs",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "mat" : {
                        "$eq" : "1101024071"
                    }
                },
                {
                    "start" : {
                        "$lte" : ISODate("2018-05-01T00:00:00.000+02:00")
                    }
                },
                {
                    "start" : {
                        "$gte" : ISODate("2018-04-01T00:00:00.000+02:00")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 500,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "start" : {
                                "$lte" : ISODate("2018-05-01T00:00:00.000+02:00")
                            }
                        },
                        {
                            "start" : {
                                "$gte" : ISODate("2018-04-01T00:00:00.000+02:00")
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "mat" : 1
                    },
                    "indexName" : "mat",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "mat" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "mat" : [
                            "[\"1101024071\", \"1101024071\"]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "mat" : {
                            "$eq" : "1101024071"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1
                        },
                        "indexName" : "start",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "mat" : {
                            "$eq" : "1101024071"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "end" : 1
                        },
                        "indexName" : "start_end",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "end" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "end" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "mat" : 1
                        },
                        "indexName" : "start_1_mat_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "mat" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "mat" : [
                                "[\"1101024071\", \"1101024071\"]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "end" : 1,
                            "mat" : 1,
                            "serial" : 1,
                            "shipping_code" : 1,
                            "status" : 1
                        },
                        "indexName" : "start_end_mat_serial_code_status",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "end" : [ ],
                            "mat" : [ ],
                            "serial" : [ ],
                            "shipping_code" : [ ],
                            "status" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "end" : [
                                "[MinKey, MaxKey]"
                            ],
                            "mat" : [
                                "[\"1101024071\", \"1101024071\"]"
                            ],
                            "serial" : [
                                "[MinKey, MaxKey]"
                            ],
                            "shipping_code" : [
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "mat" : {
                            "$eq" : "1101024071"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "shipping_code" : 1
                        },
                        "indexName" : "start_1_code_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "shipping_code" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "shipping_code" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "mat" : 1,
                            "serial" : 1,
                            "shipping_code" : 1,
                            "status" : 1
                        },
                        "indexName" : "start_mat_serial_code_status",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "mat" : [ ],
                            "serial" : [ ],
                            "shipping_code" : [ ],
                            "status" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "mat" : [
                                "[\"1101024071\", \"1101024071\"]"
                            ],
                            "serial" : [
                                "[MinKey, MaxKey]"
                            ],
                            "shipping_code" : [
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "LIMIT",
                "limitAmount" : 500,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "mat" : {
                            "$eq" : "1101024071"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "start" : 1,
                            "serial" : 1,
                            "status" : 1
                        },
                        "indexName" : "start_serial",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "start" : [ ],
                            "serial" : [ ],
                            "status" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "start" : [
                                "[new Date(1522533600000), new Date(1525125600000)]"
                            ],
                            "serial" : [
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 500,
        "executionTimeMillis" : 633514,
        "totalKeysExamined" : 104446,
        "totalDocsExamined" : 104446,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 500,
            "executionTimeMillisEstimate" : 181937,
            "works" : 104447,
            "advanced" : 500,
            "needTime" : 103946,
            "needYield" : 0,
            "saveState" : 34932,
            "restoreState" : 34932,
            "isEOF" : 1,
            "limitAmount" : 500,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "start" : {
                                "$lte" : ISODate("2018-05-01T00:00:00.000+02:00")
                            }
                        },
                        {
                            "start" : {
                                "$gte" : ISODate("2018-04-01T00:00:00.000+02:00")
                            }
                        }
                    ]
                },
                "nReturned" : 500,
                "executionTimeMillisEstimate" : 181914,
                "works" : 104446,
                "advanced" : 500,
                "needTime" : 103946,
                "needYield" : 0,
                "saveState" : 34932,
                "restoreState" : 34932,
                "isEOF" : 0,
                "docsExamined" : 104446,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 104446,
                    "executionTimeMillisEstimate" : 315,
                    "works" : 104446,
                    "advanced" : 104446,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 34932,
                    "restoreState" : 34932,
                    "isEOF" : 0,
                    "keyPattern" : {
                        "mat" : 1
                    },
                    "indexName" : "mat",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "mat" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "mat" : [
                            "[\"1101024071\", \"1101024071\"]"
                        ]
                    },
                    "keysExamined" : 104446,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "indexDef" : {
                        "indexName" : "mat",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "mat" : [ ]
                        },
                        "keyPattern" : {
                            "mat" : 1
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "direction" : "forward"
                    }
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "localhost",
        "port" : 27017,
        "version" : "4.2.13",
        "gitVersion" : "82dd40f60c55dae12426c08fd7150d79a0e28e23"
    },
    "ok" : 1,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1620838429, 122),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : 0
        }
    },
    "operationTime" : Timestamp(1620838429, 122)
}
cb420
  • 51
  • 4
  • 1
    By definition: no. REST ist stateless. A cursor position would _be_ state. But there are workarounds. For example if you have Ids you could do something like "Give me 10 docs starting after Id XYZ". Which also avoids the skip/limit drawbacks. – Fildor May 11 '21 at 13:47
  • 1
    "It works, but gets slower the more documents I'm skipping". that sounds like a case of missing indexes. – Dĵ ΝιΓΞΗΛψΚ May 12 '21 at 11:31
  • Thank you both for your comments. @ĐĵΝιΓΞΗΛψΚ I updated my question with more information about the queries and indexes. – cb420 May 12 '21 at 13:20
  • 1
    @cb420 does `.explain("executionStats")` show IXSCAN or COLSCAN/FETCH when it takes a long time? if it needs to FETCH for 500 items, that may be the issue. [this article](https://www.mongodb.com/blog/post/performance-best-practices-indexing) maye help with the indexing. i try to follow the ESR principle, but sometimes mongo decides to not use indexes. for ex: the range queries with $lte/$gte. – Dĵ ΝιΓΞΗΛψΚ May 12 '21 at 14:52
  • @ĐĵΝιΓΞΗΛψΚ I added the output of `.explain("executionStats")` to my question. It shows that it used the `{mat: 1}` index, but FETCH took most of the time. Regarding ESR principle, does using `$in` count as an equality? So maybe I should make an index like `{ mat: 1, serial: 1, code: 1, status: 1, start: 1}` with `start` being last? – cb420 May 12 '21 at 17:05
  • 1
    @cb420 index tuning is tricky business. give [this answer](https://stackoverflow.com/a/46897198/4368485) a good read too. and yes afaik, $in should be considered equality. so a compound index like `{ mat: 1, serial: 1, code: 1, status: 1, start: 1}` should work, theoretically. if all else fails, give [index hinting](https://docs.mongodb.com/manual/tutorial/measure-index-use/#control-index-use-with-hint--) a shot. – Dĵ ΝιΓΞΗΛψΚ May 13 '21 at 05:30

0 Answers0