/ Check-in [21530798]
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:Enable the WHERE_ONEPASS_DESIRED optimization for UPDATE operations on WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 215307985590c2f3f7aa0d5a0b7799155a506045
User & Date: drh 2013-11-07 21:25:13
Context
2013-11-07
21:32
Fix a compiler warning introduced by the previous check-in. check-in: 404bd98f user: drh tags: trunk
21:25
Enable the WHERE_ONEPASS_DESIRED optimization for UPDATE operations on WITHOUT ROWID tables. check-in: 21530798 user: drh tags: trunk
19:43
Add the --stats and --summary options to the wordcount.c test program. check-in: 8aa21e67 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

   124    124   #ifndef SQLITE_OMIT_TRIGGER
   125    125     int isView;            /* True when updating a view (INSTEAD OF trigger) */
   126    126     Trigger *pTrigger;     /* List of triggers on pTab, if required */
   127    127     int tmask;             /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
   128    128   #endif
   129    129     int newmask;           /* Mask of NEW.* columns accessed by BEFORE triggers */
   130    130     int iEph = 0;          /* Ephemeral table holding all primary key values */
          131  +  int nKey;              /* Number of elements in regKey */
   131    132   
   132    133     /* Register Allocations */
   133    134     int regRowCount = 0;   /* A count of rows changed */
   134    135     int regOldRowid;       /* The old rowid */
   135    136     int regNewRowid;       /* The new rowid */
   136    137     int regNew;            /* Content of the NEW.* table in triggers */
   137    138     int regOld = 0;        /* Content of OLD.* table in triggers */
................................................................................
   349    350     
   350    351       /* End the database scan loop.
   351    352       */
   352    353       sqlite3WhereEnd(pWInfo);
   353    354     }else{
   354    355       int iPk;         /* First of nPk memory cells holding PRIMARY KEY value */
   355    356       i16 nPk;         /* Number of components of the PRIMARY KEY */
          357  +    int addrOpen;    /* Address of the OpenEphemeral instruction */
   356    358   
   357    359       assert( pPk!=0 );
   358    360       nPk = pPk->nKeyCol;
   359    361       iPk = pParse->nMem+1;
   360    362       pParse->nMem += nPk;
   361    363       regKey = ++pParse->nMem;
   362    364       iEph = pParse->nTab++;
   363         -    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
          365  +    sqlite3VdbeAddOp2(v, OP_Null, 0, iPk);
          366  +    addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
   364    367       sqlite3VdbeSetP4KeyInfo(pParse, pPk);
   365         -    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 0, 0);
          368  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
          369  +                               WHERE_ONEPASS_DESIRED, 0);
   366    370       if( pWInfo==0 ) goto update_cleanup;
          371  +    okOnePass = sqlite3WhereOkOnePass(pWInfo);
   367    372       for(i=0; i<nPk; i++){
   368    373         sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
   369    374                                         iPk+i);
   370    375       }
   371         -    sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
   372         -                      sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
   373         -    sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);
          376  +    if( okOnePass ){
          377  +      sqlite3VdbeChangeToNoop(v, addrOpen);
          378  +      nKey = nPk;
          379  +      regKey = iPk;
          380  +    }else{
          381  +      sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
          382  +                        sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
          383  +      sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);
          384  +      nKey = 0;
          385  +    }
   374    386       sqlite3WhereEnd(pWInfo);
   375         -    okOnePass = 0;
   376    387     }
   377    388   
   378    389     /* Initialize the count of updated rows
   379    390     */
   380    391     if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
   381    392       regRowCount = ++pParse->nMem;
   382    393       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
