/ Check-in [7c4b6d54]
Login

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

Overview
Comment:Avoid unnecessary cursor seeks during upsert processing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert-opt2
Files: files | file ages | folders
SHA3-256:7c4b6d5475092a3e205f01a6972366e27a404568e8e7ba327f2feefac2ce2c7c
User & Date: drh 2018-04-20 15:56:24
Context
2018-04-20
16:27
Minor simplification of the previous checkin. check-in: d1906689 user: drh tags: upsert-opt2
15:56
Avoid unnecessary cursor seeks during upsert processing. check-in: 7c4b6d54 user: drh tags: upsert-opt2
15:34
Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. check-in: d8eb9f8d user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

   208    208     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
   209    209       if( pPk==pIdx ){
   210    210         iDataCur = pParse->nTab;
   211    211       }
   212    212       pParse->nTab++;
   213    213     }
   214    214     if( pUpsert ){
          215  +    /* On an UPSERT, reuse the same cursors already opened by INSERT */
   215    216       iDataCur = pUpsert->iDataCur;
   216    217       iIdxCur = pUpsert->iIdxCur;
   217    218       pParse->nTab = iBaseCur;
   218    219     }
   219    220     pTabList->a[0].iCursor = iDataCur;
   220    221   
   221    222     /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
................................................................................
   385    386     if( IsVirtual(pTab) ){
   386    387       updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
   387    388                          pWhere, onError);
   388    389       goto update_cleanup;
   389    390     }
   390    391   #endif
   391    392   
   392         -  /* Initialize the count of updated rows */
   393         -  if( (db->flags&SQLITE_CountRows)!=0
   394         -   && !pParse->pTriggerTab
   395         -   && !pParse->nested
   396         -   && !pUpsert
   397         -  ){
   398         -    regRowCount = ++pParse->nMem;
   399         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   400         -  }
          393  +  /* Jump to labelBreak to abandon further processing of this UPDATE */
          394  +  labelBreak = sqlite3VdbeMakeLabel(v);
   401    395   
   402         -  if( HasRowid(pTab) ){
   403         -    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
          396  +  if( pUpsert ){
          397  +    /* If this is an UPSERT, then all cursors have already been opened by
          398  +    ** the outer INSERT and the data cursor should be pointing at the row
          399  +    ** that is to be updated.  So bypass the code that searches for the
          400  +    ** row(s) to be updated.
          401  +    */
          402  +    pWInfo = 0;
          403  +    eOnePass = ONEPASS_SINGLE;
          404  +    labelContinue = labelBreak;
          405  +    if( !HasRowid(pTab) ){
          406  +      nPk = pPk->nKeyCol;
          407  +      iPk = pParse->nMem+1;
          408  +      pParse->nMem += nPk;
          409  +      regKey = ++pParse->nMem;
          410  +    }
          411  +    sqlite3ExprIfFalse(pParse, pWhere, labelBreak, SQLITE_JUMPIFNULL);
   404    412     }else{
   405         -    assert( pPk!=0 );
   406         -    nPk = pPk->nKeyCol;
   407         -    iPk = pParse->nMem+1;
   408         -    pParse->nMem += nPk;
   409         -    regKey = ++pParse->nMem;
   410         -    iEph = pParse->nTab++;
   411         -
   412         -    sqlite3VdbeAddOp3(v, OP_Null, 0, iPk, iPk+nPk-1);
   413         -    addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
   414         -    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
   415         -  }
   416         -
   417         -  /* Begin the database scan. 
   418         -  **
   419         -  ** Do not consider a single-pass strategy for a multi-row update if
   420         -  ** there are any triggers or foreign keys to process, or rows may
   421         -  ** be deleted as a result of REPLACE conflict handling. Any of these
   422         -  ** things might disturb a cursor being used to scan through the table
   423         -  ** or index, causing a single-pass approach to malfunction.  */
   424         -  flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE;
   425         -  if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
   426         -    flags |= WHERE_ONEPASS_MULTIROW;
   427         -  }
   428         -  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
   429         -  if( pWInfo==0 ) goto update_cleanup;
   430         -
   431         -  /* A one-pass strategy that might update more than one row may not
   432         -  ** be used if any column of the index used for the scan is being
   433         -  ** updated. Otherwise, if there is an index on "b", statements like
   434         -  ** the following could create an infinite loop:
   435         -  **
   436         -  **   UPDATE t1 SET b=b+1 WHERE b>?
   437         -  **
   438         -  ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
   439         -  ** strategy that uses an index for which one or more columns are being
   440         -  ** updated.  */
   441         -  eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   442         -  if( eOnePass==ONEPASS_MULTI ){
   443         -    int iCur = aiCurOnePass[1];
   444         -    if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
   445         -      eOnePass = ONEPASS_OFF;
   446         -    }
   447         -    assert( iCur!=iDataCur || !HasRowid(pTab) );
   448         -  }
          413  +    /* Not an UPSERT.  Normal processing.  Begin by
          414  +    ** initialize the count of updated rows */
          415  +    if( (db->flags&SQLITE_CountRows)!=0
          416  +     && !pParse->pTriggerTab
          417  +     && !pParse->nested
          418  +    ){
          419  +      regRowCount = ++pParse->nMem;
          420  +      sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
          421  +    }
          422  +  
          423  +    if( HasRowid(pTab) ){
          424  +      sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
          425  +    }else{
          426  +      assert( pPk!=0 );
          427  +      nPk = pPk->nKeyCol;
          428  +      iPk = pParse->nMem+1;
          429  +      pParse->nMem += nPk;
          430  +      regKey = ++pParse->nMem;
          431  +      iEph = pParse->nTab++;
          432  +  
          433  +      sqlite3VdbeAddOp3(v, OP_Null, 0, iPk, iPk+nPk-1);
          434  +      addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
          435  +      sqlite3VdbeSetP4KeyInfo(pParse, pPk);
          436  +    }
          437  +  
          438  +    /* Begin the database scan. 
          439  +    **
          440  +    ** Do not consider a single-pass strategy for a multi-row update if
          441  +    ** there are any triggers or foreign keys to process, or rows may
          442  +    ** be deleted as a result of REPLACE conflict handling. Any of these
          443  +    ** things might disturb a cursor being used to scan through the table
          444  +    ** or index, causing a single-pass approach to malfunction.  */
          445  +    flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE;
          446  +    if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
          447  +      flags |= WHERE_ONEPASS_MULTIROW;
          448  +    }
          449  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
          450  +    if( pWInfo==0 ) goto update_cleanup;
   449    451     
          452  +    /* A one-pass strategy that might update more than one row may not
          453  +    ** be used if any column of the index used for the scan is being
          454  +    ** updated. Otherwise, if there is an index on "b", statements like
          455  +    ** the following could create an infinite loop:
          456  +    **
          457  +    **   UPDATE t1 SET b=b+1 WHERE b>?
          458  +    **
          459  +    ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
          460  +    ** strategy that uses an index for which one or more columns are being
          461  +    ** updated.  */
          462  +    eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
          463  +    if( eOnePass==ONEPASS_MULTI ){
          464  +      int iCur = aiCurOnePass[1];
          465  +      if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
          466  +        eOnePass = ONEPASS_OFF;
          467  +      }
          468  +      assert( iCur!=iDataCur || !HasRowid(pTab) );
          469  +    }
          470  +  }
          471  +
   450    472     if( HasRowid(pTab) ){
   451    473       /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
   452    474       ** mode, write the rowid into the FIFO. In either of the one-pass modes,
   453    475       ** leave it in register regOldRowid.  */
   454    476       sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
   455    477       if( eOnePass==ONEPASS_OFF ){
   456    478         sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid);
................................................................................
   462    484       ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table 
   463    485       ** is not required) and leave the PK fields in the array of registers.  */
   464    486       for(i=0; i<nPk; i++){
   465    487         assert( pPk->aiColumn[i]>=0 );
   466    488         sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur,pPk->aiColumn[i],iPk+i);
   467    489       }
   468    490       if( eOnePass ){
   469         -      sqlite3VdbeChangeToNoop(v, addrOpen);
          491  +      if( addrOpen ) sqlite3VdbeChangeToNoop(v, addrOpen);
   470    492         nKey = nPk;
   471    493         regKey = iPk;
   472    494       }else{
   473    495         sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
   474    496                           sqlite3IndexAffinityStr(db, pPk), nPk);
   475    497         sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk);
   476    498       }
   477    499     }
   478    500   
   479         -  if( eOnePass!=ONEPASS_MULTI ){
   480         -    sqlite3WhereEnd(pWInfo);
   481         -  }
   482         -
   483         -  labelBreak = sqlite3VdbeMakeLabel(v);
   484         -  if( !isView && pUpsert==0 ){
   485         -    int addrOnce = 0;
   486         -
   487         -    /* Open every index that needs updating. */
          501  +  if( pUpsert==0 ){
          502  +    if( eOnePass!=ONEPASS_MULTI ){
          503  +      sqlite3WhereEnd(pWInfo);
          504  +    }
          505  +  
          506  +    if( !isView ){
          507  +      int addrOnce = 0;
          508  +  
          509  +      /* Open every index that needs updating. */
          510  +      if( eOnePass!=ONEPASS_OFF ){
          511  +        if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
          512  +        if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
          513  +      }
          514  +  
          515  +      if( eOnePass==ONEPASS_MULTI && (nIdx-(aiCurOnePass[1]>=0))>0 ){
          516  +        addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
          517  +      }
          518  +      sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur,
          519  +                                 aToOpen, 0, 0);
          520  +      if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
          521  +    }
          522  +  
          523  +    /* Top of the update loop */
   488    524       if( eOnePass!=ONEPASS_OFF ){
   489         -      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
   490         -      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
   491         -    }
   492         -
   493         -    if( eOnePass==ONEPASS_MULTI && (nIdx-(aiCurOnePass[1]>=0))>0 ){
   494         -      addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
   495         -    }
   496         -    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur, aToOpen,
   497         -                               0, 0);
   498         -    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
   499         -  }
   500         -
   501         -  /* Top of the update loop */
   502         -  if( eOnePass!=ONEPASS_OFF ){
   503         -    if( !isView && aiCurOnePass[0]!=iDataCur && aiCurOnePass[1]!=iDataCur ){
   504         -      assert( pPk );
   505         -      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
   506         -      VdbeCoverageNeverTaken(v);
   507         -    }
   508         -    if( eOnePass==ONEPASS_SINGLE ){
   509         -      labelContinue = labelBreak;
   510         -    }else{
          525  +      if( !isView && aiCurOnePass[0]!=iDataCur && aiCurOnePass[1]!=iDataCur ){
          526  +        assert( pPk );
          527  +        sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey,nKey);
          528  +        VdbeCoverageNeverTaken(v);
          529  +      }
          530  +      if( eOnePass==ONEPASS_SINGLE ){
          531  +        labelContinue = labelBreak;
          532  +      }else{
          533  +        labelContinue = sqlite3VdbeMakeLabel(v);
          534  +      }
          535  +      sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
          536  +      VdbeCoverageIf(v, pPk==0);
          537  +      VdbeCoverageIf(v, pPk!=0);
          538  +    }else if( pPk ){
   511    539         labelContinue = sqlite3VdbeMakeLabel(v);
          540  +      sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v);
          541  +      addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey);
          542  +      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
          543  +      VdbeCoverage(v);
          544  +    }else{
          545  +      labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet,labelBreak,
          546  +                               regOldRowid);
          547  +      VdbeCoverage(v);
          548  +      sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
          549  +      VdbeCoverage(v);
   512    550       }
   513         -    sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
   514         -    VdbeCoverageIf(v, pPk==0);
   515         -    VdbeCoverageIf(v, pPk!=0);
   516         -  }else if( pPk ){
   517         -    labelContinue = sqlite3VdbeMakeLabel(v);
   518         -    sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v);
   519         -    addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey);
   520         -    sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
   521         -    VdbeCoverage(v);
   522         -  }else{
   523         -    labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
   524         -                             regOldRowid);
   525         -    VdbeCoverage(v);
   526         -    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
   527         -    VdbeCoverage(v);
   528    551     }
   529    552   
   530    553     /* If the rowid value will change, set register regNewRowid to
   531    554     ** contain the new value. If the rowid is not being modified,
   532    555     ** then regNewRowid is the same register as regOldRowid, which is
   533    556     ** already populated.  */
   534    557     assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid );

