/ Check-in [dc555b10]
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:Avoid redundant table b-tree cursor seeks in UPDATE statements that use the two-pass strategy.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dc555b1039c6930f6d15355c698ff917a85e8056
User & Date: dan 2017-01-28 19:45:34
Context
2017-01-28
19:53
Fix a couple comment typos. No changes to code. check-in: 2a2e7d86 user: mistachkin tags: trunk
19:45
Avoid redundant table b-tree cursor seeks in UPDATE statements that use the two-pass strategy. check-in: dc555b10 user: dan tags: trunk
15:26
Updates to the sqlite3_blob documentation. No changes to code. check-in: 426b440a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/session/session1.test.

   577    577     CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
   578    578     INSERT INTO t1 VALUES(1, 0.0);
   579    579   }
   580    580   do_iterator_test 11.2 * {
   581    581     UPDATE t1 SET b = 0.0;
   582    582   } {
   583    583   }
          584  +
          585  +reset_db
          586  +do_execsql_test 12.1 {
          587  +  CREATE TABLE t1(r INTEGER PRIMARY KEY, a, b);
          588  +  CREATE INDEX i1 ON t1(a);
          589  +  INSERT INTO t1 VALUES(1, 1, 1);
          590  +  INSERT INTO t1 VALUES(2, 1, 2);
          591  +  INSERT INTO t1 VALUES(3, 1, 3);
          592  +}
          593  +
          594  +do_iterator_test 12.2 * {
          595  +  UPDATE t1 SET b='one' WHERE a=1;
          596  +} {
          597  +  {UPDATE t1 0 X.. {i 1 {} {} i 1} {{} {} {} {} t one}}
          598  +  {UPDATE t1 0 X.. {i 2 {} {} i 2} {{} {} {} {} t one}}
          599  +  {UPDATE t1 0 X.. {i 3 {} {} i 3} {{} {} {} {} t one}}
          600  +}
          601  +
          602  +
   584    603   
   585    604   finish_test

Changes to src/sqliteInt.h.

  2588   2588                                         ** the OR optimization  */
  2589   2589   #define WHERE_GROUPBY          0x0040 /* pOrderBy is really a GROUP BY */
  2590   2590   #define WHERE_DISTINCTBY       0x0080 /* pOrderby is really a DISTINCT clause */
  2591   2591   #define WHERE_WANT_DISTINCT    0x0100 /* All output needs to be distinct */
  2592   2592   #define WHERE_SORTBYGROUP      0x0200 /* Support sqlite3WhereIsSorted() */
  2593   2593   #define WHERE_SEEK_TABLE       0x0400 /* Do not defer seeks on main table */
  2594   2594   #define WHERE_ORDERBY_LIMIT    0x0800 /* ORDERBY+LIMIT on the inner loop */
  2595         -                        /*     0x1000    not currently used */
         2595  +#define WHERE_SEEK_UNIQ_TABLE  0x1000 /* Do not defer seeks if unique */
  2596   2596                           /*     0x2000    not currently used */
  2597   2597   #define WHERE_USE_LIMIT        0x4000 /* Use the LIMIT in cost estimates */
  2598   2598                           /*     0x8000    not currently used */
  2599   2599   
  2600   2600   /* Allowed return values from sqlite3WhereIsDistinct()
  2601   2601   */
  2602   2602   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */

Changes to src/update.c.

   388    388     /* Begin the database scan. 
   389    389     **
   390    390     ** Do not consider a single-pass strategy for a multi-row update if
   391    391     ** there are any triggers or foreign keys to process, or rows may
   392    392     ** be deleted as a result of REPLACE conflict handling. Any of these
   393    393     ** things might disturb a cursor being used to scan through the table
   394    394     ** or index, causing a single-pass approach to malfunction.  */
   395         -  flags = WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE;
          395  +  flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE;
   396    396     if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
   397    397       flags |= WHERE_ONEPASS_MULTIROW;
   398    398     }
   399    399     pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
   400    400     if( pWInfo==0 ) goto update_cleanup;
   401    401   
   402    402     /* A one-pass strategy that might update more than one row may not

Changes to src/wherecode.c.

  1587   1587         testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
  1588   1588       }
  1589   1589   
  1590   1590       /* Seek the table cursor, if required */
  1591   1591       if( omitTable ){
  1592   1592         /* pIdx is a covering index.  No need to access the main table. */
  1593   1593       }else if( HasRowid(pIdx->pTable) ){
  1594         -      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0 ){
         1594  +      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE) || (
         1595  +          (pWInfo->wctrlFlags & WHERE_SEEK_UNIQ_TABLE) 
         1596  +       && (pWInfo->eOnePass==ONEPASS_SINGLE)
         1597  +      )){
  1595   1598           iRowidReg = ++pParse->nMem;
  1596   1599           sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  1597   1600           sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  1598   1601           sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
  1599   1602           VdbeCoverage(v);
  1600   1603         }else{
  1601   1604           codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);

Changes to test/update2.test.

   171    171       3 a 3 3
   172    172       4 a 14 4
   173    173       5 a 15 5
   174    174       6 a 16 6
   175    175       7 a 17 7
   176    176     }
   177    177   }
          178  +
          179  +#-------------------------------------------------------------------------
          180  +#
          181  +do_execsql_test 5.0 {
          182  +  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
          183  +  CREATE INDEX x1c ON x1(b, c);
          184  +  INSERT INTO x1 VALUES(1, 'a', 1);
          185  +  INSERT INTO x1 VALUES(2, 'a', 2);
          186  +  INSERT INTO x1 VALUES(3, 'a', 3);
          187  +}
          188  +
          189  +do_execsql_test 5.1.1 {
          190  +  UPDATE x1 SET c=c+1 WHERE b='a';
          191  +}
          192  +
          193  +do_execsql_test 5.1.2 {
          194  +  SELECT * FROM x1;
          195  +} {1 a 2 2 a 3 3 a 4}
          196  +
          197  +do_test 5.2 {
          198  +  catch { array unset A }
          199  +  db eval { EXPLAIN UPDATE x1 SET c=c+1 WHERE b='a' } { incr A($opcode) }
          200  +  set A(NotExists)
          201  +} {1}
          202  +
   178    203   
   179    204   finish_test
   180    205