/ Check-in [6f99b54a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional performance improvements in sqlite3BtreeNext() and sqlite3BtreePrevious().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | toTypeFuncs
Files: files | file ages | folders
SHA1: 6f99b54aedeb91e46d52f65504d02a9cc61c0062
User & Date: drh 2013-08-19 22:22:41
References
2013-08-20
03:13
Performance optimizations in the VDBE and especially to the OP_Next and related opcodes and in the sqlite3BtreeNext() and sqlite3BtreePrevious() routines. This is a cherrypick of [6f99b54aedeb], [d2efea1682a7], and [d78c5d89de4b]. check-in: 7f72fc4f user: drh tags: trunk
Context
2013-08-20
03:13
Performance optimizations in the VDBE and especially to the OP_Next and related opcodes and in the sqlite3BtreeNext() and sqlite3BtreePrevious() routines. This is a cherrypick of [6f99b54aedeb], [d2efea1682a7], and [d78c5d89de4b]. check-in: 7f72fc4f user: drh tags: trunk
2013-08-19
23:18
Performance improvement to SQL function calls in the VDBE. check-in: d2efea16 user: drh tags: toTypeFuncs
22:22
Additional performance improvements in sqlite3BtreeNext() and sqlite3BtreePrevious(). check-in: 6f99b54a user: drh tags: toTypeFuncs
21:15
Add tointeger() and toreal() SQL functions. check-in: af497072 user: mistachkin tags: toTypeFuncs
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

   720    720     }
   721    721     pCur->eState = CURSOR_INVALID;
   722    722     rc = btreeMoveto(pCur, pCur->pKey, pCur->nKey, 0, &pCur->skipNext);
   723    723     if( rc==SQLITE_OK ){
   724    724       sqlite3_free(pCur->pKey);
   725    725       pCur->pKey = 0;
   726    726       assert( pCur->eState==CURSOR_VALID || pCur->eState==CURSOR_INVALID );
          727  +    if( pCur->skipNext && pCur->eState==CURSOR_VALID ){
          728  +      pCur->eState = CURSOR_SKIPNEXT;
          729  +    }
   727    730     }
   728    731     return rc;
   729    732   }
   730    733   
   731    734   #define restoreCursorPosition(p) \
   732    735     (p->eState>=CURSOR_REQUIRESEEK ? \
   733    736            btreeRestoreCursorPosition(p) : \
................................................................................
   745    748     int rc;
   746    749   
   747    750     rc = restoreCursorPosition(pCur);
   748    751     if( rc ){
   749    752       *pHasMoved = 1;
   750    753       return rc;
   751    754     }
   752         -  if( pCur->eState!=CURSOR_VALID || pCur->skipNext!=0 ){
          755  +  if( pCur->eState!=CURSOR_VALID || NEVER(pCur->skipNext!=0) ){
   753    756       *pHasMoved = 1;
   754    757     }else{
   755    758       *pHasMoved = 0;
   756    759     }
   757    760     return SQLITE_OK;
   758    761   }
   759    762   
................................................................................
  4793   4796   int sqlite3BtreeNext(BtCursor *pCur, int *pRes){
  4794   4797     int rc;
  4795   4798     int idx;
  4796   4799     MemPage *pPage;
  4797   4800   
  4798   4801     assert( cursorHoldsMutex(pCur) );
  4799   4802     assert( pRes!=0 );
         4803  +  assert( pCur->skipNext==0 || pCur->eState!=CURSOR_VALID );
  4800   4804     if( pCur->eState!=CURSOR_VALID ){
  4801   4805       rc = restoreCursorPosition(pCur);
  4802   4806       if( rc!=SQLITE_OK ){
  4803   4807         return rc;
  4804   4808       }
  4805   4809       if( CURSOR_INVALID==pCur->eState ){
  4806   4810         *pRes = 1;
  4807   4811         return SQLITE_OK;
  4808   4812       }
  4809         -  }
  4810         -  if( pCur->skipNext ){
  4811         -    if( pCur->skipNext>0 ){
         4813  +    if( pCur->skipNext ){
         4814  +      assert( pCur->eState==CURSOR_VALID || pCur->eState==CURSOR_SKIPNEXT );
         4815  +      pCur->eState = CURSOR_VALID;
         4816  +      if( pCur->skipNext>0 ){
         4817  +        pCur->skipNext = 0;
         4818  +        *pRes = 0;
         4819  +        return SQLITE_OK;
         4820  +      }
  4812   4821         pCur->skipNext = 0;
  4813         -      *pRes = 0;
  4814         -      return SQLITE_OK;
  4815   4822       }
  4816         -    pCur->skipNext = 0;
  4817   4823     }
  4818   4824   
  4819   4825     pPage = pCur->apPage[pCur->iPage];
  4820   4826     idx = ++pCur->aiIdx[pCur->iPage];
  4821   4827     assert( pPage->isInit );
  4822   4828   
  4823   4829     /* If the database file is corrupt, it is possible for the value of idx 
................................................................................
  4870   4876   ** this routine was called, then set *pRes=1.
  4871   4877   */
  4872   4878   int sqlite3BtreePrevious(BtCursor *pCur, int *pRes){
  4873   4879     int rc;
  4874   4880     MemPage *pPage;
  4875   4881   
  4876   4882     assert( cursorHoldsMutex(pCur) );
         4883  +  assert( pRes!=0 );
         4884  +  assert( pCur->skipNext==0 || pCur->eState!=CURSOR_VALID );
  4877   4885     pCur->atLast = 0;
  4878   4886     if( pCur->eState!=CURSOR_VALID ){
  4879   4887       if( ALWAYS(pCur->eState>=CURSOR_REQUIRESEEK) ){
  4880   4888         rc = btreeRestoreCursorPosition(pCur);
  4881   4889         if( rc!=SQLITE_OK ) return rc;
  4882   4890       }
  4883   4891       if( CURSOR_INVALID==pCur->eState ){
  4884   4892         *pRes = 1;
  4885   4893         return SQLITE_OK;
  4886   4894       }
  4887         -  }
  4888         -  if( pCur->skipNext ){
  4889         -    if( pCur->skipNext<0 ){
         4895  +    if( pCur->skipNext ){
         4896  +      assert( pCur->eState==CURSOR_VALID || pCur->eState==CURSOR_SKIPNEXT );
         4897  +      pCur->eState = CURSOR_VALID;
         4898  +      if( pCur->skipNext<0 ){
         4899  +        pCur->skipNext = 0;
         4900  +        *pRes = 0;
         4901  +        return SQLITE_OK;
         4902  +      }
  4890   4903         pCur->skipNext = 0;
  4891         -      *pRes = 0;
  4892         -      return SQLITE_OK;
  4893   4904       }
  4894         -    pCur->skipNext = 0;
  4895   4905     }
  4896   4906   
  4897   4907     pPage = pCur->apPage[pCur->iPage];
  4898   4908     assert( pPage->isInit );
  4899   4909     if( !pPage->leaf ){
  4900   4910       int idx = pCur->aiIdx[pCur->iPage];
  4901   4911       rc = moveToChild(pCur, get4byte(findCell(pPage, idx)));

Changes to src/btreeInt.h.

   516    516     u16 aiIdx[BTCURSOR_MAX_DEPTH];        /* Current index in apPage[i] */
   517    517     MemPage *apPage[BTCURSOR_MAX_DEPTH];  /* Pages from root to current page */
   518    518   };
   519    519   
   520    520   /*
   521    521   ** Potential values for BtCursor.eState.
   522    522   **
   523         -** CURSOR_VALID:
   524         -**   Cursor points to a valid entry. getPayload() etc. may be called.
   525         -**
   526    523   ** CURSOR_INVALID:
   527    524   **   Cursor does not point to a valid entry. This can happen (for example) 
   528    525   **   because the table is empty or because BtreeCursorFirst() has not been
   529    526   **   called.
   530    527   **
          528  +** CURSOR_VALID:
          529  +**   Cursor points to a valid entry. getPayload() etc. may be called.
          530  +**
          531  +** CURSOR_SKIPNEXT:
          532  +**   Cursor is valid except that the Cursor.skipNext field is non-zero
          533  +**   indicating that the next sqlite3BtreeNext() or sqlite3BtreePrevious()
          534  +**   operation should be a no-op.
          535  +**
   531    536   ** CURSOR_REQUIRESEEK:
   532    537   **   The table that this cursor was opened on still exists, but has been 
   533    538   **   modified since the cursor was last used. The cursor position is saved
   534    539   **   in variables BtCursor.pKey and BtCursor.nKey. When a cursor is in 
   535    540   **   this state, restoreCursorPosition() can be called to attempt to
   536    541   **   seek the cursor to the saved position.
   537    542   **
................................................................................
   540    545   **   on a different connection that shares the BtShared cache with this
   541    546   **   cursor.  The error has left the cache in an inconsistent state.
   542    547   **   Do nothing else with this cursor.  Any attempt to use the cursor
   543    548   **   should return the error code stored in BtCursor.skip
   544    549   */
   545    550   #define CURSOR_INVALID           0
   546    551   #define CURSOR_VALID             1
   547         -#define CURSOR_REQUIRESEEK       2
   548         -#define CURSOR_FAULT             3
          552  +#define CURSOR_SKIPNEXT          2
          553  +#define CURSOR_REQUIRESEEK       3
          554  +#define CURSOR_FAULT             4
   549    555   
   550    556   /* 
   551    557   ** The database page the PENDING_BYTE occupies. This page is never used.
   552    558   */
   553    559   # define PENDING_BYTE_PAGE(pBt) PAGER_MJ_PGNO(pBt)
   554    560   
   555    561   /*