/ Check-in [58704ed1]
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:Remove an unnecessary column-cache flush operation. Add code to trace the column cache when compiled with SQLITE_DEBUG and using PRAGMA vdbe_addoptrace=ON.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 58704ed1f4cd78bb3b0c095ffd1626906a95a413
User & Date: drh 2013-12-18 18:44:43
References
2014-03-24
09:18 New ticket [a8a0d299] Incorrect column data type reported due to invalid VM register reuse. artifact: c40b3fef user: dan
Context
2013-12-19
02:23
Omit one or more pointless instructions that occur in between OP_NoConflict and OP_Halt. check-in: 61e2f357 user: drh tags: trunk
2013-12-18
18:44
Remove an unnecessary column-cache flush operation. Add code to trace the column cache when compiled with SQLITE_DEBUG and using PRAGMA vdbe_addoptrace=ON. check-in: 58704ed1 user: drh tags: trunk
16:27
Remove an unnecessary column-cache flush. Add another test case to the speedtest1.c program to accentuate the benefit of not flushing the cache at that point. Closed-Leaf check-in: 97fdfc6b user: drh tags: column-cache-debug
2013-12-17
16:32
Add evidence marks and additional test cases for the printf() SQL function. check-in: 93121d30 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

  2166   2166   /*
  2167   2167   ** Remember the current column cache context.  Any new entries added
  2168   2168   ** added to the column cache after this call are removed when the
  2169   2169   ** corresponding pop occurs.
  2170   2170   */
  2171   2171   void sqlite3ExprCachePush(Parse *pParse){
  2172   2172     pParse->iCacheLevel++;
         2173  +#ifdef SQLITE_DEBUG
         2174  +  if( pParse->db->flags & SQLITE_VdbeAddopTrace ){
         2175  +    printf("PUSH to %d\n", pParse->iCacheLevel);
         2176  +  }
         2177  +#endif
  2173   2178   }
  2174   2179   
  2175   2180   /*
  2176   2181   ** Remove from the column cache any entries that were added since the
  2177   2182   ** the previous N Push operations.  In other words, restore the cache
  2178   2183   ** to the state it was in N Pushes ago.
  2179   2184   */
  2180   2185   void sqlite3ExprCachePop(Parse *pParse, int N){
  2181   2186     int i;
  2182   2187     struct yColCache *p;
  2183   2188     assert( N>0 );
  2184   2189     assert( pParse->iCacheLevel>=N );
  2185   2190     pParse->iCacheLevel -= N;
         2191  +#ifdef SQLITE_DEBUG
         2192  +  if( pParse->db->flags & SQLITE_VdbeAddopTrace ){
         2193  +    printf("POP  to %d\n", pParse->iCacheLevel);
         2194  +  }
         2195  +#endif
  2186   2196     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2187   2197       if( p->iReg && p->iLevel>pParse->iCacheLevel ){
  2188   2198         cacheEntryClear(pParse, p);
  2189   2199         p->iReg = 0;
  2190   2200       }
  2191   2201     }
  2192   2202   }
................................................................................
  2273   2283   /*
  2274   2284   ** Clear all column cache entries.
  2275   2285   */
  2276   2286   void sqlite3ExprCacheClear(Parse *pParse){
  2277   2287     int i;
  2278   2288     struct yColCache *p;
  2279   2289   
         2290  +#if SQLITE_DEBUG
         2291  +  if( pParse->db->flags & SQLITE_VdbeAddopTrace ){
         2292  +    printf("CLEAR\n");
         2293  +  }
         2294  +#endif
  2280   2295     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2281   2296       if( p->iReg ){
  2282   2297         cacheEntryClear(pParse, p);
  2283   2298         p->iReg = 0;
  2284   2299       }
  2285   2300     }
  2286   2301   }