Changes to src/upsert.c.

   199    199     Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
   200    200     Table *pTab,          /* The table being updated */
   201    201     Index *pIdx,          /* The UNIQUE constraint that failed */
   202    202     int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
   203    203   ){
   204    204     Vdbe *v = pParse->pVdbe;
   205    205     sqlite3 *db = pParse->db;
   206         -  int regKey;               /* Register(s) containing the key */
   207         -  Expr *pWhere;             /* Where clause for the UPDATE */
   208         -  Expr *pE1, *pE2;
   209    206     SrcList *pSrc;            /* FROM clause for the UPDATE */
          207  +  int iDataCur = pUpsert->iDataCur;
   210    208   
   211    209     assert( v!=0 );
   212    210     VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
   213         -  pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
   214         -  if( pIdx==0 || HasRowid(pTab) ){
   215         -    /* We are dealing with an IPK */
   216         -    regKey = ++pParse->nMem;
   217         -    if( pIdx ){
   218         -      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
          211  +  if( pIdx && iCur!=iDataCur ){
          212  +    if( HasRowid(pTab) ){
          213  +      int regRowid = sqlite3GetTempReg(pParse);
          214  +      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid);
          215  +      sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid);
          216  +      VdbeCoverage(v);
          217  +      sqlite3ReleaseTempReg(pParse, regRowid);
   219    218       }else{
   220         -      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
   221         -    }
   222         -    pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
   223         -    if( pE1 ){
   224         -      pE1->pTab = pTab;
   225         -      pE1->iTable = pUpsert->iDataCur;
   226         -      pE1->iColumn = -1;
   227         -    }
   228         -    pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
   229         -    if( pE2 ){
   230         -      pE2->iTable = regKey;
   231         -      pE2->affinity = SQLITE_AFF_INTEGER;
   232         -    }
   233         -    pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
   234         -  }else{
   235         -    /* a WITHOUT ROWID table */
   236         -    int i, j;
   237         -    for(i=0; i<pIdx->nKeyCol; i++){
   238         -      regKey = ++pParse->nMem;
   239         -      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
   240         -      j = pIdx->aiColumn[i];
   241         -      VdbeComment((v, "%s", pTab->aCol[j].zName));
   242         -      pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
   243         -      if( pE1 ){
   244         -        pE1->pTab = pTab;
   245         -        pE1->iTable = pUpsert->iDataCur;
   246         -        pE1->iColumn = j;
          219  +      Index *pPk = sqlite3PrimaryKeyIndex(pTab);
          220  +      int nPk = pPk->nKeyCol;
          221  +      int iPk = pParse->nMem+1;
          222  +      int i;
          223  +      pParse->nMem += nPk;
          224  +      for(i=0; i<nPk; i++){
          225  +        int k;
          226  +        assert( pPk->aiColumn[i]>=0 );
          227  +        k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
          228  +        sqlite3VdbeAddOp3(v, OP_Column, iCur, k, iPk+i);
   247    229         }
   248         -      pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
   249         -      if( pE2 ){
   250         -        pE2->iTable = regKey;
   251         -        pE2->affinity = pTab->zColAff[j];
   252         -      }
   253         -      pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
          230  +      i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
          231  +      VdbeCoverage(v);
          232  +      sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_CORRUPT, OE_Abort);
          233  +      sqlite3VdbeJumpHere(v, i);
   254    234       }
   255    235     }
   256    236     /* pUpsert does not own pUpsertSrc - the outer INSERT statement does.  So
   257    237     ** we have to make a copy before passing it down into sqlite3Update() */
   258    238     pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
   259    239     sqlite3Update(pParse, pSrc, pUpsert->pUpsertSet,
   260         -      pWhere, OE_Abort, 0, 0, pUpsert);
   261         -  pUpsert->pUpsertSet = 0;  /* Will have been deleted by sqlite3Update() */
          240  +      pUpsert->pUpsertWhere, OE_Abort, 0, 0, pUpsert);
          241  +  pUpsert->pUpsertSet = 0;    /* Will have been deleted by sqlite3Update() */
          242  +  pUpsert->pUpsertWhere = 0;  /* Will have been deleted by sqlite3Update() */
   262    243     VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
   263    244   }
   264    245   
   265    246   #endif /* SQLITE_OMIT_UPSERT */