................................................................................
   413    424           VdbeComment((v, "%s", pIdx->zName));
   414    425         }
   415    426       }
   416    427     }
   417    428   
   418    429     /* Top of the update loop */
   419    430     labelBreak = sqlite3VdbeMakeLabel(v);
   420         -  if( pPk ){
          431  +  if( okOnePass ){
          432  +    labelContinue = labelBreak;
          433  +    sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
          434  +    if( pPk ){
          435  +      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
          436  +    }
          437  +  }else if( pPk ){
   421    438       labelContinue = sqlite3VdbeMakeLabel(v);
   422    439       sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak);
   423    440       addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey);
   424    441       sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
   425         -  }else if( okOnePass ){
   426         -    labelContinue = labelBreak;
   427         -    sqlite3VdbeAddOp2(v, OP_IsNull, regOldRowid, labelBreak);
   428    442     }else{
   429    443       labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
   430    444                                regOldRowid);
   431    445       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
   432    446     }
   433    447   
   434    448     /* If the record number will change, set register regNewRowid to
................................................................................
   512    526       /* The row-trigger may have deleted the row being updated. In this
   513    527       ** case, jump to the next row. No updates or AFTER triggers are 
   514    528       ** required. This behavior - what happens when the row being updated
   515    529       ** is deleted or renamed by a BEFORE trigger - is left undefined in the
   516    530       ** documentation.
   517    531       */
   518    532       if( pPk ){
   519         -      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
          533  +      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey);
   520    534       }else{
   521    535         sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
   522    536       }
   523    537   
   524    538       /* If it did not delete it, the row-trigger may still have modified 
   525    539       ** some of the columns of the row being updated. Load the values for 
   526    540       ** all columns not modified by the update statement into their 
................................................................................
   544    558       /* Do FK constraint checks. */
   545    559       if( hasFK ){
   546    560         sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
   547    561       }
   548    562   
   549    563       /* Delete the index entries associated with the current record.  */
   550    564       if( pPk ){
   551         -      j1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, 0);
          565  +      j1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, nKey);
   552    566       }else{
   553    567         j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid);
   554    568       }
   555    569       sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx);
   556    570     
   557    571       /* If changing the record number, delete the old record.  */
   558    572       if( hasFK || chngKey || pPk!=0 ){
................................................................................
   588    602   
   589    603     sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
   590    604         TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue);
   591    605   
   592    606     /* Repeat the above with the next record to be updated, until
   593    607     ** all record selected by the WHERE clause have been updated.
   594    608     */
   595         -  if( pPk ){
          609  +  if( okOnePass ){
          610  +    /* Nothing to do at end-of-loop for a single-pass */
          611  +  }else if( pPk ){
   596    612       sqlite3VdbeResolveLabel(v, labelContinue);
   597    613       sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop);
   598         -  }else if( !okOnePass ){
          614  +  }else{
   599    615       sqlite3VdbeAddOp2(v, OP_Goto, 0, labelContinue);
   600    616     }
   601    617     sqlite3VdbeResolveLabel(v, labelBreak);
   602    618   
   603    619     /* Close all tables */
   604    620     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
   605    621       assert( aRegIdx );

Changes to src/where.c.

  4635   4635         }else{
  4636   4636           m = pSrc->colUsed & ~columnsInIndex(pProbe);
  4637   4637           pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
  4638   4638         }
  4639   4639   
  4640   4640         /* Full scan via index */
  4641   4641         if( b
         4642  +       || !HasRowid(pTab)
  4642   4643          || ( m==0
  4643   4644            && pProbe->bUnordered==0
  4644         -         && (!HasRowid(pTab) || pProbe->szIdxRow<pTab->szTabRow)
         4645  +         && (pProbe->szIdxRow<pTab->szTabRow)
  4645   4646            && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  4646   4647            && sqlite3GlobalConfig.bUseCis
  4647   4648            && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
  4648   4649             )
  4649   4650         ){
  4650   4651           pNew->iSortIdx = b ? iSortIdx : 0;
  4651   4652           if( m==0 ){
................................................................................
  4653   4654             **  +  The extra factor K of between 1.1 and 3.0 that depends
  4654   4655             **     on the relative sizes of the table and the index.  K
  4655   4656             **     is smaller for smaller indices, thus favoring them.
  4656   4657             */
  4657   4658             pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 1 +
  4658   4659                           (15*pProbe->szIdxRow)/pTab->szTabRow;
  4659   4660           }else{
  4660         -          assert( b!=0 ); 
  4661   4661             /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
  4662   4662             ** which we will simplify to just N*log2(N) */
  4663   4663             pNew->rRun = rSize + rLogSize;
  4664   4664           }
  4665   4665           whereLoopOutputAdjust(pWC, pNew);
  4666   4666           rc = whereLoopInsert(pBuilder, pNew);
  4667   4667           pNew->nOut = rSize;
................................................................................
  5986   5986     ** The one-pass algorithm only works if the WHERE clause constrains
  5987   5987     ** the statement to update a single row.
  5988   5988     */
  5989   5989     assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
  5990   5990     if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 
  5991   5991      && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
  5992   5992       pWInfo->okOnePass = 1;
  5993         -    pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
         5993  +    if( HasRowid(pTabList->a[0].pTab) ){
         5994  +      pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
         5995  +    }
  5994   5996     }
  5995   5997   
  5996   5998     /* Open all tables in the pTabList and any indices selected for
  5997   5999     ** searching those tables.
  5998   6000     */
  5999   6001     notReady = ~(Bitmask)0;
  6000   6002     for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){