Changes to src/select.c.

   594    594       for(i=0; i<nColumn; i++){
   595    595         sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i);
   596    596       }
   597    597     }else if( eDest!=SRT_Exists ){
   598    598       /* If the destination is an EXISTS(...) expression, the actual
   599    599       ** values returned by the SELECT are not required.
   600    600       */
   601         -    sqlite3ExprCacheClear(pParse);
   602    601       sqlite3ExprCodeExprList(pParse, pEList, regResult,
   603    602                               (eDest==SRT_Output)?SQLITE_ECEL_DUP:0);
   604    603     }
   605    604     nColumn = nResultCol;
   606    605   
   607    606     /* If the DISTINCT keyword was present on the SELECT statement
   608    607     ** and this row has been seen before, then do not make this row
................................................................................
  1561   1560   /*
  1562   1561   ** Get a VDBE for the given parser context.  Create a new one if necessary.
  1563   1562   ** If an error occurs, return NULL and leave a message in pParse.
  1564   1563   */
  1565   1564   Vdbe *sqlite3GetVdbe(Parse *pParse){
  1566   1565     Vdbe *v = pParse->pVdbe;
  1567   1566     if( v==0 ){
  1568         -    v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
         1567  +    v = pParse->pVdbe = sqlite3VdbeCreate(pParse);
  1569   1568   #ifndef SQLITE_OMIT_TRACE
  1570   1569       if( v ){
  1571   1570         sqlite3VdbeAddOp0(v, OP_Trace);
  1572   1571       }
  1573   1572   #endif
  1574   1573     }
  1575   1574     return v;

Changes to src/vdbe.h.

   156    156   */
   157    157   #include "opcodes.h"
   158    158   
   159    159   /*
   160    160   ** Prototypes for the VDBE interface.  See comments on the implementation
   161    161   ** for a description of what each of these routines does.
   162    162   */
   163         -Vdbe *sqlite3VdbeCreate(sqlite3*);
          163  +Vdbe *sqlite3VdbeCreate(Parse*);
   164    164   int sqlite3VdbeAddOp0(Vdbe*,int);
   165    165   int sqlite3VdbeAddOp1(Vdbe*,int,int);
   166    166   int sqlite3VdbeAddOp2(Vdbe*,int,int,int);
   167    167   int sqlite3VdbeAddOp3(Vdbe*,int,int,int,int);
   168    168   int sqlite3VdbeAddOp4(Vdbe*,int,int,int,int,const char *zP4,int);
   169    169   int sqlite3VdbeAddOp4Int(Vdbe*,int,int,int,int,int);
   170    170   int sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp);

