/ Check-in [56bc5ce8]
Login

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

Overview
Comment:Remove the "rowid cache" that sought to remember the largest rowid for a table and thereby speed up OP_NewRowid. That cache was ineffective. Removing it results in a performance increase of 0.4%, less memory usage, and a slightly smaller library size.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 56bc5ce8958c8e6250531b4052b905d7ac993db3
User & Date: drh 2014-02-12 21:31:12
References
2015-09-30
12:10 New ticket [1b266395] INSERT OR REPLACE with a foreign key constraint leads to assertion fault. artifact: 7f88fd96 user: drh
Context
2014-02-12
23:52
Minor simplifications to the "zeroPage()" routine in btree.c. check-in: cc8f10bc user: drh tags: trunk
21:31
Remove the "rowid cache" that sought to remember the largest rowid for a table and thereby speed up OP_NewRowid. That cache was ineffective. Removing it results in a performance increase of 0.4%, less memory usage, and a slightly smaller library size. check-in: 56bc5ce8 user: drh tags: trunk
14:43
Make sure "rowid" columns are correctly resolved in joins between normal tables and WITHOUT ROWID tables. Fix for ticket [c34d0557f740c45070]. check-in: 5d01426d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  3628   3628     pCur->wrFlag = (u8)wrFlag;
  3629   3629     pCur->pNext = pBt->pCursor;
  3630   3630     if( pCur->pNext ){
  3631   3631       pCur->pNext->pPrev = pCur;
  3632   3632     }
  3633   3633     pBt->pCursor = pCur;
  3634   3634     pCur->eState = CURSOR_INVALID;
  3635         -  pCur->cachedRowid = 0;
  3636   3635     return SQLITE_OK;
  3637   3636   }
  3638   3637   int sqlite3BtreeCursor(
  3639   3638     Btree *p,                                   /* The btree */
  3640   3639     int iTable,                                 /* Root page of table to open */
  3641   3640     int wrFlag,                                 /* 1 to write. 0 read-only */
  3642   3641     struct KeyInfo *pKeyInfo,                   /* First arg to xCompare() */
................................................................................
  3669   3668   ** do not need to be zeroed and they are large, so we can save a lot
  3670   3669   ** of run-time by skipping the initialization of those elements.
  3671   3670   */
  3672   3671   void sqlite3BtreeCursorZero(BtCursor *p){
  3673   3672     memset(p, 0, offsetof(BtCursor, iPage));
  3674   3673   }
  3675   3674   
  3676         -/*
  3677         -** Set the cached rowid value of every cursor in the same database file
  3678         -** as pCur and having the same root page number as pCur.  The value is
  3679         -** set to iRowid.
  3680         -**
  3681         -** Only positive rowid values are considered valid for this cache.
  3682         -** The cache is initialized to zero, indicating an invalid cache.
  3683         -** A btree will work fine with zero or negative rowids.  We just cannot
  3684         -** cache zero or negative rowids, which means tables that use zero or
  3685         -** negative rowids might run a little slower.  But in practice, zero
  3686         -** or negative rowids are very uncommon so this should not be a problem.
  3687         -*/
  3688         -void sqlite3BtreeSetCachedRowid(BtCursor *pCur, sqlite3_int64 iRowid){
  3689         -  BtCursor *p;
  3690         -  for(p=pCur->pBt->pCursor; p; p=p->pNext){
  3691         -    if( p->pgnoRoot==pCur->pgnoRoot ) p->cachedRowid = iRowid;
  3692         -  }
  3693         -  assert( pCur->cachedRowid==iRowid );
  3694         -}
  3695         -
  3696         -/*
  3697         -** Return the cached rowid for the given cursor.  A negative or zero
  3698         -** return value indicates that the rowid cache is invalid and should be
  3699         -** ignored.  If the rowid cache has never before been set, then a
  3700         -** zero is returned.
  3701         -*/
  3702         -sqlite3_int64 sqlite3BtreeGetCachedRowid(BtCursor *pCur){
  3703         -  return pCur->cachedRowid;
  3704         -}
  3705         -
  3706   3675   /*
  3707   3676   ** Close a cursor.  The read lock on the database file is released
  3708   3677   ** when the last cursor is closed.
  3709   3678   */
  3710   3679   int sqlite3BtreeCloseCursor(BtCursor *pCur){
  3711   3680     Btree *pBtree = pCur->pBtree;
  3712   3681     if( pBtree ){
................................................................................
  6982   6951     ** that the cursor is already where it needs to be and returns without
  6983   6952     ** doing any work. To avoid thwarting these optimizations, it is important
  6984   6953     ** not to clear the cursor here.
  6985   6954     */
  6986   6955     rc = saveAllCursors(pBt, pCur->pgnoRoot, pCur);
  6987   6956     if( rc ) return rc;
  6988   6957   
  6989         -  /* If this is an insert into a table b-tree, invalidate any incrblob 
  6990         -  ** cursors open on the row being replaced (assuming this is a replace
  6991         -  ** operation - if it is not, the following is a no-op).  */
  6992   6958     if( pCur->pKeyInfo==0 ){
         6959  +    /* If this is an insert into a table b-tree, invalidate any incrblob 
         6960  +    ** cursors open on the row being replaced */
  6993   6961       invalidateIncrblobCursors(p, nKey, 0);
         6962  +
         6963  +    /* If the cursor is currently on the last row and we are appending a
         6964  +    ** new row onto the end, set the "loc" to avoid an unnecessary btreeMoveto()
         6965  +    ** call */
         6966  +    if( pCur->validNKey && nKey>0 && pCur->info.nKey==nKey-1 ){
         6967  +      loc = -1;
         6968  +    }
  6994   6969     }
  6995   6970   
  6996   6971     if( !loc ){
  6997   6972       rc = btreeMoveto(pCur, pKey, nKey, appendBias, &loc);
  6998   6973       if( rc ) return rc;
  6999   6974     }
  7000   6975     assert( pCur->eState==CURSOR_VALID || (pCur->eState==CURSOR_INVALID && loc) );
................................................................................
  7056   7031     ** is advantageous to leave the cursor pointing to the last entry in
  7057   7032     ** the b-tree if possible. If the cursor is left pointing to the last
  7058   7033     ** entry in the table, and the next row inserted has an integer key
  7059   7034     ** larger than the largest existing key, it is possible to insert the
  7060   7035     ** row without seeking the cursor. This can be a big performance boost.
  7061   7036     */
  7062   7037     pCur->info.nSize = 0;
  7063         -  pCur->validNKey = 0;
  7064   7038     if( rc==SQLITE_OK && pPage->nOverflow ){
         7039  +    pCur->validNKey = 0;
  7065   7040       rc = balance(pCur);
  7066   7041   
  7067   7042       /* Must make sure nOverflow is reset to zero even if the balance()
  7068   7043       ** fails. Internal data structure corruption will result otherwise. 
  7069   7044       ** Also, set the cursor state to invalid. This stops saveCursorPosition()
  7070   7045       ** from trying to save the current position of the cursor.  */
  7071   7046       pCur->apPage[pCur->iPage]->nOverflow = 0;

Changes to src/btree.h.

   178    178   int sqlite3BtreePrevious(BtCursor*, int *pRes);
   179    179   int sqlite3BtreeKeySize(BtCursor*, i64 *pSize);
   180    180   int sqlite3BtreeKey(BtCursor*, u32 offset, u32 amt, void*);
   181    181   const void *sqlite3BtreeKeyFetch(BtCursor*, u32 *pAmt);
   182    182   const void *sqlite3BtreeDataFetch(BtCursor*, u32 *pAmt);
   183    183   int sqlite3BtreeDataSize(BtCursor*, u32 *pSize);
   184    184   int sqlite3BtreeData(BtCursor*, u32 offset, u32 amt, void*);
   185         -void sqlite3BtreeSetCachedRowid(BtCursor*, sqlite3_int64);
   186         -sqlite3_int64 sqlite3BtreeGetCachedRowid(BtCursor*);
   187    185   
   188    186   char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
   189    187   struct Pager *sqlite3BtreePager(Btree*);
   190    188   
   191    189   int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
   192    190   void sqlite3BtreeCacheOverflow(BtCursor *);
   193    191   void sqlite3BtreeClearCursor(BtCursor *);

Changes to src/btreeInt.h.

   496    496     BtShared *pBt;            /* The BtShared this cursor points to */
   497    497     BtCursor *pNext, *pPrev;  /* Forms a linked list of all cursors */
   498    498     struct KeyInfo *pKeyInfo; /* Argument passed to comparison function */
   499    499   #ifndef SQLITE_OMIT_INCRBLOB
   500    500     Pgno *aOverflow;          /* Cache of overflow page locations */
   501    501   #endif
   502    502     Pgno pgnoRoot;            /* The root page of this tree */
   503         -  sqlite3_int64 cachedRowid; /* Next rowid cache.  0 means not valid */
   504    503     CellInfo info;            /* A parse of the cell we are pointing at */
   505    504     i64 nKey;        /* Size of pKey, or last integer key */
   506    505     void *pKey;      /* Saved key that was cursor's last known position */
   507    506     int skipNext;    /* Prev() is noop if negative. Next() is noop if positive */
   508    507     u8 wrFlag;                /* True if writable */
   509    508     u8 atLast;                /* Cursor pointing to the last entry */
   510    509     u8 validNKey;             /* True if info.nKey is valid */

Changes to src/vdbe.c.

  3830   3830       ** Others complain about 0x7ffffffffffffffffLL.  The following macro seems
  3831   3831       ** to provide the constant while making all compilers happy.
  3832   3832       */
  3833   3833   #   define MAX_ROWID  (i64)( (((u64)0x7fffffff)<<32) | (u64)0xffffffff )
  3834   3834   #endif
  3835   3835   
  3836   3836       if( !pC->useRandomRowid ){
  3837         -      v = sqlite3BtreeGetCachedRowid(pC->pCursor);
  3838         -      if( v==0 ){
  3839         -        rc = sqlite3BtreeLast(pC->pCursor, &res);
  3840         -        if( rc!=SQLITE_OK ){
  3841         -          goto abort_due_to_error;
  3842         -        }
  3843         -        if( res ){
  3844         -          v = 1;   /* IMP: R-61914-48074 */
         3837  +      rc = sqlite3BtreeLast(pC->pCursor, &res);
         3838  +      if( rc!=SQLITE_OK ){
         3839  +        goto abort_due_to_error;
         3840  +      }
         3841  +      if( res ){
         3842  +        v = 1;   /* IMP: R-61914-48074 */
         3843  +      }else{
         3844  +        assert( sqlite3BtreeCursorIsValid(pC->pCursor) );
         3845  +        rc = sqlite3BtreeKeySize(pC->pCursor, &v);
         3846  +        assert( rc==SQLITE_OK );   /* Cannot fail following BtreeLast() */
         3847  +        if( v>=MAX_ROWID ){
         3848  +          pC->useRandomRowid = 1;
  3845   3849           }else{
  3846         -          assert( sqlite3BtreeCursorIsValid(pC->pCursor) );
  3847         -          rc = sqlite3BtreeKeySize(pC->pCursor, &v);
  3848         -          assert( rc==SQLITE_OK );   /* Cannot fail following BtreeLast() */
  3849         -          if( v>=MAX_ROWID ){
  3850         -            pC->useRandomRowid = 1;
  3851         -          }else{
  3852         -            v++;   /* IMP: R-29538-34987 */
  3853         -          }
         3850  +          v++;   /* IMP: R-29538-34987 */
  3854   3851           }
  3855   3852         }
         3853  +    }
  3856   3854   
  3857   3855   #ifndef SQLITE_OMIT_AUTOINCREMENT
  3858         -      if( pOp->p3 ){
         3856  +    if( pOp->p3 ){
         3857  +      /* Assert that P3 is a valid memory cell. */
         3858  +      assert( pOp->p3>0 );
         3859  +      if( p->pFrame ){
         3860  +        for(pFrame=p->pFrame; pFrame->pParent; pFrame=pFrame->pParent);
         3861  +        /* Assert that P3 is a valid memory cell. */
         3862  +        assert( pOp->p3<=pFrame->nMem );
         3863  +        pMem = &pFrame->aMem[pOp->p3];
         3864  +      }else{
  3859   3865           /* Assert that P3 is a valid memory cell. */
  3860         -        assert( pOp->p3>0 );
  3861         -        if( p->pFrame ){
  3862         -          for(pFrame=p->pFrame; pFrame->pParent; pFrame=pFrame->pParent);
  3863         -          /* Assert that P3 is a valid memory cell. */
  3864         -          assert( pOp->p3<=pFrame->nMem );
  3865         -          pMem = &pFrame->aMem[pOp->p3];
  3866         -        }else{
  3867         -          /* Assert that P3 is a valid memory cell. */
  3868         -          assert( pOp->p3<=(p->nMem-p->nCursor) );
  3869         -          pMem = &aMem[pOp->p3];
  3870         -          memAboutToChange(p, pMem);
  3871         -        }
  3872         -        assert( memIsValid(pMem) );
         3866  +        assert( pOp->p3<=(p->nMem-p->nCursor) );
         3867  +        pMem = &aMem[pOp->p3];
         3868  +        memAboutToChange(p, pMem);
         3869  +      }
         3870  +      assert( memIsValid(pMem) );
  3873   3871   
  3874         -        REGISTER_TRACE(pOp->p3, pMem);
  3875         -        sqlite3VdbeMemIntegerify(pMem);
  3876         -        assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P3) holds an integer */
  3877         -        if( pMem->u.i==MAX_ROWID || pC->useRandomRowid ){
  3878         -          rc = SQLITE_FULL;   /* IMP: R-12275-61338 */
  3879         -          goto abort_due_to_error;
  3880         -        }
  3881         -        if( v<pMem->u.i+1 ){
  3882         -          v = pMem->u.i + 1;
  3883         -        }
  3884         -        pMem->u.i = v;
         3872  +      REGISTER_TRACE(pOp->p3, pMem);
         3873  +      sqlite3VdbeMemIntegerify(pMem);
         3874  +      assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P3) holds an integer */
         3875  +      if( pMem->u.i==MAX_ROWID || pC->useRandomRowid ){
         3876  +        rc = SQLITE_FULL;   /* IMP: R-12275-61338 */
         3877  +        goto abort_due_to_error;
         3878  +      }
         3879  +      if( v<pMem->u.i+1 ){
         3880  +        v = pMem->u.i + 1;
  3885   3881         }
         3882  +      pMem->u.i = v;
         3883  +    }
  3886   3884   #endif
  3887         -
  3888         -      sqlite3BtreeSetCachedRowid(pC->pCursor, v<MAX_ROWID ? v+1 : 0);
  3889         -    }
  3890   3885       if( pC->useRandomRowid ){
  3891   3886         /* IMPLEMENTATION-OF: R-07677-41881 If the largest ROWID is equal to the
  3892   3887         ** largest possible integer (9223372036854775807) then the database
  3893   3888         ** engine starts picking positive candidate ROWIDs at random until
  3894   3889         ** it finds one that is not previously used. */
  3895   3890         assert( pOp->p3==0 );  /* We cannot be in random rowid mode if this is
  3896   3891                                ** an AUTOINCREMENT table. */
................................................................................
  4016   4011     }
  4017   4012     seekResult = ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0);
  4018   4013     if( pData->flags & MEM_Zero ){
  4019   4014       nZero = pData->u.nZero;
  4020   4015     }else{
  4021   4016       nZero = 0;
  4022   4017     }
  4023         -  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  4024   4018     rc = sqlite3BtreeInsert(pC->pCursor, 0, iKey,
  4025   4019                             pData->z, pData->n, nZero,
  4026   4020                             (pOp->p5 & OPFLAG_APPEND)!=0, seekResult
  4027   4021     );
  4028   4022     pC->rowidIsValid = 0;
  4029   4023     pC->deferredMoveto = 0;
  4030   4024     pC->cacheStatus = CACHE_STALE;
................................................................................
  4078   4072     ** below is always a no-op and cannot fail.  We will run it anyhow, though,
  4079   4073     ** to guard against future changes to the code generator.
  4080   4074     **/
  4081   4075     assert( pC->deferredMoveto==0 );
  4082   4076     rc = sqlite3VdbeCursorMoveto(pC);
  4083   4077     if( NEVER(rc!=SQLITE_OK) ) goto abort_due_to_error;
  4084   4078   
  4085         -  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  4086   4079     rc = sqlite3BtreeDelete(pC->pCursor);
  4087   4080     pC->cacheStatus = CACHE_STALE;
  4088   4081   
  4089   4082     /* Invoke the update-hook if required. */
  4090   4083     if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p4.z && pC->isTable ){
  4091   4084       db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE,
  4092   4085                           db->aDb[pC->iDb].zName, pOp->p4.z, iKey);