SQLite

Check-in [134e65c07f]
Login

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

Overview
Comment:Use OP_SorterColumns in aggregate queries. Remove OPFLAG_CLEARCACHE.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | OP_SorterColumns
Files: files | file ages | folders
SHA1: 134e65c07f88218a9f9f0b526695a7121df31e68
User & Date: drh 2014-10-13 12:30:17.749
Context
2014-10-13
12:30
Use OP_SorterColumns in aggregate queries. Remove OPFLAG_CLEARCACHE. (Closed-Leaf check-in: 134e65c07f user: drh tags: OP_SorterColumns)
01:23
Add the OP_SorterColumns opcode - an experiment in using a special case opcode to decode the Sorter output rather than the generic OP_Column. This might be faster. And with further work, it could eventually eliminate the need for OP_OpenPseudo. (check-in: b9c695e885 user: drh tags: OP_SorterColumns)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150

5151
5152
5153
5154
5155
5156
5157
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...
      */
      addrTopOfLoop = sqlite3VdbeCurrentAddr(v);
      sqlite3ExprCacheClear(pParse);
      if( groupBySort ){
        sqlite3VdbeAddOp2(v, OP_SorterData, sAggInfo.sortingIdx, sortOut);
      }
      for(j=0; j<pGroupBy->nExpr; j++){
        if( groupBySort ){
          sqlite3VdbeAddOp3(v, OP_Column, sortPTab, j, iBMem+j);
          if( j==0 ) sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE);
        }else{

          sAggInfo.directMode = 1;
          sqlite3ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j);
        }
      }
      sqlite3VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr,
                          (char*)sqlite3KeyInfoRef(pKeyInfo), P4_KEYINFO);
      j1 = sqlite3VdbeCurrentAddr(v);







<
|
<
|
|
|
>







5138
5139
5140
5141
5142
5143
5144

5145

5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...
      */
      addrTopOfLoop = sqlite3VdbeCurrentAddr(v);
      sqlite3ExprCacheClear(pParse);
      if( groupBySort ){
        sqlite3VdbeAddOp2(v, OP_SorterData, sAggInfo.sortingIdx, sortOut);

        j = pGroupBy->nExpr;

        sqlite3VdbeAddOp4Int(v, OP_SorterColumns, 0, j, iBMem, sortOut);
        sqlite3VdbeAddOp1(v, OP_NullRow, sortPTab);
      }else{
        for(j=0; j<pGroupBy->nExpr; j++){
          sAggInfo.directMode = 1;
          sqlite3ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j);
        }
      }
      sqlite3VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr,
                          (char*)sqlite3KeyInfoRef(pKeyInfo), P4_KEYINFO);
      j1 = sqlite3VdbeCurrentAddr(v);
Changes to src/sqliteInt.h.
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
*/
#define OPFLAG_NCHANGE       0x01    /* Set to update db->nChange */
#define OPFLAG_EPHEM         0x01    /* OP_Column: Ephemeral output is ok */
#define OPFLAG_LASTROWID     0x02    /* Set to update db->lastRowid */
#define OPFLAG_ISUPDATE      0x04    /* This OP_Insert is an sql UPDATE */
#define OPFLAG_APPEND        0x08    /* This is likely to be an append */
#define OPFLAG_USESEEKRESULT 0x10    /* Try to avoid a seek in BtreeInsert() */
#define OPFLAG_CLEARCACHE    0x20    /* Clear pseudo-table cache in OP_Column */
#define OPFLAG_LENGTHARG     0x40    /* OP_Column only used for length() */
#define OPFLAG_TYPEOFARG     0x80    /* OP_Column only used for typeof() */
#define OPFLAG_BULKCSR       0x01    /* OP_Open** used to open bulk cursor */
#define OPFLAG_P2ISREG       0x02    /* P2 to OP_Open** is a register number */
#define OPFLAG_PERMUTE       0x01    /* OP_Compare: use the permutation */

/*







<







2662
2663
2664
2665
2666
2667
2668

2669
2670
2671
2672
2673
2674
2675
*/
#define OPFLAG_NCHANGE       0x01    /* Set to update db->nChange */
#define OPFLAG_EPHEM         0x01    /* OP_Column: Ephemeral output is ok */
#define OPFLAG_LASTROWID     0x02    /* Set to update db->lastRowid */
#define OPFLAG_ISUPDATE      0x04    /* This OP_Insert is an sql UPDATE */
#define OPFLAG_APPEND        0x08    /* This is likely to be an append */
#define OPFLAG_USESEEKRESULT 0x10    /* Try to avoid a seek in BtreeInsert() */

