/ Check-in [bec5b6d4]
Login

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

Overview
Comment:Avoid superfluous cursor seeks in "INSERT OR REPLACE" statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bec5b6d4d083556d111a89186b4f7b35b5e7cebf
User & Date: dan 2016-11-08 19:22:32
Context
2016-11-09
00:57
Remove the "experimental" marking from the sqlite3_preupdate interfaces. But be sure all the interface definitions are within documentation. check-in: d6dd2ad3 user: drh tags: trunk
00:10
Enhance the OP_IdxInsert opcode to optionally accept unpacked key material. check-in: 89d958ab user: drh tags: unpacked-IdxInsert
2016-11-08
19:22
Avoid superfluous cursor seeks in "INSERT OR REPLACE" statements. check-in: bec5b6d4 user: dan tags: trunk
17:19
Avoid generating OP_TableLock unnecessary instructions on btrees that are not sharable. check-in: 8cb8516d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   991    991         sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
   992    992         sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
   993    993         sqlite3MayAbort(pParse);
   994    994       }else
   995    995   #endif
   996    996       {
   997    997         int isReplace;    /* Set to true if constraints may cause a replace */
          998  +      int bUseSeek;     /* True to use OPFLAG_SEEKRESULT */
   998    999         sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   999   1000             regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
  1000   1001         );
  1001   1002         sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
         1003  +
         1004  +      /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
         1005  +      ** constraints or (b) there are no triggers and this table is not a
         1006  +      ** parent table in a foreign key constraint. It is safe to set the
         1007  +      ** flag in the second case as if any REPLACE constraint is hit, an
         1008  +      ** OP_Delete or OP_IdxDelete instruction will be executed on each 
         1009  +      ** cursor that is disturbed. And these instructions both clear the
         1010  +      ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
         1011  +      ** functionality.  */
         1012  +      bUseSeek = (isReplace==0 || (pTrigger==0 &&
         1013  +          ((db->flags & SQLITE_ForeignKeys)==0 || sqlite3FkReferences(pTab)==0)
         1014  +      ));
  1002   1015         sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
  1003         -                               regIns, aRegIdx, 0, appendFlag, isReplace==0);
         1016  +          regIns, aRegIdx, 0, appendFlag, bUseSeek
         1017  +      );
  1004   1018       }
  1005   1019     }
  1006   1020   
  1007   1021     /* Update the count of rows that are inserted
  1008   1022     */
  1009   1023     if( (db->flags & SQLITE_CountRows)!=0 ){
  1010   1024       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);

Changes to src/vdbe.c.

  4550   4550         nExtraDelete--;
  4551   4551       }
  4552   4552     }
  4553   4553   #endif
  4554   4554   
  4555   4555     rc = sqlite3BtreeDelete(pC->uc.pCursor, pOp->p5);
  4556   4556     pC->cacheStatus = CACHE_STALE;
         4557  +  pC->seekResult = 0;
  4557   4558     if( rc ) goto abort_due_to_error;
  4558   4559   
  4559   4560     /* Invoke the update-hook if required. */
  4560   4561     if( opflags & OPFLAG_NCHANGE ){
  4561   4562       p->nChange++;
  4562   4563       if( db->xUpdateCallback && HasRowid(pTab) ){
  4563   4564         db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, pTab->zName,
................................................................................
  5105   5106     if( rc ) goto abort_due_to_error;
  5106   5107     if( res==0 ){
  5107   5108       rc = sqlite3BtreeDelete(pCrsr, BTREE_AUXDELETE);
  5108   5109       if( rc ) goto abort_due_to_error;
  5109   5110     }
  5110   5111     assert( pC->deferredMoveto==0 );
  5111   5112     pC->cacheStatus = CACHE_STALE;
         5113  +  pC->seekResult = 0;
  5112   5114     break;
  5113   5115   }
  5114   5116   
  5115   5117   /* Opcode: Seek P1 * P3 P4 *
  5116   5118   ** Synopsis: Move P3 to P1.rowid
  5117   5119   **
  5118   5120   ** P1 is an open index cursor and P3 is a cursor on the corresponding