/ Check-in [f687977a]
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:Improve the speed of OP_Column through better caching. (CVS 1577)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f687977a28eda5ce0aa1cba2fdfb0152443032bc
User & Date: drh 2004-06-12 18:12:16
Context
2004-06-12
20:12
Speed up in the handling of VDBE cursors. (CVS 1578) check-in: e42316f5 user: drh tags: trunk
18:12
Improve the speed of OP_Column through better caching. (CVS 1577) check-in: f687977a user: drh tags: trunk
14:11
Fix typo in the locking document. (CVS 1576) check-in: 99a7bd83 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/os_common.h.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


/*
** Macros for performance tracing.  Normally turned off.  Only works
** on i486 hardware.
*/
int sqlite3_os_trace = 0;
#if 1
static int last_page = 0;
__inline__ unsigned long long int hwtime(void){
  unsigned long long int x;
  __asm__("rdtsc\n\t"
          "mov %%edx, %%ecx\n\t"
          :"=A" (x));
  return x;







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


/*
** Macros for performance tracing.  Normally turned off.  Only works
** on i486 hardware.
*/
int sqlite3_os_trace = 0;
#ifdef SQLITE_DEBUG
static int last_page = 0;
__inline__ unsigned long long int hwtime(void){
  unsigned long long int x;
  __asm__("rdtsc\n\t"
          "mov %%edx, %%ecx\n\t"
          :"=A" (x));
  return x;

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
....
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921






1922
1923
1924
1925
1926
1927
1928

1929
1930
1931
1932
1933
1934
1935
1936
1937
1938

1939
1940
1941
1942
1943
1944
1945

1946
1947
1948
1949
1950
1951
1952
1953
1954

1955
1956
1957
1958
1959
1960
1961
....
1992
1993
1994
1995
1996
1997
1998










1999
2000
2001





2002

2003
2004
2005
2006
2007
2008
2009
2010
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.368 2004/06/12 09:25:25 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
*/
case OP_IdxColumn:
case OP_Column: {
  int payloadSize;   /* Number of bytes in the record */
  int p1 = pOp->p1;  /* P1 value of the opcode */
  int p2 = pOp->p2;  /* column number to retrieve */
  Cursor *pC = 0;    /* The VDBE cursor */
  char *zRec;        /* Pointer to record-data from stack or pseudo-table. */
  BtCursor *pCrsr;   /* The BTree cursor */
  u32 *aType;        /* aType[i] holds the numeric type of the i-th column */
  u32 *aOffset;      /* aOffset[i] is offset to start of data for i-th column */
  u32 nField;        /* number of fields in the record */
  u32 szHdr;         /* Number of bytes in the record header */
  int len;           /* The length of the serialized data for the column */
  int offset = 0;    /* Offset into the data */
................................................................................
  char *zData;       /* Part of the record being decoded */
  Mem sMem;          /* For storing the record being decoded */

  sMem.flags = 0;
  assert( p1<p->nCursor );
  pTos++;

  /* This block sets the variable payloadSize, and if the data is coming
  ** from the stack or from a pseudo-table zRec. If the data is coming
  ** from a real cursor, then zRec is left as NULL.






  **
  ** We also compute the number of columns in the record.  For cursors,
  ** the number of columns is stored in the Cursor.nField element.  For
  ** records on the stack, the next entry down on the stack is an integer
  ** which is the number of records.
  */
  if( p1<0 ){

    Mem *pRec = &pTos[p1];
    Mem *pCnt = &pRec[-1];
    assert( pRec>=p->aStack );
    assert( pRec->flags & MEM_Blob );
    payloadSize = pRec->n;
    zRec = pRec->z;
    assert( pCnt>=p->aStack );
    assert( pCnt->flags & MEM_Int );
    nField = pCnt->i;
  }else if( (pC = p->apCsr[p1])->pCursor!=0 ){

    sqlite3VdbeCursorMoveto(pC);
    zRec = 0;
    pCrsr = pC->pCursor;
    if( pC->nullRow ){
      payloadSize = 0;
    }else if( pC->cacheValid ){
      payloadSize = pC->payloadSize;

    }else if( pC->keyAsData ){
      i64 payloadSize64;
      sqlite3BtreeKeySize(pCrsr, &payloadSize64);
      payloadSize = payloadSize64;
    }else{
      sqlite3BtreeDataSize(pCrsr, &payloadSize);
    }
    nField = pC->nField;
  }else if( pC->pseudoTable ){

    payloadSize = pC->nData;
    zRec = pC->pData;
    pC->cacheValid = 0;
    assert( payloadSize==0 || zRec!=0 );
    nField = pC->nField;
  }else{
    payloadSize = 0;
................................................................................
      zData = zRec;
    }else{
      if( pC->keyAsData ){
        zData = (char*)sqlite3BtreeKeyFetch(pCrsr, &avail);
      }else{
        zData = (char*)sqlite3BtreeDataFetch(pCrsr, &avail);
      }










    }
    idx = sqlite3GetVarint32(zData, &szHdr);






    /* Get the complete header text */

    if( !zRec && avail<idx ){
      rc = sqlite3VdbeMemFromBtree(pCrsr, 0, szHdr, pC->keyAsData, &sMem);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      zData = sMem.z;
    }








|







 







|







 







|
|
|
>
>
>
>
>
>







>










>







>









>







 







>
>
>
>
>
>
>
>
>
>



>
>
>
>
>
|
>
|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
....
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
....
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.369 2004/06/12 18:12:16 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
*/
case OP_IdxColumn:
case OP_Column: {
  int payloadSize;   /* Number of bytes in the record */
  int p1 = pOp->p1;  /* P1 value of the opcode */
  int p2 = pOp->p2;  /* column number to retrieve */
  Cursor *pC = 0;    /* The VDBE cursor */
  char *zRec;        /* Pointer to complete record-data */
  BtCursor *pCrsr;   /* The BTree cursor */
  u32 *aType;        /* aType[i] holds the numeric type of the i-th column */
  u32 *aOffset;      /* aOffset[i] is offset to start of data for i-th column */
  u32 nField;        /* number of fields in the record */
  u32 szHdr;         /* Number of bytes in the record header */
  int len;           /* The length of the serialized data for the column */
  int offset = 0;    /* Offset into the data */
................................................................................
  char *zData;       /* Part of the record being decoded */
  Mem sMem;          /* For storing the record being decoded */

  sMem.flags = 0;
  assert( p1<p->nCursor );
  pTos++;

  /* This block sets the variable payloadSize to be the total number of
  ** bytes in the record.
  **
  ** zRec is set to be the complete text of the record if it is available.
  ** The complete record text is always available for pseudo-tables and
  ** when we are decoded a record from the stack.  If the record is stored
  ** in a cursor, the complete record text might be available in the 
  ** pC->aRow cache.  Or it might not be.  If the data is unavailable,
  ** zRec is set to NULL.
  **
  ** We also compute the number of columns in the record.  For cursors,
  ** the number of columns is stored in the Cursor.nField element.  For
  ** records on the stack, the next entry down on the stack is an integer
  ** which is the number of records.
  */
  if( p1<0 ){
    /* Take the record off of the stack */
    Mem *pRec = &pTos[p1];
    Mem *pCnt = &pRec[-1];
    assert( pRec>=p->aStack );
    assert( pRec->flags & MEM_Blob );
    payloadSize = pRec->n;
    zRec = pRec->z;
    assert( pCnt>=p->aStack );
    assert( pCnt->flags & MEM_Int );
    nField = pCnt->i;
  }else if( (pC = p->apCsr[p1])->pCursor!=0 ){
    /* The record is stored in a B-Tree */
    sqlite3VdbeCursorMoveto(pC);
    zRec = 0;
    pCrsr = pC->pCursor;
    if( pC->nullRow ){
      payloadSize = 0;
    }else if( pC->cacheValid ){
      payloadSize = pC->payloadSize;
      zRec = pC->aRow;
    }else if( pC->keyAsData ){
      i64 payloadSize64;
      sqlite3BtreeKeySize(pCrsr, &payloadSize64);
      payloadSize = payloadSize64;
    }else{
      sqlite3BtreeDataSize(pCrsr, &payloadSize);
    }
    nField = pC->nField;
  }else if( pC->pseudoTable ){
    /* The record is the sole entry of a pseudo-table */
    payloadSize = pC->nData;
    zRec = pC->pData;
    pC->cacheValid = 0;
    assert( payloadSize==0 || zRec!=0 );
    nField = pC->nField;
  }else{
    payloadSize = 0;
................................................................................
      zData = zRec;
    }else{
      if( pC->keyAsData ){
        zData = (char*)sqlite3BtreeKeyFetch(pCrsr, &avail);
      }else{
        zData = (char*)sqlite3BtreeDataFetch(pCrsr, &avail);
      }
      /* If KeyFetch()/DataFetch() managed to get the entire payload,
      ** save the payload in the pC->aRow cache.  That will save us from
      ** having to make additional calls to fetch the content portion of
      ** the record.
      */
      if( avail>=payloadSize ){
        zRec = pC->aRow = zData;
      }else{
        pC->aRow = 0;
      }
    }
    idx = sqlite3GetVarint32(zData, &szHdr);


    /* The KeyFetch() or DataFetch() above are fast and will get the entire
    ** record header in most cases.  But they will fail to get the complete
    ** record header if the record header does not fit on a single page
    ** in the B-Tree.  When that happens, use sqlite3VdbeMemFromBtree() to
    ** acquire the complete header text.
    */
    if( !zRec && avail<szHdr ){
      rc = sqlite3VdbeMemFromBtree(pCrsr, 0, szHdr, pC->keyAsData, &sMem);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      zData = sMem.z;
    }

Changes to src/vdbeInt.h.

79
80
81
82
83
84
85
86


87
88
89
90

91
92
93
94
95
96
97
  char *pData;          /* Data for a NEW or OLD pseudo-table */
  i64 iKey;             /* Key for the NEW or OLD pseudo-table row */
  u8 *pIncrKey;         /* Pointer to pKeyInfo->incrKey */
  KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
  int nField;           /* Number of fields in the header */

  /* Cached information about the header for the data record that the
  ** cursor is currently pointing to */


  Bool cacheValid;      /* True if the cache is valid */
  int payloadSize;      /* Total number of bytes in the record */
  u32 *aType;           /* Type values for all entries in the record */
  u32 *aOffset;         /* Cached offsets to the start of each columns data */

};
typedef struct Cursor Cursor;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
*/







|
>
>




>







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
  char *pData;          /* Data for a NEW or OLD pseudo-table */
  i64 iKey;             /* Key for the NEW or OLD pseudo-table row */
  u8 *pIncrKey;         /* Pointer to pKeyInfo->incrKey */
  KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
  int nField;           /* Number of fields in the header */

  /* Cached information about the header for the data record that the
  ** cursor is currently pointing to.  Only valid if cacheValid is true.
  ** zRow might point to (ephemeral) data for the current row, or it might
  ** be NULL. */
  Bool cacheValid;      /* True if the cache is valid */
  int payloadSize;      /* Total number of bytes in the record */
  u32 *aType;           /* Type values for all entries in the record */
  u32 *aOffset;         /* Cached offsets to the start of each columns data */
  u8 *aRow;             /* Data for the current row, if all on one page */
};
typedef struct Cursor Cursor;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
*/