#define OPFLAG_LENGTHARG     0x40    /* OP_Column only used for length() */
#define OPFLAG_TYPEOFARG     0x80    /* OP_Column only used for typeof() */
#define OPFLAG_BULKCSR       0x01    /* OP_Open** used to open bulk cursor */
#define OPFLAG_P2ISREG       0x02    /* P2 to OP_Open** is a register number */
#define OPFLAG_PERMUTE       0x01    /* OP_Compare: use the permutation */

/*
Changes to src/vdbe.c.
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
**
** The value extracted is stored in register P3.
**
** If the column contains fewer than P2 fields, then extract a NULL.  Or,
** if the P4 argument is a P4_MEM use the value of the P4 argument as
** the result.
**
** If the OPFLAG_CLEARCACHE bit is set on P5 and P1 is a pseudo-table cursor,
** then the cache of the cursor is reset prior to extracting the column.
** The first OP_Column against a pseudo-table after the value of the content
** register has changed should have this bit set.
**
** If the OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG bits are set on P5 when
** the result is guaranteed to only be used as the argument of a length()
** or typeof() function, respectively.  The loading of large blobs can be
** skipped for length() and all content loading can be skipped for typeof().
*/
case OP_Column: {
  i64 payloadSize64; /* Number of bytes in the record */







<
<
<
<
<







2235
2236
2237
2238
2239
2240
2241





2242
2243
2244
2245
2246
2247
2248
**
** The value extracted is stored in register P3.
**
** If the column contains fewer than P2 fields, then extract a NULL.  Or,
** if the P4 argument is a P4_MEM use the value of the P4 argument as
** the result.
**





** If the OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG bits are set on P5 when
** the result is guaranteed to only be used as the argument of a length()
** or typeof() function, respectively.  The loading of large blobs can be
** skipped for length() and all content loading can be skipped for typeof().
*/
case OP_Column: {
  i64 payloadSize64; /* Number of bytes in the record */
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
  pCrsr = pC->pCursor;
  assert( pCrsr!=0 || pC->pseudoTableReg>0 ); /* pCrsr NULL on PseudoTables */
  assert( pCrsr!=0 || pC->nullRow );          /* pC->nullRow on PseudoTables */

  /* If the cursor cache is stale, bring it up-to-date */
  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) goto abort_due_to_error;
  assert( (pOp->p5&OPFLAG_CLEARCACHE)==0 || aOp[pc-1].opcode==OP_SorterData );
  if( pC->cacheStatus!=p->cacheCtr || (pOp->p5&OPFLAG_CLEARCACHE)!=0 ){
    if( pC->nullRow ){
      if( pCrsr==0 ){
        assert( pC->pseudoTableReg>0 );
        pReg = &aMem[pC->pseudoTableReg];
        assert( pReg->flags & MEM_Blob );
        assert( memIsValid(pReg) );
        pC->payloadSize = pC->szRow = avail = pReg->n;







<
|







2279
2280
2281
2282
2283
2284
2285

2286
2287
2288
2289
2290
2291
2292
2293
  pCrsr = pC->pCursor;
  assert( pCrsr!=0 || pC->pseudoTableReg>0 ); /* pCrsr NULL on PseudoTables */
  assert( pCrsr!=0 || pC->nullRow );          /* pC->nullRow on PseudoTables */

  /* If the cursor cache is stale, bring it up-to-date */
  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) goto abort_due_to_error;

  if( pC->cacheStatus!=p->cacheCtr ){
    if( pC->nullRow ){
      if( pCrsr==0 ){
        assert( pC->pseudoTableReg>0 );
        pReg = &aMem[pC->pseudoTableReg];
        assert( pReg->flags & MEM_Blob );
        assert( memIsValid(pReg) );
        pC->payloadSize = pC->szRow = avail = pReg->n;
4300
4301
4302
4303
4304
4305
4306

4307
4308
4309
4310
4311
4312
4313
  }
  do{
    assert( d<=aMem[pOp->p4.i].n );
    idx += getVarint32(&aKey[idx], serial_type);
    if( VdbeMemDynamic(pDest) ) sqlite3VdbeMemSetNull(pDest);
    d += sqlite3VdbeSerialGet(&aKey[d], serial_type, pDest);
    pDest->enc = encoding;

    REGISTER_TRACE((int)(pDest-aMem), pDest);
    pDest++;
  }while( pDest<=pLast );
  break;
}

/* Opcode: RowData P1 P2 * * *







>







4294
4295
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
  }
  do{
    assert( d<=aMem[pOp->p4.i].n );
    idx += getVarint32(&aKey[idx], serial_type);
    if( VdbeMemDynamic(pDest) ) sqlite3VdbeMemSetNull(pDest);
    d += sqlite3VdbeSerialGet(&aKey[d], serial_type, pDest);
    pDest->enc = encoding;
    Deephemeralize(pDest);
    REGISTER_TRACE((int)(pDest-aMem), pDest);
    pDest++;
  }while( pDest<=pLast );
  break;
}

/* Opcode: RowData P1 P2 * * *