/ Check-in [ff9eab95]
Login

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

Overview
Comment:Merge latest changes from trunk, and also move the perf-counter into the inner loop of sqlite3BtreeMovetoUnpacked().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | vdbe-aux-perf
Files: files | file ages | folders
SHA1: ff9eab95879038ab3015b93f637f6d93bd87f9f4
User & Date: drh 2017-01-09 20:57:38
Context
2017-01-09
20:57
Merge latest changes from trunk, and also move the perf-counter into the inner loop of sqlite3BtreeMovetoUnpacked(). Leaf check-in: ff9eab95 user: drh tags: vdbe-aux-perf
19:55
Remove a redundant assignment statement. check-in: a5fa0965 user: drh tags: trunk
2017-01-07
00:42
This hack illustrates how to use the VDBE_PROFILE mechanism to show which bytecode operators are using resources other than time. In this case, the number of loops through the binary search code in sqlite3BtreeMovetoUnpacked() is measured, for the purpose of helping to identify unnecessary btree searches. check-in: 746b1836 user: drh tags: vdbe-aux-perf
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  5120   5120     assert( pCur->apPage[0]->intKey==pCur->curIntKey );
  5121   5121     assert( pCur->curIntKey || pIdxKey );
  5122   5122     for(;;){
  5123   5123       int lwr, upr, idx, c;
  5124   5124       Pgno chldPg;
  5125   5125       MemPage *pPage = pCur->apPage[pCur->iPage];
  5126   5126       u8 *pCell;                          /* Pointer to current cell in pPage */
  5127         -sqlite3PerfCnt++;
  5128   5127   
  5129   5128       /* pPage->nCell must be greater than zero. If this is the root-page
  5130   5129       ** the cursor would have been INVALID above and this for(;;) loop
  5131   5130       ** not run. If this is not the root-page, then the moveToChild() routine
  5132   5131       ** would have already detected db corruption. Similarly, pPage must
  5133   5132       ** be the right kind (index or table) of b-tree page. Otherwise
  5134   5133       ** a moveToChild() or moveToRoot() call would have detected corruption.  */
................................................................................
  5138   5137       upr = pPage->nCell-1;
  5139   5138       assert( biasRight==0 || biasRight==1 );
  5140   5139       idx = upr>>(1-biasRight); /* idx = biasRight ? upr : (lwr+upr)/2; */
  5141   5140       pCur->aiIdx[pCur->iPage] = (u16)idx;
  5142   5141       if( xRecordCompare==0 ){
  5143   5142         for(;;){
  5144   5143           i64 nCellKey;
         5144  +sqlite3PerfCnt++;
  5145   5145           pCell = findCellPastPtr(pPage, idx);
  5146   5146           if( pPage->intKeyLeaf ){
  5147   5147             while( 0x80 <= *(pCell++) ){
  5148   5148               if( pCell>=pPage->aDataEnd ) return SQLITE_CORRUPT_BKPT;
  5149   5149             }
  5150   5150           }
  5151   5151           getVarint(pCell, (u64*)&nCellKey);
................................................................................
  5171   5171           }
  5172   5172           assert( lwr+upr>=0 );
  5173   5173           idx = (lwr+upr)>>1;  /* idx = (lwr+upr)/2; */
  5174   5174         }
  5175   5175       }else{
  5176   5176         for(;;){
  5177   5177           int nCell;  /* Size of the pCell cell in bytes */
         5178  +sqlite3PerfCnt++;
  5178   5179           pCell = findCellPastPtr(pPage, idx);
  5179   5180   
  5180   5181           /* The maximum supported page-size is 65536 bytes. This means that
  5181   5182           ** the maximum number of record bytes stored on an index B-Tree
  5182   5183           ** page is less than 16384 bytes and may be stored as a 2-byte
  5183   5184           ** varint. This information is used to attempt to avoid parsing 
  5184   5185           ** the entire cell by checking for the cases where the record is 

Changes to src/delete.c.

   515    515         if( eOnePass==ONEPASS_SINGLE && sqlite3IsToplevel(pParse) ){
   516    516           pParse->isMultiWrite = 0;
   517    517         }
   518    518       }else
   519    519   #endif
   520    520       {
   521    521         int count = (pParse->nested==0);    /* True to count changes */
   522         -      int iIdxNoSeek = -1;
   523         -      if( bComplex==0 && aiCurOnePass[1]!=iDataCur ){
   524         -        iIdxNoSeek = aiCurOnePass[1];
   525         -      }
   526    522         sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
   527         -          iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek);
          523  +          iKey, nKey, count, OE_Default, eOnePass, aiCurOnePass[1]);
   528    524       }
   529    525     
   530    526       /* End of the loop over all rowids/primary-keys. */
   531    527       if( eOnePass!=ONEPASS_OFF ){
   532    528         sqlite3VdbeResolveLabel(v, addrBypass);
   533    529         sqlite3WhereEnd(pWInfo);
   534    530       }else if( pPk ){
................................................................................
   600    596   **   ONEPASS_MULTI.  If eMode is not ONEPASS_OFF, then the cursor
   601    597   **   iDataCur already points to the row to delete. If eMode is ONEPASS_OFF
   602    598   **   then this function must seek iDataCur to the entry identified by iPk
   603    599   **   and nPk before reading from it.
   604    600   **
   605    601   **   If eMode is ONEPASS_MULTI, then this call is being made as part
   606    602   **   of a ONEPASS delete that affects multiple rows. In this case, if 
   607         -**   iIdxNoSeek is a valid cursor number (>=0), then its position should
   608         -**   be preserved following the delete operation. Or, if iIdxNoSeek is not
   609         -**   a valid cursor number, the position of iDataCur should be preserved
   610         -**   instead.
          603  +**   iIdxNoSeek is a valid cursor number (>=0) and is not the same as
          604  +**   iDataCur, then its position should be preserved following the delete
          605  +**   operation. Or, if iIdxNoSeek is not a valid cursor number, the
          606  +**   position of iDataCur should be preserved instead.
   611    607   **
   612    608   ** iIdxNoSeek:
   613         -**   If iIdxNoSeek is a valid cursor number (>=0), then it identifies an
   614         -**   index cursor (from within array of cursors starting at iIdxCur) that
   615         -**   already points to the index entry to be deleted.
          609  +**   If iIdxNoSeek is a valid cursor number (>=0) not equal to iDataCur,
          610  +**   then it identifies an index cursor (from within array of cursors
          611  +**   starting at iIdxCur) that already points to the index entry to be deleted.
          612  +**   Except, this optimization is disabled if there are BEFORE triggers since
          613  +**   the trigger body might have moved the cursor.
   616    614   */
   617    615   void sqlite3GenerateRowDelete(
   618    616     Parse *pParse,     /* Parsing context */
   619    617     Table *pTab,       /* Table containing the row to be deleted */
   620    618     Trigger *pTrigger, /* List of triggers to (potentially) fire */
   621    619     int iDataCur,      /* Cursor from which column data is extracted */
   622    620     int iIdxCur,       /* First index cursor */
................................................................................
   679    677       addrStart = sqlite3VdbeCurrentAddr(v);
   680    678       sqlite3CodeRowTrigger(pParse, pTrigger, 
   681    679           TK_DELETE, 0, TRIGGER_BEFORE, pTab, iOld, onconf, iLabel
   682    680       );
   683    681   
   684    682       /* If any BEFORE triggers were coded, then seek the cursor to the 
   685    683       ** row to be deleted again. It may be that the BEFORE triggers moved
   686         -    ** the cursor or of already deleted the row that the cursor was
          684  +    ** the cursor or already deleted the row that the cursor was
   687    685       ** pointing to.
          686  +    **
          687  +    ** Also disable the iIdxNoSeek optimization since the BEFORE trigger
          688  +    ** may have moved that cursor.
   688    689       */
   689    690       if( addrStart<sqlite3VdbeCurrentAddr(v) ){
   690    691         sqlite3VdbeAddOp4Int(v, opSeek, iDataCur, iLabel, iPk, nPk);
   691    692         VdbeCoverageIf(v, opSeek==OP_NotExists);
   692    693         VdbeCoverageIf(v, opSeek==OP_NotFound);
          694  +      testcase( iIdxNoSeek>=0 );
          695  +      iIdxNoSeek = -1;
   693    696       }
   694    697   
   695    698       /* Do FK processing. This call checks that any FK constraints that
   696    699       ** refer to this table (i.e. constraints attached to other tables) 
   697    700       ** are not violated by deleting this row.  */
   698    701       sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0);
   699    702     }
