/ Check-in [08e8f04d]
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:Whenever two or more OP_Column opcodes on the same cursor occur in succession, try to reordering them so that the one that extracts the right-most column is first, so that it will warm up the row cache for all those that follow. This gives a small performance boost.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | reorder-column-opcodes
Files: files | file ages | folders
SHA1: 08e8f04d1241db8f190686404874461371ffbeaa
User & Date: drh 2015-10-16 03:34:38
Context
2015-10-16
03:34
Whenever two or more OP_Column opcodes on the same cursor occur in succession, try to reordering them so that the one that extracts the right-most column is first, so that it will warm up the row cache for all those that follow. This gives a small performance boost. Leaf check-in: 08e8f04d user: drh tags: reorder-column-opcodes
2015-10-15
21:30
Performance optimization for the OP_Column opcode. check-in: 076be547 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

4495
4496
4497
4498
4499
4500
4501

4502

4503
4504
4505
4506
4507
4508
4509
      offset = (offset%ovflSize);
    }

    for( ; rc==SQLITE_OK && amt>0 && nextPage; iIdx++){

      /* If required, populate the overflow page-list cache. */
      if( (pCur->curFlags & BTCF_ValidOvfl)!=0 ){

        assert(!pCur->aOverflow[iIdx] || pCur->aOverflow[iIdx]==nextPage);

        pCur->aOverflow[iIdx] = nextPage;
      }

      if( offset>=ovflSize ){
        /* The only reason to read this page is to obtain the page
        ** number for the next page in the overflow chain. The page
        ** data is not required. So first try to lookup the overflow







>
|
>







4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
      offset = (offset%ovflSize);
    }

    for( ; rc==SQLITE_OK && amt>0 && nextPage; iIdx++){

      /* If required, populate the overflow page-list cache. */
      if( (pCur->curFlags & BTCF_ValidOvfl)!=0 ){
        assert( pCur->aOverflow[iIdx]==0
                || pCur->aOverflow[iIdx]==nextPage
                || CORRUPT_DB );
        pCur->aOverflow[iIdx] = nextPage;
      }

      if( offset>=ovflSize ){
        /* The only reason to read this page is to obtain the page
        ** number for the next page in the overflow chain. The page
        ** data is not required. So first try to lookup the overflow

Changes to src/sqliteInt.h.

1306
1307
1308
1309
1310
1311
1312

1313
1314
1315
1316
1317
1318
1319
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */

#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#ifndef SQLITE_OMIT_BUILTIN_TEST
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)







>







1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
#define SQLITE_RowCache       0x1000   /* Reorder OP_Column opcodes */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#ifndef SQLITE_OMIT_BUILTIN_TEST
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)

Changes to src/vdbe.c.

652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
        sqlite3_interrupt(db);
      }
    }
#endif

    /* Sanity checking on other operands */
#ifdef SQLITE_DEBUG
    assert( pOp->opflags==sqlite3OpcodeProperty[pOp->opcode] );
    if( (pOp->opflags & OPFLG_IN1)!=0 ){
      assert( pOp->p1>0 );
      assert( pOp->p1<=(p->nMem-p->nCursor) );
      assert( memIsValid(&aMem[pOp->p1]) );
      assert( sqlite3VdbeCheckMemInvariants(&aMem[pOp->p1]) );
      REGISTER_TRACE(pOp->p1, &aMem[pOp->p1]);
    }







|







652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
        sqlite3_interrupt(db);
      }
    }
#endif

    /* Sanity checking on other operands */
#ifdef SQLITE_DEBUG
    assert( (pOp->opflags&~OPFLG_JMPDEST)==sqlite3OpcodeProperty[pOp->opcode] );
    if( (pOp->opflags & OPFLG_IN1)!=0 ){
      assert( pOp->p1>0 );
      assert( pOp->p1<=(p->nMem-p->nCursor) );
      assert( memIsValid(&aMem[pOp->p1]) );
      assert( sqlite3VdbeCheckMemInvariants(&aMem[pOp->p1]) );
      REGISTER_TRACE(pOp->p1, &aMem[pOp->p1]);
    }

Changes to src/vdbeaux.c.

514
515
516
517
518
519
520






521
522
523
524
525
526
527
528
529


530
531

532




















533
534
535


















536
537
538
539
540
541
542
...
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
**
** (3) Update the Vdbe.readOnly and Vdbe.bIsReader flags to accurately
**     indicate what the prepared statement actually does.
**
** (4) Initialize the p4.xAdvance pointer on opcodes that use it.
**
** (5) Reclaim the memory allocated for storing labels.






