/ Check-in [85dddf2b]
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:Very small performance improvements and size reductions in sqlite3VdbeExec() and blobSeekToRow().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 85dddf2b453b8afaf1f485b96084d31e22f97dda
User & Date: drh 2017-01-31 19:10:42
Context
2017-01-31
21:22
R-TREE optimization: unwrap the coordinate decode loop in rtreeCallbackConstraint(). check-in: 0bf7b518 user: drh tags: trunk
19:10
Very small performance improvements and size reductions in sqlite3VdbeExec() and blobSeekToRow(). check-in: 85dddf2b user: drh tags: trunk
19:02
Simplifications to blobSeekToRow(). Closed-Leaf check-in: 495ea824 user: drh tags: micro-optimizations
15:29
Add the "stat" command to kvtest.c. Also add the --variance option to the "init" command. Add the tool/kvtest-speed.sh script used for doing performance testing on key/value access patterns. check-in: b63deed6 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
...
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
** Execute as much of a VDBE program as we can.
** This is the core of sqlite3_step().  
*/
int sqlite3VdbeExec(
  Vdbe *p                    /* The VDBE */
){
  Op *aOp = p->aOp;          /* Copy of p->aOp */
  Op *pOp = aOp;             /* Current operation */
#if defined(SQLITE_DEBUG) || defined(VDBE_PROFILE)
  Op *pOrigOp;               /* Value of pOp at the top of the loop */
#endif
#ifdef SQLITE_DEBUG
  int nExtraDelete = 0;      /* Verifies FORDELETE and AUXDELETE flags */
#endif
  int rc = SQLITE_OK;        /* Value to return */
................................................................................
  if( p->rc==SQLITE_NOMEM ){
    /* This happens if a malloc() inside a call to sqlite3_column_text() or
    ** sqlite3_column_text16() failed.  */
    goto no_mem;
  }
  assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
  assert( p->bIsReader || p->readOnly!=0 );
  p->rc = SQLITE_OK;
  p->iCurrentTime = 0;
  assert( p->explain==0 );
  p->pResultSet = 0;
  db->busyHandler.nBusy = 0;
  if( db->u1.isInterrupted ) goto abort_due_to_interrupt;
  sqlite3VdbeIOTraceSql(p);
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK







|







 







<







558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
...
593
594
595
596
597
598
599

600
601
602
603
604
605
606
** Execute as much of a VDBE program as we can.
** This is the core of sqlite3_step().  
*/
int sqlite3VdbeExec(
  Vdbe *p                    /* The VDBE */
){
  Op *aOp = p->aOp;          /* Copy of p->aOp */
  Op *pOp;                   /* Current operation */
#if defined(SQLITE_DEBUG) || defined(VDBE_PROFILE)
  Op *pOrigOp;               /* Value of pOp at the top of the loop */
#endif
#ifdef SQLITE_DEBUG
  int nExtraDelete = 0;      /* Verifies FORDELETE and AUXDELETE flags */
#endif
  int rc = SQLITE_OK;        /* Value to return */
................................................................................
  if( p->rc==SQLITE_NOMEM ){
    /* This happens if a malloc() inside a call to sqlite3_column_text() or
    ** sqlite3_column_text16() failed.  */
    goto no_mem;
  }
  assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
  assert( p->bIsReader || p->readOnly!=0 );

  p->iCurrentTime = 0;
  assert( p->explain==0 );
  p->pResultSet = 0;
  db->busyHandler.nBusy = 0;
  if( db->u1.isInterrupted ) goto abort_due_to_interrupt;
  sqlite3VdbeIOTraceSql(p);
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK

Changes to src/vdbeblob.c.

51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
** calls to sqlite3_blob_read(), blob_write() or blob_reopen() will 
** immediately return SQLITE_ABORT.
*/
static int blobSeekToRow(Incrblob *p, sqlite3_int64 iRow, char **pzErr){
  int rc;                         /* Error code */
  char *zErr = 0;                 /* Error message */
  Vdbe *v = (Vdbe *)p->pStmt;
  sqlite3 *db = v->db;

  /* Set the value of register r[1] in the SQL statement to integer iRow. 
  ** This is done directly as a performance optimization
  */
  v->aMem[1].flags = MEM_Int;
  v->aMem[1].u.i = iRow;

  /* If the statement has been run before (and is paused at the OP_ResultRow)
  ** then back it up to the point where it does the OP_SeekRowid.  This could
  ** have been down with an extra OP_Goto, but simply setting the program
  ** counter is faster. */
  if( v->pc>3 ){
    v->pc = 3;
    db->nVdbeExec++;
    rc = sqlite3VdbeExec((Vdbe*)p->pStmt);
    db->nVdbeExec--;
  }else{
    rc = sqlite3_step(p->pStmt);
  }
  if( rc==SQLITE_ROW ){
    VdbeCursor *pC = v->apCsr[0];
    u32 type = pC->nHdrParsed>p->iCol ? pC->aType[p->iCol] : 0;
    testcase( pC->nHdrParsed==p->iCol );







<













<
|
<







51
52
53
54
55
56
57

58
59
60
61
62
63
64
65
66
67
68
69
70

71

72
73
74
75
76
77
78
** calls to sqlite3_blob_read(), blob_write() or blob_reopen() will 
** immediately return SQLITE_ABORT.
*/
static int blobSeekToRow(Incrblob *p, sqlite3_int64 iRow, char **pzErr){
  int rc;                         /* Error code */
  char *zErr = 0;                 /* Error message */
  Vdbe *v = (Vdbe *)p->pStmt;


  /* Set the value of register r[1] in the SQL statement to integer iRow. 
  ** This is done directly as a performance optimization
  */
  v->aMem[1].flags = MEM_Int;
  v->aMem[1].u.i = iRow;

  /* If the statement has been run before (and is paused at the OP_ResultRow)
  ** then back it up to the point where it does the OP_SeekRowid.  This could
  ** have been down with an extra OP_Goto, but simply setting the program
  ** counter is faster. */
  if( v->pc>3 ){
    v->pc = 3;

    rc = sqlite3VdbeExec(v);

  }else{
    rc = sqlite3_step(p->pStmt);
  }
  if( rc==SQLITE_ROW ){
    VdbeCursor *pC = v->apCsr[0];
    u32 type = pC->nHdrParsed>p->iCol ? pC->aType[p->iCol] : 0;
    testcase( pC->nHdrParsed==p->iCol );

Changes to tool/kvtest-speed.sh.

17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
OPTS="-DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DIRECT_OVERFLOW_READ -DUSE_PREAD"
KVARGS="--count 100K --stats"
gcc -g -Os -I. $OPTS $* kvtest.c sqlite3.c -o kvtest

# First run using SQL
rm cachegrind.out.[1-9][0-9]*
valgrind --tool=cachegrind ./kvtest run kvtest.db $KVARGS 2>&1 | tee summary-kvtest-$NAME.txt
mv cachegrind.out.[1-9][0-9]* cachegrind.out.$NAME
cg_anno.tcl cachegrind.out.$NAME >cout-kvtest-sql-$NAME.txt

# Second run using the sqlite3_blob object
rm cachegrind.out.[1-9][0-9]*
valgrind --tool=cachegrind ./kvtest run kvtest.db $KVARGS --blob-api 2>&1 | tee -a summary-kvtest-$NAME.txt
mv cachegrind.out.[1-9][0-9]* cachegrind.out.$NAME
cg_anno.tcl cachegrind.out.$NAME >cout-kvtest-$NAME.txt

# Diff the sqlite3_blob API analysis for non-trunk runs.
if test "$NAME" != "trunk"; then
  fossil test-diff --tk cout-kvtest-trunk.txt cout-kvtest-$NAME.txt &
fi







|
|


<








17
18
19
20
21
22
23
24
25
26
27

28
29
30
31
32
33
34
35
OPTS="-DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DIRECT_OVERFLOW_READ -DUSE_PREAD"
KVARGS="--count 100K --stats"
gcc -g -Os -I. $OPTS $* kvtest.c sqlite3.c -o kvtest

# First run using SQL
rm cachegrind.out.[1-9][0-9]*
valgrind --tool=cachegrind ./kvtest run kvtest.db $KVARGS 2>&1 | tee summary-kvtest-$NAME.txt
mv cachegrind.out.[1-9][0-9]* cachegrind.out.sql-$NAME
cg_anno.tcl cachegrind.out.sql-$NAME >cout-kvtest-sql-$NAME.txt

# Second run using the sqlite3_blob object

valgrind --tool=cachegrind ./kvtest run kvtest.db $KVARGS --blob-api 2>&1 | tee -a summary-kvtest-$NAME.txt
mv cachegrind.out.[1-9][0-9]* cachegrind.out.$NAME
cg_anno.tcl cachegrind.out.$NAME >cout-kvtest-$NAME.txt

# Diff the sqlite3_blob API analysis for non-trunk runs.
if test "$NAME" != "trunk"; then
  fossil test-diff --tk cout-kvtest-trunk.txt cout-kvtest-$NAME.txt &
fi