................................................................................
   712    715       u8 p5 = 0;
   713    716       sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,iIdxNoSeek);
   714    717       sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0));
   715    718       sqlite3VdbeAppendP4(v, (char*)pTab, P4_TABLE);
   716    719       if( eMode!=ONEPASS_OFF ){
   717    720         sqlite3VdbeChangeP5(v, OPFLAG_AUXDELETE);
   718    721       }
   719         -    if( iIdxNoSeek>=0 ){
          722  +    if( iIdxNoSeek>=0 && iIdxNoSeek!=iDataCur ){
   720    723         sqlite3VdbeAddOp1(v, OP_Delete, iIdxNoSeek);
   721    724       }
   722    725       if( eMode==ONEPASS_MULTI ) p5 |= OPFLAG_SAVEPOSITION;
   723    726       sqlite3VdbeChangeP5(v, p5);
   724    727     }
   725    728   
   726    729     /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to

Changes to src/insert.c.

  1647   1647           assert( onError==OE_Replace );
  1648   1648           sqlite3MultiWrite(pParse);
  1649   1649           if( db->flags&SQLITE_RecTriggers ){
  1650   1650             pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  1651   1651           }
  1652   1652           sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
  1653   1653               regR, nPkField, 0, OE_Replace,
  1654         -            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), -1);
         1654  +            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1655   1655           seenReplace = 1;
  1656   1656           break;
  1657   1657         }
  1658   1658       }
  1659   1659       sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1660   1660       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
  1661   1661     }
