/ Check-in [50e5f390]
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:Here is the beginnings of an effort to factor out the implementation of scalar subqueries to be implemented as subroutines at the end of the main body of bytecode, after the jump back to the OP_Init opcode. This is an incremental check-in that contains only preliminary changes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | factor-out-scalar-subselect
Files: files | file ages | folders
SHA3-256: 50e5f390fbab4189c9700a2c62c951ab9acbf0a03c491e88f3e3bcf19a2311cc
User & Date: drh 2018-12-26 15:04:43
Context
2018-12-28
21:40
Merge performance enhancements and other patches. Leaf check-in: 44e1b55a user: drh tags: factor-out-scalar-subselect
2018-12-26
15:04
Here is the beginnings of an effort to factor out the implementation of scalar subqueries to be implemented as subroutines at the end of the main body of bytecode, after the jump back to the OP_Init opcode. This is an incremental check-in that contains only preliminary changes. check-in: 50e5f390 user: drh tags: factor-out-scalar-subselect
2018-12-25
00:15
Increase the number of parameters to sqlite3WhereCodeOneLoopStart() to encourage the compiler to inline that routine within sqlite3WhereBegin(). This gives a performance improvement. check-in: 3c2ad0e9 user: drh tags: reuse-subqueries
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   164    164       /* The cookie mask contains one bit for each database file open.
   165    165       ** (Bit 0 is for main, bit 1 is for temp, and so forth.)  Bits are
   166    166       ** set for each database that is used.  Generate code to start a
   167    167       ** transaction on each used database and to verify the schema cookie
   168    168       ** on each used database.
   169    169       */
   170    170       if( db->mallocFailed==0 
   171         -     && (DbMaskNonZero(pParse->cookieMask) || pParse->pConstExpr)
          171  +     && (DbMaskNonZero(pParse->cookieMask) || pParse->pAuxExpr)
   172    172       ){
   173    173         int iDb, i;
   174    174         assert( sqlite3VdbeGetOp(v, 0)->opcode==OP_Init );
   175    175         sqlite3VdbeJumpHere(v, 0);
   176    176         for(iDb=0; iDb<db->nDb; iDb++){
   177    177           Schema *pSchema;
   178    178           if( DbMaskTest(pParse->cookieMask, iDb)==0 ) continue;
................................................................................
   203    203         */
   204    204         codeTableLocks(pParse);
   205    205   
   206    206         /* Initialize any AUTOINCREMENT data structures required.
   207    207         */
   208    208         sqlite3AutoincrementBegin(pParse);
   209    209   
   210         -      /* Code constant expressions that where factored out of inner loops */
   211         -      if( pParse->pConstExpr ){
   212         -        ExprList *pEL = pParse->pConstExpr;
          210  +      if( pParse->pAuxExpr ){
          211  +        ExprList *pEL = pParse->pAuxExpr;
          212  +        struct ExprList_item *pX;
   213    213           pParse->okConstFactor = 0;
   214         -        for(i=0; i<pEL->nExpr; i++){
   215         -          sqlite3ExprCode(pParse, pEL->a[i].pExpr, pEL->a[i].u.iConstExprReg);
          214  +        /* Code constant expressions that where factored out of inner loops */
          215  +        for(pX=pEL->a, i=pEL->nExpr; i>0; i--, pX++){
          216  +          if( pX->bAuxSubrtn ) continue;
          217  +          sqlite3ExprCode(pParse, pX->pExpr, pX->u.iConstExprReg);
          218  +        }
          219  +        sqlite3VdbeGoto(v, 1);  /* Jump back to the main code */
          220  +        /* Code subroutines factored out from main */
          221  +        for(pX=pEL->a, i=pEL->nExpr; i>0; i--, pX++){
          222  +          if( !pX->bAuxSubrtn ) continue;
          223  +          /* sqlite3ExprCode(pParse, pX->pExpr, pX->u.iConstExprReg); */
   216    224           }
          225  +      }else{
          226  +        /* Jump back to the beginning of main */
          227  +        sqlite3VdbeGoto(v, 1);
   217    228         }
   218         -
   219         -      /* Finally, jump back to the beginning of the executable code. */
   220         -      sqlite3VdbeGoto(v, 1);
   221    229       }
   222    230     }
   223    231   
   224    232   
   225    233     /* Get the VDBE program ready for execution
   226    234     */
   227    235     if( v && pParse->nErr==0 && !db->mallocFailed ){

Changes to src/expr.c.

  1946   1946   **   (1) the expression is constant, and
  1947   1947   **   (2) the expression does originate in the ON or USING clause
  1948   1948   **       of a LEFT JOIN, and
  1949   1949   **   (3) the expression does not contain any EP_FixedCol TK_COLUMN
  1950   1950   **       operands created by the constant propagation optimization.
  1951   1951   **
  1952   1952   ** When this routine returns true, it indicates that the expression
  1953         -** can be added to the pParse->pConstExpr list and evaluated once when
         1953  +** can be added to the pParse->pAuxExpr list and evaluated once when
  1954   1954   ** the prepared statement starts up.  See sqlite3ExprCodeAtInit().
  1955   1955   */
  1956   1956   int sqlite3ExprIsConstantNotJoin(Expr *p){
  1957   1957     return exprIsConst(p, 2, 0);
  1958   1958   }
  1959   1959   
  1960   1960   /*
................................................................................
  4134   4134   int sqlite3ExprCodeAtInit(
  4135   4135     Parse *pParse,    /* Parsing context */
  4136   4136     Expr *pExpr,      /* The expression to code when the VDBE initializes */
  4137   4137     int regDest       /* Store the value in this register */
  4138   4138   ){
  4139   4139     ExprList *p;
  4140   4140     assert( ConstFactorOk(pParse) );
  4141         -  p = pParse->pConstExpr;
         4141  +  p = pParse->pAuxExpr;
  4142   4142     if( regDest<0 && p ){
  4143   4143       struct ExprList_item *pItem;
  4144   4144       int i;
  4145   4145       for(pItem=p->a, i=p->nExpr; i>0; pItem++, i--){
  4146   4146         if( pItem->reusable && sqlite3ExprCompare(0,pItem->pExpr,pExpr,-1)==0 ){
  4147   4147           return pItem->u.iConstExprReg;
  4148   4148         }
................................................................................
  4152   4152     p = sqlite3ExprListAppend(pParse, p, pExpr);
  4153   4153     if( p ){
  4154   4154        struct ExprList_item *pItem = &p->a[p->nExpr-1];
  4155   4155        pItem->reusable = regDest<0;
  4156   4156        if( regDest<0 ) regDest = ++pParse->nMem;
  4157   4157        pItem->u.iConstExprReg = regDest;
  4158   4158     }
  4159         -  pParse->pConstExpr = p;
         4159  +  pParse->pAuxExpr = p;
  4160   4160     return regDest;
  4161   4161   }
  4162   4162   
  4163   4163   /*
  4164   4164   ** Generate code to evaluate an expression and store the results
  4165   4165   ** into a register.  Return the register number where the results
  4166   4166   ** are stored.
................................................................................
  4390   4390     compRight.pRight = pExpr->x.pList->a[1].pExpr;
  4391   4391     exprToRegister(&exprX, exprCodeVector(pParse, &exprX, &regFree1));
  4392   4392     if( xJump ){
  4393   4393       xJump(pParse, &exprAnd, dest, jumpIfNull);
  4394   4394     }else{
  4395   4395       /* Mark the expression is being from the ON or USING clause of a join
  4396   4396       ** so that the sqlite3ExprCodeTarget() routine will not attempt to move
  4397         -    ** it into the Parse.pConstExpr list.  We should use a new bit for this,
         4397  +    ** it into the Parse.pAuxExpr list.  We should use a new bit for this,
  4398   4398       ** for clarity, but we are out of bits in the Expr.flags field so we
  4399   4399       ** have to reuse the EP_FromJoin bit.  Bummer. */
  4400   4400       exprX.flags |= EP_FromJoin;
  4401   4401       sqlite3ExprCodeTarget(pParse, &exprAnd, dest);
  4402   4402     }
  4403   4403     sqlite3ReleaseTempReg(pParse, regFree1);
  4404   4404   

Changes to src/prepare.c.

   502    502   
   503    503   /*
   504    504   ** Free all memory allocations in the pParse object
   505    505   */
   506    506   void sqlite3ParserReset(Parse *pParse){
   507    507     sqlite3 *db = pParse->db;
   508    508     sqlite3DbFree(db, pParse->aLabel);
   509         -  sqlite3ExprListDelete(db, pParse->pConstExpr);
          509  +  sqlite3ExprListDelete(db, pParse->pAuxExpr);
   510    510     if( db ){
   511    511       assert( db->lookaside.bDisable >= pParse->disableLookaside );
   512    512       db->lookaside.bDisable -= pParse->disableLookaside;
   513    513     }
   514    514     pParse->disableLookaside = 0;
   515    515   }
   516    516   

Changes to src/sqliteInt.h.

  2583   2583     struct ExprList_item { /* For each expression in the list */
  2584   2584       Expr *pExpr;            /* The parse tree for this expression */
  2585   2585       char *zName;            /* Token associated with this expression */
  2586   2586       char *zSpan;            /* Original text of the expression */
  2587   2587       u8 sortOrder;           /* 1 for DESC or 0 for ASC */
  2588   2588       unsigned done :1;       /* A flag to indicate when processing is finished */
  2589   2589       unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
  2590         -    unsigned reusable :1;   /* Constant expression is reusable */
         2590  +    unsigned reusable   :1; /* Constant expression is reusable */
  2591   2591       unsigned bSorterRef :1; /* Defer evaluation until after sorting */
         2592  +    unsigned bAuxSubrtn :1; /* Member of pAuxExpr to be coded as a subroutine */
  2592   2593       union {
  2593   2594         struct {
  2594   2595           u16 iOrderByCol;      /* For ORDER BY, column number in result set */
  2595   2596           u16 iAlias;           /* Index into Parse.aAlias[] for zName */
  2596   2597         } x;
  2597   2598         int iConstExprReg;      /* Register in which Expr value is cached */
  2598   2599       } u;
................................................................................
  3072   3073     int nMem;            /* Number of memory cells used so far */
  3073   3074     int nOpAlloc;        /* Number of slots allocated for Vdbe.aOp[] */
  3074   3075     int szOpAlloc;       /* Bytes of memory space allocated for Vdbe.aOp[] */
  3075   3076     int iSelfTab;        /* Table associated with an index on expr, or negative
  3076   3077                          ** of the base register during check-constraint eval */
  3077   3078     int nLabel;          /* Number of labels used */
  3078   3079     int *aLabel;         /* Space to hold the labels */
  3079         -  ExprList *pConstExpr;/* Constant expressions */
         3080  +  ExprList *pAuxExpr;  /* Exprs coded at initialization or as subroutines */
  3080   3081     Token constraintName;/* Name of the constraint currently being parsed */
  3081   3082     yDbMask writeMask;   /* Start a write transaction on these databases */
  3082   3083     yDbMask cookieMask;  /* Bitmask of schema verified databases */
  3083   3084     int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  3084   3085     int regRoot;         /* Register holding root page number for new objects */
  3085   3086     int nMaxArg;         /* Max args passed to user function by sub-program */
  3086   3087     int nSelect;         /* Number of SELECT stmts. Counter for Select.selId */