/ Check-in [02741d17]
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:Further prepare-time performance improvements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 02741d177bfc2fca23bc99974cd899eba13cb59a
User & Date: drh 2013-06-07 00:29:23
Context
2013-06-07
02:04
Must faster computation of estimated logarithm. check-in: dfbca3ac user: drh tags: nextgen-query-plan-exp
00:29
Further prepare-time performance improvements. check-in: 02741d17 user: drh tags: nextgen-query-plan-exp
2013-06-06
23:44
Performance improvements. check-in: 9f8e84ab user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
114
115
116
117
118
119
120





121
122
123
124
125
126
127
....
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
3941
3942
3943
3944
3945
3946
3947
3948
3949
....
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692
4693
4694
4695
4696
4697
4698
4699
4700
4701
....
4705
4706
4707
4708
4709
4710
4711
4712
4713
4714
4715
4716
4717
4718
4719
  Bitmask prereq;       /* Bitmask of other loops that must run first */
  Bitmask maskSelf;     /* Bitmask identifying table iTab */
#ifdef SQLITE_DEBUG
  char cId;             /* Symbolic ID of this loop for debugging use */
#endif
  u8 iTab;              /* Position in FROM clause of table for this loop */
  u8 iSortIdx;          /* Sorting index number.  0==None */
  u16 nLTerm;           /* Number of entries in aLTerm[] */
  u16 nLSlot;           /* Number of slots allocated for aLTerm[] */
  u32 wsFlags;          /* WHERE_* flags describing the plan */
  WhereCost rSetup;     /* One-time setup cost (ex: create transient index) */
  WhereCost rRun;       /* Cost of running each loop */
  WhereCost nOut;       /* Estimated number of output rows */
  union {
    struct {               /* Information for internal btree tables */
      int nEq;               /* Number of equality constraints */
      Index *pIndex;         /* Index used, or NULL */
................................................................................
      int idxNum;            /* Index number */
      u8 needFree;           /* True if sqlite3_free(idxStr) is needed */
      u8 isOrdered;          /* True if satisfies ORDER BY */
      u16 omitMask;          /* Terms that may be omitted */
      char *idxStr;          /* Index identifier string */
    } vtab;
  } u;





  WhereTerm **aLTerm;   /* WhereTerms used */
  WhereLoop *pNextLoop; /* Next WhereLoop object in the WhereClause */
  WhereTerm *aLTermSpace[4];  /* Initial aLTerm[] space */
};

/* Forward declaration of methods */
static int whereLoopResize(sqlite3*, WhereLoop*, int);
................................................................................

/*
** Transfer content from the second pLoop into the first.
*/
static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){
  if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM;
  whereLoopClearUnion(db, pTo);
  pTo->prereq = pFrom->prereq;
  pTo->maskSelf = pFrom->maskSelf;
  pTo->iTab = pFrom->iTab;
  pTo->iSortIdx = pFrom->iSortIdx;
  pTo->nLTerm = pFrom->nLTerm;
  pTo->rSetup = pFrom->rSetup;
  pTo->rRun = pFrom->rRun;
  pTo->nOut = pFrom->nOut;
  if( pTo->nLTerm ){
    memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));
  }
  pTo->wsFlags = pFrom->wsFlags;
  pTo->u = pFrom->u;
  if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){
    pFrom->u.vtab.needFree = 0;
  }else if( (pFrom->wsFlags & WHERE_TEMP_INDEX)!=0 ){
    pFrom->u.btree.pIndex = 0;
  }
  return SQLITE_OK;
}
................................................................................
  Bitmask mPrior = 0;
  int iTab;
  SrcList *pTabList = pWInfo->pTabList;
  struct SrcList_item *pItem;
  sqlite3 *db = pWInfo->pParse->db;
  int nTabList = pWInfo->nLevel;
  int rc = SQLITE_OK;
  WhereLoop *pNew;

  /* Loop over the tables in the join, from left to right */
  pBuilder->pNew = pNew = sqlite3DbMallocZero(db, sizeof(WhereLoop));
  if( pNew==0 ) return SQLITE_NOMEM;
  pNew->aLTerm = pNew->aLTermSpace;
  pNew->nLSlot = ArraySize(pNew->aLTermSpace);
  for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
    pNew->iTab = iTab;
    pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){
      mExtra = mPrior;
    }
    if( IsVirtual(pItem->pTab) ){
................................................................................
    }
    if( rc==SQLITE_OK ){
      rc = whereLoopAddOr(pBuilder, mExtra);
    }
    mPrior |= pNew->maskSelf;
    if( rc || db->mallocFailed ) break;
  }
  whereLoopDelete(db, pBuilder->pNew);
  pBuilder->pNew = 0;
  return rc;
}

