/ Check-in [e7984ad2]
Login

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

Overview
Comment:Reduce the memory required by prepared statements that use the IN operator with a list on the right and an INTEGER PRIMARY KEY on the left.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:e7984ad2f7e4be30e7198ebd6609a7f6177513e2
User & Date: drh 2009-10-29 13:48:11
Context
2009-10-30
13:25
Avoid storing the result register for EXISTS and SELECT in any field of the Expr object - simply return the register number as the return value of the function that codes those expressions. check-in: 7253f8fa user: drh tags: trunk
2009-10-29
13:48
Reduce the memory required by prepared statements that use the IN operator with a list on the right and an INTEGER PRIMARY KEY on the left. check-in: e7984ad2 user: drh tags: trunk
2009-10-27
18:06
Minor updates and corrections to comments in btreeInt.h. No changes to code. check-in: ad949fa1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1598   1598   
  1599   1599           /* Loop through each expression in <exprlist>. */
  1600   1600           r1 = sqlite3GetTempReg(pParse);
  1601   1601           r2 = sqlite3GetTempReg(pParse);
  1602   1602           sqlite3VdbeAddOp2(v, OP_Null, 0, r2);
  1603   1603           for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
  1604   1604             Expr *pE2 = pItem->pExpr;
         1605  +          int iValToIns;
  1605   1606   
  1606   1607             /* If the expression is not constant then we will need to
  1607   1608             ** disable the test that was generated above that makes sure
  1608   1609             ** this code only executes once.  Because for a non-constant
  1609   1610             ** expression we need to rerun this code each time.
  1610   1611             */
  1611   1612             if( testAddr && !sqlite3ExprIsConstant(pE2) ){
  1612   1613               sqlite3VdbeChangeToNoop(v, testAddr-1, 2);
  1613   1614               testAddr = 0;
  1614   1615             }
  1615   1616   
  1616   1617             /* Evaluate the expression and insert it into the temp table */
  1617         -          r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
  1618         -          if( isRowid ){
  1619         -            sqlite3VdbeAddOp2(v, OP_MustBeInt, r3, sqlite3VdbeCurrentAddr(v)+2);
  1620         -            sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3);
         1618  +          if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
         1619  +            sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
  1621   1620             }else{
  1622         -            sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
  1623         -            sqlite3ExprCacheAffinityChange(pParse, r3, 1);
  1624         -            sqlite3VdbeAddOp2(v, OP_IdxInsert, pExpr->iTable, r2);
         1621  +            r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
         1622  +            if( isRowid ){
         1623  +              sqlite3VdbeAddOp2(v, OP_MustBeInt, r3,
         1624  +                                sqlite3VdbeCurrentAddr(v)+2);
         1625  +              sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3);
         1626  +            }else{
         1627  +              sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
         1628  +              sqlite3ExprCacheAffinityChange(pParse, r3, 1);
         1629  +              sqlite3VdbeAddOp2(v, OP_IdxInsert, pExpr->iTable, r2);
         1630  +            }
  1625   1631             }
  1626   1632           }
  1627   1633           sqlite3ReleaseTempReg(pParse, r1);
  1628   1634           sqlite3ReleaseTempReg(pParse, r2);
  1629   1635         }
  1630   1636         if( !isRowid ){
  1631   1637           sqlite3VdbeChangeP4(v, addr, (void *)&keyInfo, P4_KEYINFO);
................................................................................
  2871   2877   ** factoring out of a loop, then evaluate the expression
  2872   2878   ** into a register and convert the expression into a TK_REGISTER
  2873   2879   ** expression.
  2874   2880   */
  2875   2881   static int evalConstExpr(Walker *pWalker, Expr *pExpr){
  2876   2882     Parse *pParse = pWalker->pParse;
  2877   2883     switch( pExpr->op ){
         2884  +    case TK_IN:
  2878   2885       case TK_REGISTER: {
  2879   2886         return WRC_Prune;
  2880   2887       }
  2881   2888       case TK_FUNCTION:
  2882   2889       case TK_AGG_FUNCTION:
  2883   2890       case TK_CONST_FUNC: {
  2884   2891         /* The arguments to a function have a fixed destination.

Changes to src/vdbe.c.

  3754   3754   ** and register P2 becomes ephemeral.  If the cursor is changed, the
  3755   3755   ** value of register P2 will then change.  Make sure this does not
  3756   3756   ** cause any problems.)
  3757   3757   **
  3758   3758   ** This instruction only works on tables.  The equivalent instruction
  3759   3759   ** for indices is OP_IdxInsert.
  3760   3760   */
  3761         -case OP_Insert: {
         3761  +/* Opcode: InsertInt P1 P2 P3 P4 P5
         3762  +**
         3763  +** This works exactly like OP_Insert except that the key is the
         3764  +** integer value P3, not the value of the integer stored in register P3.
         3765  +*/
         3766  +case OP_Insert: 
         3767  +case OP_InsertInt: {
  3762   3768     Mem *pData;       /* MEM cell holding data for the record to be inserted */
  3763   3769     Mem *pKey;        /* MEM cell holding key  for the record */
  3764   3770     i64 iKey;         /* The integer ROWID or key for the record to be inserted */
  3765   3771     VdbeCursor *pC;   /* Cursor to table into which insert is written */
  3766   3772     int nZero;        /* Number of zero-bytes to append */
  3767   3773     int seekResult;   /* Result of prior seek or 0 if no USESEEKRESULT flag */
  3768   3774     const char *zDb;  /* database name - used by the update hook */
  3769   3775     const char *zTbl; /* Table name - used by the opdate hook */
  3770   3776     int op;           /* Opcode for update hook: SQLITE_UPDATE or SQLITE_INSERT */
  3771   3777   
  3772   3778     pData = &p->aMem[pOp->p2];
  3773         -  pKey = &p->aMem[pOp->p3];
  3774   3779     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  3775   3780     pC = p->apCsr[pOp->p1];
  3776   3781     assert( pC!=0 );
  3777   3782     assert( pC->pCursor!=0 );
  3778   3783     assert( pC->pseudoTableReg==0 );
  3779         -  assert( pKey->flags & MEM_Int );
  3780   3784     assert( pC->isTable );
  3781   3785     REGISTER_TRACE(pOp->p2, pData);
  3782         -  REGISTER_TRACE(pOp->p3, pKey);
  3783   3786   
  3784         -  iKey = pKey->u.i;
         3787  +  if( pOp->opcode==OP_Insert ){
         3788  +    pKey = &p->aMem[pOp->p3];
         3789  +    assert( pKey->flags & MEM_Int );
         3790  +    REGISTER_TRACE(pOp->p3, pKey);
         3791  +    iKey = pKey->u.i;
         3792  +  }else{
         3793  +    assert( pOp->opcode==OP_InsertInt );
         3794  +    iKey = pOp->p3;
         3795  +  }
         3796  +
  3785   3797     if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++;
  3786         -  if( pOp->p5 & OPFLAG_LASTROWID ) db->lastRowid = pKey->u.i;
         3798  +  if( pOp->p5 & OPFLAG_LASTROWID ) db->lastRowid = iKey;
  3787   3799     if( pData->flags & MEM_Null ){
  3788   3800       pData->z = 0;
  3789   3801       pData->n = 0;
  3790   3802     }else{
  3791   3803       assert( pData->flags & (MEM_Blob|MEM_Str) );
  3792   3804     }
  3793   3805     seekResult = ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0);