................................................................................
  2134   2134         autoIncStep(pParse, regAutoinc, regRowid);
  2135   2135       }else if( pDest->pIndex==0 ){
  2136   2136         addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
  2137   2137       }else{
  2138   2138         addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
  2139   2139         assert( (pDest->tabFlags & TF_Autoincrement)==0 );
  2140   2140       }
  2141         -    sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
         2141  +    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
  2142   2142       if( db->flags & SQLITE_Vacuum ){
  2143   2143         sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1);
  2144   2144         insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|
  2145   2145                              OPFLAG_APPEND|OPFLAG_USESEEKRESULT;
  2146   2146       }else{
  2147   2147         insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND;
  2148   2148       }
................................................................................
  2166   2166       sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);
  2167   2167       VdbeComment((v, "%s", pSrcIdx->zName));
  2168   2168       sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest);
  2169   2169       sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
  2170   2170       sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
  2171   2171       VdbeComment((v, "%s", pDestIdx->zName));
  2172   2172       addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
  2173         -    sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
         2173  +    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
  2174   2174       if( db->flags & SQLITE_Vacuum ){
  2175   2175         /* This INSERT command is part of a VACUUM operation, which guarantees
  2176   2176         ** that the destination table is empty. If all indexed columns use
  2177   2177         ** collation sequence BINARY, then it can also be assumed that the
  2178   2178         ** index will be populated by inserting keys in strictly sorted 
  2179   2179         ** order. In this case, instead of seeking within the b-tree as part
  2180   2180         ** of every OP_IdxInsert opcode, an OP_Last is added before the

Changes to src/select.c.

   653    653     int r1;
   654    654   
   655    655     v = pParse->pVdbe;
   656    656     r1 = sqlite3GetTempReg(pParse);
   657    657     sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, iMem, N); VdbeCoverage(v);
   658    658     sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, N, r1);
   659    659     sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r1, iMem, N);
          660  +  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
   660    661     sqlite3ReleaseTempReg(pParse, r1);
   661    662   }
   662    663   
   663    664   /*
   664    665   ** This routine generates the code for the inside of the inner loop
   665    666   ** of a SELECT.
   666    667   **

Changes to src/sqlite.h.in.

   255    255   ** ^The sqlite3_int64 and sqlite_int64 types can store integer values
   256    256   ** between -9223372036854775808 and +9223372036854775807 inclusive.  ^The
   257    257   ** sqlite3_uint64 and sqlite_uint64 types can store integer values 
   258    258   ** between 0 and +18446744073709551615 inclusive.
   259    259   */
   260    260   #ifdef SQLITE_INT64_TYPE
   261    261     typedef SQLITE_INT64_TYPE sqlite_int64;
   262         -  typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;
          262  +# ifdef SQLITE_UINT64_TYPE
          263  +    typedef SQLITE_UINT64_TYPE sqlite_uint64;
          264  +# else  
          265  +    typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;
          266  +# endif
   263    267   #elif defined(_MSC_VER) || defined(__BORLANDC__)
   264    268     typedef __int64 sqlite_int64;
   265    269     typedef unsigned __int64 sqlite_uint64;
   266    270   #else
   267    271     typedef long long int sqlite_int64;
   268    272     typedef unsigned long long int sqlite_uint64;
   269    273   #endif

Changes to src/vdbe.c.

  1192   1192   
  1193   1193     assert( pOp->p1>0 && pOp->p1<=p->nVar );
  1194   1194     assert( pOp->p4.z==0 || pOp->p4.z==sqlite3VListNumToName(p->pVList,pOp->p1) );
  1195   1195     pVar = &p->aVar[pOp->p1 - 1];
  1196   1196     if( sqlite3VdbeMemTooBig(pVar) ){
  1197   1197       goto too_big;
  1198   1198     }
  1199         -  pOut = out2Prerelease(p, pOp);
         1199  +  pOut = &aMem[pOp->p2];
  1200   1200     sqlite3VdbeMemShallowCopy(pOut, pVar, MEM_Static);
  1201   1201     UPDATE_MAX_BLOBSIZE(pOut);
  1202   1202     break;
  1203   1203   }
  1204   1204   
  1205   1205   /* Opcode: Move P1 P2 P3 * *
  1206   1206   ** Synopsis: r[P2@P3]=r[P1@P3]
................................................................................
  4629   4629     assert( rc!=SQLITE_OK || (pOut->flags & MEM_Blob) );
  4630   4630     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  4631   4631     if( rc ) goto abort_due_to_error;
  4632   4632     p->apCsr[pOp->p3]->cacheStatus = CACHE_STALE;
  4633   4633     break;
  4634   4634   }
  4635   4635   
  4636         -/* Opcode: RowData P1 P2 * * *
         4636  +/* Opcode: RowData P1 P2 P3 * *
  4637   4637   ** Synopsis: r[P2]=data
  4638   4638   **
  4639   4639   ** Write into register P2 the complete row content for the row at 
  4640   4640   ** which cursor P1 is currently pointing.
  4641   4641   ** There is no interpretation of the data.  
  4642   4642   ** It is just copied onto the P2 register exactly as 
  4643   4643   ** it is found in the database file.
  4644   4644   **
  4645   4645   ** If cursor P1 is an index, then the content is the key of the row.
  4646   4646   ** If cursor P2 is a table, then the content extracted is the data.
  4647   4647   **
  4648   4648   ** If the P1 cursor must be pointing to a valid row (not a NULL row)
  4649   4649   ** of a real table, not a pseudo-table.
         4650  +**
         4651  +** If P3!=0 then this opcode is allowed to make an ephermeral pointer
         4652  +** into the database page.  That means that the content of the output
         4653  +** register will be invalidated as soon as the cursor moves - including
         4654  +** moves caused by other cursors that "save" the the current cursors
         4655  +** position in order that they can write to the same table.  If P3==0
         4656  +** then a copy of the data is made into memory.  P3!=0 is faster, but
         4657  +** P3==0 is safer.
         4658  +**
         4659  +** If P3!=0 then the content of the P2 register is unsuitable for use
         4660  +** in OP_Result and any OP_Result will invalidate the P2 register content.
         4661  +** The P2 register content is invalidated by opcodes like OP_Function or
         4662  +** by any use of another cursor pointing to the same table.
  4650   4663   */
  4651   4664   case OP_RowData: {
  4652   4665     VdbeCursor *pC;
  4653   4666     BtCursor *pCrsr;
  4654   4667     u32 n;
  4655   4668   
  4656         -  pOut = &aMem[pOp->p2];
  4657         -  memAboutToChange(p, pOut);
         4669  +  pOut = out2Prerelease(p, pOp);
  4658   4670   
  4659   4671     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  4660   4672     pC = p->apCsr[pOp->p1];
  4661   4673     assert( pC!=0 );
  4662   4674     assert( pC->eCurType==CURTYPE_BTREE );
  4663   4675     assert( isSorter(pC)==0 );
  4664   4676     assert( pC->nullRow==0 );
................................................................................
  4681   4693   #endif
  4682   4694   
  4683   4695     n = sqlite3BtreePayloadSize(pCrsr);
  4684   4696     if( n>(u32)db->aLimit[SQLITE_LIMIT_LENGTH] ){
  4685   4697       goto too_big;
  4686   4698     }
  4687   4699     testcase( n==0 );
  4688         -  if( sqlite3VdbeMemClearAndResize(pOut, MAX(n,32)) ){
  4689         -    goto no_mem;
  4690         -  }
  4691         -  pOut->n = n;
  4692         -  MemSetTypeFlag(pOut, MEM_Blob);
  4693         -  rc = sqlite3BtreePayload(pCrsr, 0, n, pOut->z);
         4700  +  rc = sqlite3VdbeMemFromBtree(pCrsr, 0, n, pOut);
  4694   4701     if( rc ) goto abort_due_to_error;
  4695         -  pOut->enc = SQLITE_UTF8;  /* In case the blob is ever cast to text */
         4702  +  if( !pOp->p3 ) Deephemeralize(pOut);
  4696   4703     UPDATE_MAX_BLOBSIZE(pOut);
  4697   4704     REGISTER_TRACE(pOp->p2, pOut);
  4698   4705     break;
  4699   4706   }
  4700   4707   
  4701   4708   /* Opcode: Rowid P1 P2 * * *
  4702   4709   ** Synopsis: r[P2]=rowid
................................................................................
  5198   5205         pTabCur->deferredMoveto = 1;
  5199   5206         assert( pOp->p4type==P4_INTARRAY || pOp->p4.ai==0 );
  5200   5207         pTabCur->aAltMap = pOp->p4.ai;
  5201   5208         pTabCur->pAltCursor = pC;
  5202   5209       }else{
  5203   5210         pOut = out2Prerelease(p, pOp);
  5204   5211         pOut->u.i = rowid;
  5205         -      pOut->flags = MEM_Int;
  5206   5212       }
  5207   5213     }else{
  5208   5214       assert( pOp->opcode==OP_IdxRowid );
  5209   5215       sqlite3VdbeMemSetNull(&aMem[pOp->p2]);
  5210   5216     }
  5211   5217     break;
  5212   5218   }