/ Check-in [915f6f1c]
Login

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

Overview
Comment:Improvements to "NOT IN (SELECT ...)" processing. Only test for NULL values on the RHS on the first iteration, then remember the result. There has been logic to do this for year, but it didn't work right and ended up repeating the NULL test on every iteration. This inefficiency was found using the VDBE coverage testing tools.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 915f6f1c7aab54583729e60bdc1565f25ecc6f74
User & Date: drh 2014-02-18 01:07:38
Context
2014-02-18
03:07
Add VdbeCoverage() and VdbeCoverageIf() macros for improved VDBE coverage testing. check-in: b92d31a9 user: drh tags: trunk
01:07
Improvements to "NOT IN (SELECT ...)" processing. Only test for NULL values on the RHS on the first iteration, then remember the result. There has been logic to do this for year, but it didn't work right and ended up repeating the NULL test on every iteration. This inefficiency was found using the VDBE coverage testing tools. check-in: 915f6f1c user: drh tags: trunk
2014-02-17
23:52
Merge in performance enhancements for INSERT operations, especially INSERTs on tables that have no affinity columns or that have many indices or INSERTs with content coming from a SELECT. Add the SQLITE_TESTCTRL_VDBE_COVERAGE test control and the SQLITE_VDBE_COVERAGE compile-time option used for measure coverage of branches in VDBE programs. check-in: a7268769 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1626   1626             int iAddr = sqlite3CodeOnce(pParse);
  1627   1627             sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
  1628   1628             sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1629   1629             VdbeComment((v, "%s", pIdx->zName));
  1630   1630             assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
  1631   1631             eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];
  1632   1632   
  1633         -          sqlite3VdbeJumpHere(v, iAddr);
  1634   1633             if( prNotFound && !pTab->aCol[iCol].notNull ){
  1635   1634               *prNotFound = ++pParse->nMem;
  1636   1635               sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
  1637   1636             }
         1637  +          sqlite3VdbeJumpHere(v, iAddr);
  1638   1638           }
  1639   1639         }
  1640   1640       }
  1641   1641     }
  1642   1642   
  1643   1643     if( eType==0 ){
  1644   1644       /* Could not found an existing table or index to use as the RHS b-tree.
................................................................................
  2000   2000         sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1);
  2001   2001         VdbeCoverage(v);
  2002   2002       }else{
  2003   2003         /* In this branch, the RHS of the IN might contain a NULL and
  2004   2004         ** the presence of a NULL on the RHS makes a difference in the
  2005   2005         ** outcome.
  2006   2006         */
  2007         -      int j1, j2, j3;
         2007  +      int j1, j2;
  2008   2008   
  2009   2009         /* First check to see if the LHS is contained in the RHS.  If so,
  2010   2010         ** then the presence of NULLs in the RHS does not matter, so jump
  2011   2011         ** over all of the code that follows.
  2012   2012         */
  2013   2013         j1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1);
  2014   2014         VdbeCoverage(v);
................................................................................
  2015   2015   
  2016   2016         /* Here we begin generating code that runs if the LHS is not
  2017   2017         ** contained within the RHS.  Generate additional code that
  2018   2018         ** tests the RHS for NULLs.  If the RHS contains a NULL then
  2019   2019         ** jump to destIfNull.  If there are no NULLs in the RHS then
  2020   2020         ** jump to destIfFalse.
  2021   2021         */
  2022         -      j2 = sqlite3VdbeAddOp1(v, OP_NotNull, rRhsHasNull); VdbeCoverage(v);
  2023         -      j3 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, rRhsHasNull, 1);
         2022  +      sqlite3VdbeAddOp2(v, OP_If, rRhsHasNull, destIfNull); VdbeCoverage(v);
         2023  +      sqlite3VdbeAddOp2(v, OP_IfNot, rRhsHasNull, destIfFalse); VdbeCoverage(v);
         2024  +      j2 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, rRhsHasNull, 1);
  2024   2025         VdbeCoverage(v);
  2025         -      sqlite3VdbeAddOp2(v, OP_Integer, -1, rRhsHasNull);
  2026         -      sqlite3VdbeJumpHere(v, j3);
  2027         -      sqlite3VdbeAddOp2(v, OP_AddImm, rRhsHasNull, 1);
         2026  +      sqlite3VdbeAddOp2(v, OP_Integer, 0, rRhsHasNull);
         2027  +      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);
  2028   2028         sqlite3VdbeJumpHere(v, j2);
  2029         -
  2030         -      /* Jump to the appropriate target depending on whether or not
  2031         -      ** the RHS contains a NULL
  2032         -      */
  2033         -      sqlite3VdbeAddOp2(v, OP_If, rRhsHasNull, destIfNull); VdbeCoverage(v);
  2034         -      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);
         2029  +      sqlite3VdbeAddOp2(v, OP_Integer, 1, rRhsHasNull);
         2030  +      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull);
  2035   2031   
  2036   2032         /* The OP_Found at the top of this branch jumps here when true, 
  2037   2033         ** causing the overall IN expression evaluation to fall through.
  2038   2034         */
  2039   2035         sqlite3VdbeJumpHere(v, j1);
  2040   2036       }
  2041   2037     }