SQLite

Check-in [83a844357e]
Login

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

Overview
Comment:Always provide the BTREE_BULKLOAD hint, even when SQLITE_ENABLE_CURSOR_HINTS is not defined, as that hint gives a 4% performance increase.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: 83a844357e132683ab3d88eee0fe32a8beeb6662
User & Date: drh 2015-08-14 23:57:04.215
Context
2015-08-15
00:51
Change the display of the P4 operand of CursorHint in EXPLAIN output to function notation. (check-in: bee73d429c user: drh tags: cursor-hints)
2015-08-14
23:57
Always provide the BTREE_BULKLOAD hint, even when SQLITE_ENABLE_CURSOR_HINTS is not defined, as that hint gives a 4% performance increase. (check-in: 83a844357e user: drh tags: cursor-hints)
20:08
Updated testing of cursor-hints. Remove the test_cursorhint.c file and associated logic in the core and do tests based purely on the newly enhanced EXPLAIN output. (check-in: bf383e665a user: drh tags: cursor-hints)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/btree.c.
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852

853

854


855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
  }else{
    assert( pCur->skipNext==0 );
    *pDifferentRow = 0;
  }
  return SQLITE_OK;
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/*
** Provide hints to the cursor.  The particular hint given (and the type
** and number of the varargs parameters) is determined by the eHintType
** parameter.  See the definitions of the BTREE_HINT_* macros for details.
**
** Hints are not (currently) used by the native SQLite implementation.
** This mechanism is provided for systems that substitute an alternative
** storage engine.
*/
void sqlite3BtreeCursorHint(BtCursor *pCur, int eHintType, ...){
  va_list ap;
  va_start(ap, eHintType);

  switch( eHintType ){

    case BTREE_HINT_FLAGS: {


      pCur->hints = va_arg(ap, unsigned int);
      assert( pCur->hints==BTREE_SEEK_EQ || pCur->hints==BTREE_BULKLOAD
                  || pCur->hints==0 );
      break;
    }
    case BTREE_HINT_RANGE: {
      (void)va_arg(ap, Expr*);
      (void)va_arg(ap, struct Mem*);
      /* Range expression not used in this implementation */
      break;
    }
  }
  va_end(ap);
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

#ifndef SQLITE_OMIT_AUTOVACUUM
/*
** Given a page number of a regular database page, return the page
** number for the pointer-map page that contains the entry for the
** input page number.
**







<




<
<
<
<




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



<







833
834
835
836
837
838
839

840
841
842
843




844
845
846
847
848
849
850
851
852
853
854
855
856








857
858
859

860
861
862
863
864
865
866
  }else{
    assert( pCur->skipNext==0 );
    *pDifferentRow = 0;
  }
  return SQLITE_OK;
}


/*
** Provide hints to the cursor.  The particular hint given (and the type
** and number of the varargs parameters) is determined by the eHintType
** parameter.  See the definitions of the BTREE_HINT_* macros for details.




*/
void sqlite3BtreeCursorHint(BtCursor *pCur, int eHintType, ...){
  va_list ap;
  va_start(ap, eHintType);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
  if( eHintType==BTREE_HINT_FLAGS )
#else
  assert( eHintType==BTREE_HINT_FLAGS );
#endif
  {
    pCur->hints = va_arg(ap, unsigned int);
    assert( pCur->hints==BTREE_SEEK_EQ || pCur->hints==BTREE_BULKLOAD
                || pCur->hints==0 );








  }
  va_end(ap);
}


#ifndef SQLITE_OMIT_AUTOVACUUM
/*
** Given a page number of a regular database page, return the page
** number for the pointer-map page that contains the entry for the
** input page number.
**
Changes to src/btree.h.
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
#define BTREE_APPLICATION_ID      8
#define BTREE_DATA_VERSION        15  /* A virtual meta-value */

/*
** Kinds of hints that can be passed into the sqlite3BtreeCursorHint()
** interface.
**
** Note that cursor hints are not used by the canonical SQLite b-tree
** ayer.  Cursor hints are provided so that systems that substitute their
** on custom b-tree layer can have access to additional information that might
** boost performance.  Hints are only provided if SQLite is compiled with
** SQLITE_ENABLE_CURSOR_HINTS.  The hinting interface is undocumented
** (except for comments such as this) and is subject to change from one
** release of SQLite to the next.
**
** BTREE_HINT_FLAGS  (arguments: unsigned int)
**
**     Some combinatation of BTREE_BULKLOAD and BTREE_SEEK_EQ flags.  The
**     argument is a single unsigned integer which overwrites all prior
**     flag settings.
**
** BTREE_HINT_RANGE  (arguments: Expr*, Mem*)







<
<
<
<
<
<
<
<







149
150
151
152
153
154
155








156
157
158
159
160
161
162
#define BTREE_APPLICATION_ID      8
#define BTREE_DATA_VERSION        15  /* A virtual meta-value */

/*
** Kinds of hints that can be passed into the sqlite3BtreeCursorHint()
** interface.
**








** BTREE_HINT_FLAGS  (arguments: unsigned int)
**
**     Some combinatation of BTREE_BULKLOAD and BTREE_SEEK_EQ flags.  The
**     argument is a single unsigned integer which overwrites all prior
**     flag settings.
**
** BTREE_HINT_RANGE  (arguments: Expr*, Mem*)
180
181
182
183
184
185
186





187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219

220
221
222
223
224
225
226
**     any function calls nor subqueries nor references to b-trees other than
**     the cursor being hinted.
**
**     The design of the _RANGE hint is aid b-tree implementations that try
**     to prefetch content from remote machines - to provide those
**     implementations with limits on what needs to be prefetched and thereby
**     reduce network bandwidth.





*/
#define BTREE_HINT_FLAGS 1       /* Set flags indicating cursor usage */
#define BTREE_HINT_RANGE 2       /* Range constraints on queries */

/*
** Values that may be OR'd together to form the argument to the
** BTREE_HINT_FLAGS hint for sqlite3BtreeCursorHint():
**
** The BTREE_BULKLOAD flag is set on index cursors when the index is going
** to be filled with content that is already in sorted order.
**
** The BTREE_SEEK_EQ flag is set on cursors that will get OP_SeekGE or
** OP_SeekLE opcodes for a range search, but where the range of entries
** selected will all have the same key.  In other words, the cursor will
** be used only for equality key searches.
**
*/
#define BTREE_BULKLOAD 0x00000001  /* Used to full index in sorted order */
#define BTREE_SEEK_EQ  0x00000002  /* EQ seeks only - no range seeks */

#ifdef SQLITE_ENABLE_CURSOR_HINTS
void sqlite3BtreeCursorHint(BtCursor*, int, ...);
#endif

int sqlite3BtreeCursor(
  Btree*,                              /* BTree containing table to open */
  int iTable,                          /* Index of root page */
  int wrFlag,                          /* 1 for writing.  0 for read-only */
  struct KeyInfo*,                     /* First argument to compare function */
  BtCursor *pCursor                    /* Space to write cursor structure */
);
int sqlite3BtreeCursorSize(void);
void sqlite3BtreeCursorZero(BtCursor*);


int sqlite3BtreeCloseCursor(BtCursor*);
int sqlite3BtreeMovetoUnpacked(
  BtCursor*,
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,







>
>
>
>
>




















<
<
<
<









>







172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203




204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
**     any function calls nor subqueries nor references to b-trees other than
**     the cursor being hinted.
**
**     The design of the _RANGE hint is aid b-tree implementations that try
**     to prefetch content from remote machines - to provide those
**     implementations with limits on what needs to be prefetched and thereby
**     reduce network bandwidth.
**
** Note that BTREE_HINT_FLAGS with BTREE_BULKLOAD is the only hint used by
** standard SQLite.  The other hints are provided for extentions that use
** the SQLite parser and code generator but substitute their own storage
** engine.
*/
#define BTREE_HINT_FLAGS 1       /* Set flags indicating cursor usage */
#define BTREE_HINT_RANGE 2       /* Range constraints on queries */

/*
** Values that may be OR'd together to form the argument to the
** BTREE_HINT_FLAGS hint for sqlite3BtreeCursorHint():
**
** The BTREE_BULKLOAD flag is set on index cursors when the index is going
** to be filled with content that is already in sorted order.
**
** The BTREE_SEEK_EQ flag is set on cursors that will get OP_SeekGE or
** OP_SeekLE opcodes for a range search, but where the range of entries
** selected will all have the same key.  In other words, the cursor will
** be used only for equality key searches.
**
*/
#define BTREE_BULKLOAD 0x00000001  /* Used to full index in sorted order */
#define BTREE_SEEK_EQ  0x00000002  /* EQ seeks only - no range seeks */





int sqlite3BtreeCursor(
  Btree*,                              /* BTree containing table to open */
  int iTable,                          /* Index of root page */
  int wrFlag,                          /* 1 for writing.  0 for read-only */
  struct KeyInfo*,                     /* First argument to compare function */
  BtCursor *pCursor                    /* Space to write cursor structure */
);
int sqlite3BtreeCursorSize(void);
void sqlite3BtreeCursorZero(BtCursor*);
void sqlite3BtreeCursorHint(BtCursor*, int, ...);

int sqlite3BtreeCloseCursor(BtCursor*);
int sqlite3BtreeMovetoUnpacked(
  BtCursor*,
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,
Changes to src/vdbe.c.
3380
3381
3382
3383
3384
3385
3386

3387


3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
  ** and report database corruption if they were not, but this check has
  ** since moved into the btree layer.  */  
  pCur->isTable = pOp->p4type!=P4_KEYINFO;

open_cursor_set_hints:
  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
  assert( OPFLAG_SEEKEQ==BTREE_SEEK_EQ );

#ifdef SQLITE_ENABLE_CURSOR_HINTS


  sqlite3BtreeCursorHint(pCur->pCursor, BTREE_HINT_FLAGS,
                         (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
#endif
  break;
}

/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.







>
|
>
>


<







3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392

3393
3394
3395
3396
3397
3398
3399
  ** and report database corruption if they were not, but this check has
  ** since moved into the btree layer.  */  
  pCur->isTable = pOp->p4type!=P4_KEYINFO;

open_cursor_set_hints:
  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
  assert( OPFLAG_SEEKEQ==BTREE_SEEK_EQ );
  testcase( pOp->p5 & OPFLAG_BULKCSR );
#ifdef SQLITE_ENABLE_CURSOR_HINT
  testcase( pOp->p2 & OPFLAG_SEEKEQ );
#endif
  sqlite3BtreeCursorHint(pCur->pCursor, BTREE_HINT_FLAGS,
                         (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));

  break;
}

/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.