/*
** Examine a WherePath (with the addition of the extra WhereLoop of the 5th
** parameters) to see if it outputs rows in the requested ORDER BY







<
<
<







 







>
>
>
>
>







 







|
<
<
<
<
<
<
<
<
|
<
<
<







 







|


|
|
<
<







 







|







95
96
97
98
99
100
101



102
103
104
105
106
107
108
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
....
3925
3926
3927
3928
3929
3930
3931
3932








3933



3934
3935
3936
3937
3938
3939
3940
....
4672
4673
4674
4675
4676
4677
4678
4679
4680
4681
4682
4683


4684
4685
4686
4687
4688
4689
4690
....
4694
4695
4696
4697
4698
4699
4700
4701
4702
4703
4704
4705
4706
4707
4708
  Bitmask prereq;       /* Bitmask of other loops that must run first */
  Bitmask maskSelf;     /* Bitmask identifying table iTab */
#ifdef SQLITE_DEBUG
  char cId;             /* Symbolic ID of this loop for debugging use */
#endif
  u8 iTab;              /* Position in FROM clause of table for this loop */
  u8 iSortIdx;          /* Sorting index number.  0==None */



  WhereCost rSetup;     /* One-time setup cost (ex: create transient index) */
  WhereCost rRun;       /* Cost of running each loop */
  WhereCost nOut;       /* Estimated number of output rows */
  union {
    struct {               /* Information for internal btree tables */
      int nEq;               /* Number of equality constraints */
      Index *pIndex;         /* Index used, or NULL */
................................................................................
      int idxNum;            /* Index number */
      u8 needFree;           /* True if sqlite3_free(idxStr) is needed */
      u8 isOrdered;          /* True if satisfies ORDER BY */
      u16 omitMask;          /* Terms that may be omitted */
      char *idxStr;          /* Index identifier string */
    } vtab;
  } u;
  u32 wsFlags;          /* WHERE_* flags describing the plan */
  u16 nLTerm;           /* Number of entries in aLTerm[] */
  /**** whereLoopXfer() copies fields above ***********************/
# define WHERE_LOOP_XFER_SZ offsetof(WhereLoop,nLSlot)
  u16 nLSlot;           /* Number of slots allocated for aLTerm[] */
  WhereTerm **aLTerm;   /* WhereTerms used */
  WhereLoop *pNextLoop; /* Next WhereLoop object in the WhereClause */
  WhereTerm *aLTermSpace[4];  /* Initial aLTerm[] space */
};

/* Forward declaration of methods */
static int whereLoopResize(sqlite3*, WhereLoop*, int);
................................................................................

/*
** Transfer content from the second pLoop into the first.
*/
static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){
  if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM;
  whereLoopClearUnion(db, pTo);
  memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ);








  memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));



  if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){
    pFrom->u.vtab.needFree = 0;
  }else if( (pFrom->wsFlags & WHERE_TEMP_INDEX)!=0 ){
    pFrom->u.btree.pIndex = 0;
  }
  return SQLITE_OK;
}
................................................................................
  Bitmask mPrior = 0;
  int iTab;
  SrcList *pTabList = pWInfo->pTabList;
  struct SrcList_item *pItem;
  sqlite3 *db = pWInfo->pParse->db;
  int nTabList = pWInfo->nLevel;
  int rc = SQLITE_OK;
  WhereLoop *pNew, sNew;

  /* Loop over the tables in the join, from left to right */
  pBuilder->pNew = pNew = &sNew;
  whereLoopInit(pNew);


  for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
    pNew->iTab = iTab;
    pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){
      mExtra = mPrior;
    }
    if( IsVirtual(pItem->pTab) ){
................................................................................
    }
    if( rc==SQLITE_OK ){
      rc = whereLoopAddOr(pBuilder, mExtra);
    }
    mPrior |= pNew->maskSelf;
    if( rc || db->mallocFailed ) break;
  }
  whereLoopClear(db, pNew);
  pBuilder->pNew = 0;
  return rc;
}

/*
** Examine a WherePath (with the addition of the extra WhereLoop of the 5th
** parameters) to see if it outputs rows in the requested ORDER BY