Compare the page-based comparison between the skip-limit and where-limit of MongoDB Based on C # And the source code containing the MongoDB help class

Recently, the MongoDB nosql database was used in the log service design (I don't know the MongoDB point) because it is used for pure log storage, and the log volume is huge, with millions of logs, so we need to use its paging query.

However, LZ just came into contact with this database. Instead of knowing the command syntax, I checked some information on the Internet, the results show that the paging efficiency of the simple and convenient skip method provided by MongoDB is very low, so you have to use other methods,

MultipleArticleWe recommend that you use the where + Limit Method for paging, which means that the efficiency is much higher than that of the Skip method. However, a lot of information is about some ideas, but it is not very specific, but it is also very helpful, now let's simply talk about this paging idea (the Skip method is so simple that we will not talk about it ):

Assume that a table (MongoDB is replaced by a set) has the following data records, 60 (only indicates the ID of the record, which can be understood as the primary key)

The current size is also pagesize = 4, so

• The data on the first page is, which is interpreted as the SQL statement select Top 4 * from table where ID> 0 because the previous page has no records, so it is replaced by 0.
• The data on the second page is 6, 7, 8, 9, 20. The WHERE clause is interpreted as the SQL statement select Top 4 * from table where ID> 5 here 5 is the last record on the previous page.
• The data on the second page is, 50, and 52. The WHERE clause is interpreted as the SQL statement select Top 4 * from table where ID> 20. Here 20 is the last record on the second page.

This is simple. In the future, you need to upload an ID number when querying by page. The idea in MongoDB is also like this, but the difference is that C # using MongoDB requires other drivers to query data, I can't use the SQL statement. I just want to paste it.Code

View code

 ///   <Summary>          ///  Pagesize Mode  ///  </Summary>          ///   <Typeparam name = "T">  Object Type of the data to be queried  </Typeparam>          ///   <Param name = "query">  The query condition cannot be null.  </Param>          ///   <Param name = "indexname">  Index name  </Param>          ///   <Param name = "lastkeyvalue"> Last index value  </Param>          ///   <Param name = "pagesize">  Paging size  </Param>          ///   <Param name = "sorttype">  Sort type 1 ascending-1 descending only for this index  </Param>          ///   <Param name = "collectionname">  Name of the specified set  </Param>          ///  <Returns>  Returns a list of data.  </Returns>          Public List <t> Find <t> (imongoquery query, String Indexname, Object Lastkeyvalue, Int Pagesize, Int Sorttype, String  Collectionname) {collect collection <T> MC = This . _ DB. getcollection <t>(Collectionname); Revoke cursor <T> export cursor = Null  ; Query = This  . Initquery (query );  //  Query after determining the ascending or descending order              If (Sorttype> 0  ){  //  Ascending                  If (Lastkeyvalue! = Null ){  //  Conditions for adding the value of the previous primary key only when the value of the previous primary key is passed in Query = Query. And (query, query. gt (indexname, bsonvalue. Create (lastkeyvalue )));}  //  Query by condition, sort by condition, and then obtain the number. Export cursor = mc. Find (query). setsortorder ( New Sortbydocument (indexname, 1  ). Setlimit (pagesize );}  Else  { //  Descending Order                  If (Lastkeyvalue! = Null  ) {Query = Query. And (query, query. LT (indexname, bsonvalue. Create (lastkeyvalue);} explain cursor = Mc. Find (query). setsortorder ( New Sortbydocument (indexname ,- 1  ). Setlimit (pagesize );}  Return Export cursor. tolist <t> ();} 

Of course, this code snippet is not very good-looking. It is estimated that readers are not very clear about it. Rest assured that the following will be accompanied by source code downloads (people who hate that half of the Code do not know what to say)

Since they all say skip efficiency is poor, let's test it by yourself,

I first added million simple data records to MongoDB, and the test was effective only when the data volume was large,

Let's take a look at the console code for the test. It's very convenient to encapsulate all the code. It's easy to undo it if you're lazy.

View code

 Class  Program {  Static  Mongodbhelper dB;  Static   Void Main ( String [] ARGs ){  //  Create a MongoDB database instance DB = New  Mongodbhelper ();  # Region Initialize million data records //  Initdata ();              # Endregion  Console. writeline (  "  Test the efficiency of your own skip-limit paging and custom where-limit paging in MongoDB (in milliseconds ):  "  ); //  I will not write any comments for testing various page sizes. Pagertest ( 1 , 100 ); //  This test is ignored. It is estimated that after the first query, the corresponding cached data will cause the query to be very fast. Pagertest ( 3 , 100  ); Pagertest (  30 , 100  ); Pagertest (  300 , 100 ); Pagertest (  300 , 1000  ); Pagertest (  3000 , 100  ); Pagertest (  30000 , 100  ); Pagertest (  300000 , 100  ); Console. readkey ();}  ///  <Summary>          ///  Paging Test  ///   </Summary>          ///   <Param name = "pageindex">  Page number  </Param>          ///   <Param name = "pagesize">  Page size  </Param>          Static   Void Pagertest (Int Pageindex, Int  Pagesize ){  //  The paging query condition is null (the encapsulation is converted to the constant truth condition). The sorting condition is null (converted to objectid increments). The page size is also set.  Console. writeline (  "  Page number {0}, page size {1}  "  , Pageindex, pagesize); stopwatch SW1 = New  Stopwatch (); sw1.start (); List <Loginfo> list1 = dB. Find <loginfo> (Null , Pageindex, pagesize, Null  ); Sw1.stop (); console. writeline (  "  Time consumed by Using skip-limit: {0}  "  , Sw1.elapsedmilliseconds); stopwatch sw2 = New  Stopwatch (); sw2.start ();  //  Here, the logid index is used as a flag. If the collection does not contain these primary key flags, you can use your own objectid for the help class. It is also encapsulated.  // The logid calculated based on the page number is just a simple simulation. In reality, these logids may not be consecutive. In this way, paging is generally not a page number, but the value of the last flag is passed. List <loginfo> list2 = dB. Find <loginfo> ( Null , "  Logid  " , (Pageindex- 1 ) * Pagesize, pagesize, 1  ); Sw2.stop (); console. writeline (  "  Where-limit paging time: {0} \ r \ n  "  , Sw2.elapsedmilliseconds );} ///   <Summary>          ///  Initialize data  ///   </Summary>          Static   Void  Initdata (){  //  Configuration for creating index of test log classes in the loginfo class features DB. createindex <loginfo> ();  //  Set of initialization logs List <loginfo> List =New List <loginfo> ();  Int Temp = 0  ;  //  Insert million pieces of test data              For ( Int I = 1 ; I <= 10000000 ; I ++ ) {List. Add (  New Loginfo {logid = I, content = "  Content  " + I. tostring (), createtime = Datetime. Now });  //  Count temp and make a judgment larger than 100                  If (++ Temp> = 100  ){ //  If the value is greater than or equal to 100, it is cleared. Temp = 0  ;  //  Batch insert data using encapsulated Methods DB. Insert <loginfo> (List );  //  After data is inserted, the current data is cleared.  List. Clear ();}}}} 

Let's take a look at the final efficiency test diagram:

Very, very. Obviously, we can see how low the paging efficiency of the skip-limit is. The speed is reduced by ten times whenever the page number increases by ten times, it takes 30 seconds to query a 30 W page, but it cannot be processed in the case of large data volumes. However, no matter how many pages you have, the speed of where-limit is still so fast, the last 0 s is rounded to 0. You can see more than one.

Continuous testing is the case of these situations. I don't want to look at tables or graphs. (the first test data can be ignored. It is estimated that the first query will be slower and will be cached later)

Of course, the where-limit method is fast, but it is still a bit difficult to do, not to pass the page number, but to pass the mark of the previous page, in addition, not all collections have their own primary keys. If not, you can use the objectid provided by MongoDB. It is the default index and the speed is fast.

It is recommended that you use skip if there is a small amount of data of tens of thousands or tens of thousands. After all, it is convenient. If there is a large amount of data, do not use it. It is dangerous !!!!

I can view the source code and run it directly. There is also the MongoDB query help class I wrote myself.

References:

MongoDB does not use skip for paging

Performance problems when using MongoDB for paging/ranking queries

Paging display in MongoDB and Its Inspiration