Changes to src/vdbeInt.h.

   308    308   struct Vdbe {
   309    309     sqlite3 *db;            /* The database connection that owns this statement */
   310    310     Op *aOp;                /* Space to hold the virtual machine's program */
   311    311     Mem *aMem;              /* The memory locations */
   312    312     Mem **apArg;            /* Arguments to currently executing user function */
   313    313     Mem *aColName;          /* Column names to return */
   314    314     Mem *pResultSet;        /* Pointer to an array of results */
          315  +#ifdef SQLITE_DEBUG
          316  +  Parse *pParse;          /* Parsing context used to create this Vdbe */
          317  +#endif
   315    318     int nMem;               /* Number of memory locations currently allocated */
   316    319     int nOp;                /* Number of instructions in the program */
   317    320     int nOpAlloc;           /* Number of slots allocated for aOp[] */
   318    321     int nLabel;             /* Number of labels used */
   319    322     int *aLabel;            /* Space to hold the labels */
   320    323     u16 nResColumn;         /* Number of columns in one row of the result set */
   321    324     int nCursor;            /* Number of slots in apCsr[] */

Changes to src/vdbeaux.c.

    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include "vdbeInt.h"
    19     19   
    20     20   /*
    21     21   ** Create a new virtual database engine.
    22     22   */
    23         -Vdbe *sqlite3VdbeCreate(sqlite3 *db){
           23  +Vdbe *sqlite3VdbeCreate(Parse *pParse){
           24  +  sqlite3 *db = pParse->db;
    24     25     Vdbe *p;
    25     26     p = sqlite3DbMallocZero(db, sizeof(Vdbe) );
    26     27     if( p==0 ) return 0;
    27     28     p->db = db;
    28     29     if( db->pVdbe ){
    29     30       db->pVdbe->pPrev = p;
    30     31     }
    31     32     p->pNext = db->pVdbe;
    32     33     p->pPrev = 0;
    33     34     db->pVdbe = p;
    34     35     p->magic = VDBE_MAGIC_INIT;
           36  +#if SQLITE_DEBUG
           37  +  p->pParse = pParse;
           38  +#endif
    35     39     return p;
    36     40   }
    37     41   
    38     42   /*
    39     43   ** Remember the SQL string for a prepared statement.
    40     44   */
    41     45   void sqlite3VdbeSetSql(Vdbe *p, const char *z, int n, int isPrepareV2){
................................................................................
   147    151     pOp->p4.p = 0;
   148    152     pOp->p4type = P4_NOTUSED;
   149    153   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
   150    154     pOp->zComment = 0;
   151    155   #endif
   152    156   #ifdef SQLITE_DEBUG
   153    157     if( p->db->flags & SQLITE_VdbeAddopTrace ){
          158  +    int jj, kk;
          159  +    Parse *pParse = p->pParse;
          160  +    for(jj=kk=0; jj<SQLITE_N_COLCACHE; jj++){
          161  +      struct yColCache *x = pParse->aColCache + jj;
          162  +      if( x->iLevel>pParse->iCacheLevel || x->iReg==0 ) continue;
          163  +      printf(" r[%d]={%d:%d}", x->iReg, x->iTable, x->iColumn);
          164  +      kk++;
          165  +    }
          166  +    if( kk ) printf("\n");
   154    167       sqlite3VdbePrintOp(0, i, &p->aOp[i]);
   155    168       test_addop_breakpoint();
   156    169     }
   157    170   #endif
   158    171   #ifdef VDBE_PROFILE
   159    172     pOp->cycles = 0;
   160    173     pOp->cnt = 0;

Changes to src/vdbeblob.c.

   251    251           zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault);
   252    252           rc = SQLITE_ERROR;
   253    253           sqlite3BtreeLeaveAll(db);
   254    254           goto blob_open_out;
   255    255         }
   256    256       }
   257    257   
   258         -    pBlob->pStmt = (sqlite3_stmt *)sqlite3VdbeCreate(db);
          258  +    pBlob->pStmt = (sqlite3_stmt *)sqlite3VdbeCreate(pParse);
   259    259       assert( pBlob->pStmt || db->mallocFailed );
   260    260       if( pBlob->pStmt ){
   261    261         Vdbe *v = (Vdbe *)pBlob->pStmt;
   262    262         int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
   263    263   
   264    264         sqlite3VdbeAddOpList(v, sizeof(openBlob)/sizeof(VdbeOpList), openBlob);
   265    265   

Changes to test/speedtest1.c.

   659    659   
   660    660   
   661    661     speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
   662    662     speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
   663    663     speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
   664    664     speedtest1_end_test();
   665    665   
          666  +  speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
          667  +  speedtest1_exec("DELETE FROM t2;");
          668  +  speedtest1_exec(
          669  +     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);"
          670  +     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);"
          671  +  );
          672  +  speedtest1_end_test();
          673  +
   666    674   
   667    675     n = sz/5;
   668         -  speedtest1_begin_test(300, "%d four-ways joins", n);
          676  +  speedtest1_begin_test(310, "%d four-ways joins", n);
   669    677     speedtest1_exec("BEGIN");
   670    678     speedtest1_prepare(
   671    679       "SELECT t1.c FROM t1, t2, t3, t4\n"
   672    680       " WHERE t4.a BETWEEN ?1 AND ?2\n"
   673    681       "   AND t3.a=t4.b\n"
   674    682       "   AND t2.a=t3.b\n"
   675    683       "   AND t1.c=t2.c"