/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

991
992
993
994
995
996
997

998
999
1000
1001












1002
1003

1004
1005
1006
1007
1008
1009
1010
      sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
      sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
      sqlite3MayAbort(pParse);
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */

      sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
      );
      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);












      sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
                               regIns, aRegIdx, 0, appendFlag, isReplace==0);

    }
  }

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);







>




>
>
>
>
>
>
>
>
>
>
>
>

|
>







991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
      sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
      sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
      sqlite3MayAbort(pParse);
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      int bUseSeek;     /* True to use OPFLAG_SEEKRESULT */
      sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
      );
      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);

      /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
      ** constraints or (b) there are no triggers and this table is not a
      ** parent table in a foreign key constraint. It is safe to set the
      ** flag in the second case as if any REPLACE constraint is hit, an
      ** OP_Delete or OP_IdxDelete instruction will be executed on each 
      ** cursor that is disturbed. And these instructions both clear the
      ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
      ** functionality.  */
      bUseSeek = (isReplace==0 || (pTrigger==0 &&
          ((db->flags & SQLITE_ForeignKeys)==0 || sqlite3FkReferences(pTab)==0)
      ));
      sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
          regIns, aRegIdx, 0, appendFlag, bUseSeek
      );
    }
  }

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);

Changes to src/vdbe.c.

4550
4551
4552
4553
4554
4555
4556

4557
4558
4559
4560
4561
4562
4563
....
5105
5106
5107
5108
5109
5110
5111

5112
5113
5114
5115
5116
5117
5118
      nExtraDelete--;
    }
  }
#endif

  rc = sqlite3BtreeDelete(pC->uc.pCursor, pOp->p5);
  pC->cacheStatus = CACHE_STALE;

  if( rc ) goto abort_due_to_error;

  /* Invoke the update-hook if required. */
  if( opflags & OPFLAG_NCHANGE ){
    p->nChange++;
    if( db->xUpdateCallback && HasRowid(pTab) ){
      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, pTab->zName,
................................................................................
  if( rc ) goto abort_due_to_error;
  if( res==0 ){
    rc = sqlite3BtreeDelete(pCrsr, BTREE_AUXDELETE);
    if( rc ) goto abort_due_to_error;
  }
  assert( pC->deferredMoveto==0 );
  pC->cacheStatus = CACHE_STALE;

  break;
}

/* Opcode: Seek P1 * P3 P4 *
** Synopsis: Move P3 to P1.rowid
**
** P1 is an open index cursor and P3 is a cursor on the corresponding







>







 







>







4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
....
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
5119
5120
      nExtraDelete--;
    }
  }
#endif

  rc = sqlite3BtreeDelete(pC->uc.pCursor, pOp->p5);
  pC->cacheStatus = CACHE_STALE;
  pC->seekResult = 0;
  if( rc ) goto abort_due_to_error;

  /* Invoke the update-hook if required. */
  if( opflags & OPFLAG_NCHANGE ){
    p->nChange++;
    if( db->xUpdateCallback && HasRowid(pTab) ){
      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, pTab->zName,
................................................................................
  if( rc ) goto abort_due_to_error;
  if( res==0 ){
    rc = sqlite3BtreeDelete(pCrsr, BTREE_AUXDELETE);
    if( rc ) goto abort_due_to_error;
  }
  assert( pC->deferredMoveto==0 );
  pC->cacheStatus = CACHE_STALE;
  pC->seekResult = 0;
  break;
}

/* Opcode: Seek P1 * P3 P4 *
** Synopsis: Move P3 to P1.rowid
**
** P1 is an open index cursor and P3 is a cursor on the corresponding