Solve the problem of slow MongoDB query

Recently the project has been using MongoDB as a database, MongoDB has his advantage, the document type JSON format to store data, modified than the traditional relational database more convenient, but recently in MongoDB with the query slow problem, I use the command line query, display speed very fast, And the index was added, 20,000 data only takes more than 10 milliseconds, but it takes a few seconds to implement the code, and I debugged the Code Discovery Code generation query statement as I did on the command line, and I was wondering.

My code was written like this:

var list = collection.FindAs<AdClick>(query).SetSortOrder(s).toList();

This is a very normal line of code, according to the query criteria, sorted back to the list collection, when I remove ToList, the speed of the second check, and then I see the type of return is mongocursor. Here I would like to introduce Mongocursor, he is the MONGO cursor, he actually did not really query to the results, equivalent to lazy loading,

When you call find, the MongoDB shell does not immediately query the database, but rather waits for the query to be sent when the data is actually started. (similar to IQueryable in LINQ), you can control the final result by cursors. For example, limit the number of results, skip a certain part, according to any key in any order of the combination of the results of various sorts and so on. At that time when you call ToList (), he will be all the query data loaded into memory, if the query more data, the process that will be very slow, so the real slow reason, is tolist (), know that the reason for the slow change is very good change.


But modified this and found another problem, that is, MongoDB plus query condition count () will also be very slow, because do statistics need to get total, directly without the query condition directly call count () than add the query condition call count () faster, Because MongoDB query is based on the index, if you query the more criteria, there is no hit query criteria index, will be full-text search, so it will be very slow, so use the count () function, try not to add query conditions, but obviously unrealistic, because the query conditions will inevitably be many. Later on the Internet to see a solution is to use the Mongocursor.size () method, sure enough speed a lot, do not know the reason, no careful study.


There is also a problem, that is, MONGO paging problem, you will find that start a few pages will be very fast, the more to the back, the slower the paging, this is MONGO will load the query results into memory, due to memory constraints, the more slowly to the back, what is the solution?


Db.test.sort ({"Amount": 1}). Skip (100000). Limit (Ten)//183ms
Db.test.find ({amount:{$gt: 2399927}}). Sort ({"Amount": 1}). Limit (Ten)//53ms
According to the query conditions load paging, only query 10 of data loaded into memory, skip paging seems to affect efficiency, do not easily use skip to do queries, otherwise the large amount of data will lead to a sharp decline in performance, this is because skip is a number of one, more natural slow.

Solve the problem of slow MongoDB query