*/
static void resolveP2Values(Vdbe *p, int *pMaxFuncArgs){
  int i;
  int nMaxArgs = *pMaxFuncArgs;
  Op *pOp;
  Parse *pParse = p->pParse;
  int *aLabel = pParse->aLabel;
  p->readOnly = 1;
  p->bIsReader = 0;


  for(pOp=p->aOp, i=p->nOp-1; i>=0; i--, pOp++){
    u8 opcode = pOp->opcode;






















    /* NOTE: Be sure to update mkopcodeh.awk when adding or removing
    ** cases from this switch! */
    switch( opcode ){


















      case OP_Transaction: {
        if( pOp->p2!=0 ) p->readOnly = 0;
        /* fall thru */
      }
      case OP_AutoCommit:
      case OP_Savepoint: {
        p->bIsReader = 1;
................................................................................
      case OP_Prev:
      case OP_PrevIfOpen: {
        pOp->p4.xAdvance = sqlite3BtreePrevious;
        pOp->p4type = P4_ADVANCE;
        break;
      }
    }

    pOp->opflags = sqlite3OpcodeProperty[opcode];
    if( (pOp->opflags & OPFLG_JUMP)!=0 && pOp->p2<0 ){
      assert( -1-pOp->p2<pParse->nLabel );
      pOp->p2 = aLabel[-1-pOp->p2];
    }
  }
  sqlite3DbFree(p->db, pParse->aLabel);
  pParse->aLabel = 0;
  pParse->nLabel = 0;
  *pMaxFuncArgs = nMaxArgs;
  assert( p->bIsReader!=0 || DbMaskAllZero(p->btreeMask) );
}

/*
** Return the address of the next instruction to be inserted.
*/







>
>
>
>
>
>


|






>
>

<
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
<
<
<
<
<
<
<
<
<







514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538

539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
...
621
622
623
624
625
626
627
628









629
630
631
632
633
634
635
**
** (3) Update the Vdbe.readOnly and Vdbe.bIsReader flags to accurately
**     indicate what the prepared statement actually does.
**
** (4) Initialize the p4.xAdvance pointer on opcodes that use it.
**
** (5) Reclaim the memory allocated for storing labels.
**
** (6) Set the Op.opflags field on all opcodes, and especially the
**     OPFLG_JMPDEST bit.
**
** (7) Reorders OP_Column opcodes against the same cursor so that the
**     last column is extracted first.  This is a performance optimization.
*/
static void resolveP2Values(Vdbe *p, int *pMaxFuncArgs){
  int i, m;
  int nMaxArgs = *pMaxFuncArgs;
  Op *pOp;
  Parse *pParse = p->pParse;
  int *aLabel = pParse->aLabel;
  p->readOnly = 1;
  p->bIsReader = 0;

  /* First cut at the Op.opcode flags */
  for(pOp=p->aOp, i=p->nOp-1; i>=0; i--, pOp++){

    pOp->opflags = sqlite3OpcodeProperty[pOp->opcode];
  }

  /* Resolve goto labels.  And add OPFLG_JMPDEST flags to the
  ** destinations of jumps. */
  for(pOp=p->aOp, i=p->nOp-1; i>=0; i--, pOp++){
    if( (pOp->opflags & OPFLG_JUMP)!=0 ){
      if( pOp->p2<0 ){
        assert( -1-pOp->p2<pParse->nLabel );
        pOp->p2 = aLabel[-1-pOp->p2];
      }
      if( pOp->p2>0 && pOp->p2<p->nOp ){
        p->aOp[pOp->p2].opflags |= OPFLG_JMPDEST;
      }
    }
  }
  sqlite3DbFree(p->db, pParse->aLabel);
  pParse->aLabel = 0;
  pParse->nLabel = 0;

  /* Third pass: fix up opcodes */
  for(pOp=p->aOp, i=m=0; i<p->nOp; i++, pOp++){
    /* NOTE: Be sure to update mkopcodeh.awk when adding or removing
    ** cases from this switch! */
    switch( pOp->opcode ){
      case OP_Column: {
        if( OptimizationEnabled(p->db, SQLITE_RowCache) && i>=m ){
          /* Reorder OP_Column opcodes so that the right-most column is
          ** extracted first.  This warms up the row cache and helps the
          ** subsequent OP_Column opcodes to run faster */
          int j;
          int b = 0;
          int p2 = pOp->p2;
          for(j=1; pOp[j].opcode==OP_Column; j++){
            if( pOp[j].p1!=pOp->p1 ) break;
            if( pOp[j].opflags & OPFLG_JMPDEST ) break;
            if( pOp[j].p2>p2 ){ b = j; p2 = pOp[j].p2; }
          }
          if( b ) SWAP(Op, pOp[0], pOp[b]);
          m = i+j;
        }
        break;
      }
      case OP_Transaction: {
        if( pOp->p2!=0 ) p->readOnly = 0;
        /* fall thru */
      }
      case OP_AutoCommit:
      case OP_Savepoint: {
        p->bIsReader = 1;
................................................................................
      case OP_Prev:
      case OP_PrevIfOpen: {
        pOp->p4.xAdvance = sqlite3BtreePrevious;
        pOp->p4type = P4_ADVANCE;
        break;
      }
    }
  }









  *pMaxFuncArgs = nMaxArgs;
  assert( p->bIsReader!=0 || DbMaskAllZero(p->btreeMask) );
}

/*
** Return the address of the next instruction to be inserted.
*/

Changes to tool/mkopcodeh.tcl.

122
123
124
125
126
127
128

129
130
131
132
133
134
135
...
207
208
209
210
211
212
213
214
215
216
217
218
219

220
221
222
223
224
225
226
227
228
229
230
set op(OP_Explain) -1
set order($nOp) OP_Explain
incr nOp

# The following are the opcodes that are processed by resolveP2Values()
#
set rp2v_ops {

  OP_Transaction
  OP_AutoCommit
  OP_Savepoint
  OP_Checkpoint
  OP_Vacuum
  OP_JournalMode
  OP_VUpdate
................................................................................
  set bv($i) [expr {$a0+$a1+$a2+$a3+$a4+$a5}]
}
puts ""
puts "/* Properties such as \"out2\" or \"jump\" that are specified in"
puts "** comments following the \"case\" for each opcode in the vdbe.c"
puts "** are encoded into bitvectors as follows:"
puts "*/"
puts "#define OPFLG_JUMP            0x0001  /* jump:  P2 holds jmp target */"
puts "#define OPFLG_IN1             0x0002  /* in1:   P1 is an input */"
puts "#define OPFLG_IN2             0x0004  /* in2:   P2 is an input */"
puts "#define OPFLG_IN3             0x0008  /* in3:   P3 is an input */"
puts "#define OPFLG_OUT2            0x0010  /* out2:  P2 is an output */"
puts "#define OPFLG_OUT3            0x0020  /* out3:  P3 is an output */"

puts "#define OPFLG_INITIALIZER \173\\"
for {set i 0} {$i<=$max} {incr i} {
  if {$i%8==0} {
    puts -nonewline [format "/* %3d */" $i]
  }
  puts -nonewline [format " 0x%02x," $bv($i)]
  if {$i%8==7} {
    puts "\\"
  }
}
puts "\175"







>







 







|
|
|
|
|
|
>











122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
...
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
set op(OP_Explain) -1
set order($nOp) OP_Explain
incr nOp

# The following are the opcodes that are processed by resolveP2Values()
#
set rp2v_ops {
  OP_Column
  OP_Transaction
  OP_AutoCommit
  OP_Savepoint
  OP_Checkpoint
  OP_Vacuum
  OP_JournalMode
  OP_VUpdate
................................................................................
  set bv($i) [expr {$a0+$a1+$a2+$a3+$a4+$a5}]
}
puts ""
puts "/* Properties such as \"out2\" or \"jump\" that are specified in"
puts "** comments following the \"case\" for each opcode in the vdbe.c"
puts "** are encoded into bitvectors as follows:"
puts "*/"
puts "#define OPFLG_JUMP            0x01  /* jump:  P2 holds jmp target */"
puts "#define OPFLG_IN1             0x02  /* in1:   P1 is an input */"
puts "#define OPFLG_IN2             0x04  /* in2:   P2 is an input */"
puts "#define OPFLG_IN3             0x08  /* in3:   P3 is an input */"
puts "#define OPFLG_OUT2            0x10  /* out2:  P2 is an output */"
puts "#define OPFLG_OUT3            0x20  /* out3:  P3 is an output */"
puts "#define OPFLG_JMPDEST         0x40  /* A jump destination */"
puts "#define OPFLG_INITIALIZER \173\\"
for {set i 0} {$i<=$max} {incr i} {
  if {$i%8==0} {
    puts -nonewline [format "/* %3d */" $i]
  }
  puts -nonewline [format " 0x%02x," $bv($i)]
  if {$i%8==7} {
    puts "\\"
  }
}
puts "\175"