/ Check-in [3ad687b7]
Login

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

Overview
Comment:Only run the OP_OpenRead opcodes for a correlated subquery once, on the initial iteration. Keep the cursor open for subsequent runs. This was suppose to be a performance enhancement, but it is difficult to come up with a query where is makes a significant difference. Hence, the change is getting parked in a branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | open-only-once
Files: files | file ages | folders
SHA1:3ad687b7aa908b0e914be48cc8a21af5d07e52d9
User & Date: drh 2014-02-26 19:05:53
Context
2014-02-26
19:05
Only run the OP_OpenRead opcodes for a correlated subquery once, on the initial iteration. Keep the cursor open for subsequent runs. This was suppose to be a performance enhancement, but it is difficult to come up with a query where is makes a significant difference. Hence, the change is getting parked in a branch. Leaf check-in: 3ad687b7 user: drh tags: open-only-once
13:53
In the command-line shell for CSV import, if the lines are \r\n terminated and the last field is blank, make sure an empty string and not a "\r" string is imported. check-in: 9c2e7612 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

1864
1865
1866
1867
1868
1869
1870

1871
1872
1873
1874
1875
1876
1877
        sqlite3VdbeAddOp2(v, OP_Integer, 0, dest.iSDParm);
        VdbeComment((v, "Init EXISTS result"));
      }
      sqlite3ExprDelete(pParse->db, pSel->pLimit);
      pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0,
                                  &sqlite3IntTokens[1]);
      pSel->iLimit = 0;

      if( sqlite3Select(pParse, pSel, &dest) ){
        return 0;
      }
      rReg = dest.iSDParm;
      ExprSetVVAProperty(pExpr, EP_NoReduce);
      break;
    }







>







1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
        sqlite3VdbeAddOp2(v, OP_Integer, 0, dest.iSDParm);
        VdbeComment((v, "Init EXISTS result"));
      }
      sqlite3ExprDelete(pParse->db, pSel->pLimit);
      pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0,
                                  &sqlite3IntTokens[1]);
      pSel->iLimit = 0;
      dest.wctrlFlags |= WHERE_OPEN_ONCE;
      if( sqlite3Select(pParse, pSel, &dest) ){
        return 0;
      }
      rReg = dest.iSDParm;
      ExprSetVVAProperty(pExpr, EP_NoReduce);
      break;
    }

Changes to src/select.c.

33
34
35
36
37
38
39

40
41
42
43
44
45
46
....
1923
1924
1925
1926
1927
1928
1929

1930
1931
1932
1933
1934
1935
1936
....
4723
4724
4725
4726
4727
4728
4729
4730

4731
4732
4733
4734
4735
4736
4737
}

/*
** Initialize a SelectDest structure.
*/
void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
  pDest->eDest = (u8)eDest;

  pDest->iSDParm = iParm;
  pDest->affSdst = 0;
  pDest->iSdst = 0;
  pDest->nSdst = 0;
}


................................................................................
  }
  sqlite3VdbeResolveLabel(v, addrCont);

  /* Execute the recursive SELECT taking the single row in Current as
  ** the value for the recursive-table. Store the results in the Queue.
  */
  p->pPrior = 0;

  sqlite3Select(pParse, p, &destQueue);
  assert( p->pPrior==0 );
  p->pPrior = pSetup;

  /* Keep running the loop until the Queue is empty */
  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  sqlite3VdbeResolveLabel(v, addrBreak);
................................................................................
    sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  }else{
    sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  }

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);


    /* Begin the database scan. */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList,
                               wctrlFlags, 0);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);







>







 







>







 







|
>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
....
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
....
4725
4726
4727
4728
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738
4739
4740
}

/*
** Initialize a SelectDest structure.
*/
void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
  pDest->eDest = (u8)eDest;
  pDest->wctrlFlags = 0;
  pDest->iSDParm = iParm;
  pDest->affSdst = 0;
  pDest->iSdst = 0;
  pDest->nSdst = 0;
}


................................................................................
  }
  sqlite3VdbeResolveLabel(v, addrCont);

  /* Execute the recursive SELECT taking the single row in Current as
  ** the value for the recursive-table. Store the results in the Queue.
  */
  p->pPrior = 0;
  destQueue.wctrlFlags |= WHERE_OPEN_ONCE;
  sqlite3Select(pParse, p, &destQueue);
  assert( p->pPrior==0 );
  p->pPrior = pSetup;

  /* Keep running the loop until the Queue is empty */
  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  sqlite3VdbeResolveLabel(v, addrBreak);
................................................................................
    sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  }else{
    sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  }

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0) 
                     | pDest->wctrlFlags;

    /* Begin the database scan. */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList,
                               wctrlFlags, 0);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);

Changes to src/sqliteInt.h.

2071
2072
2073
2074
2075
2076
2077

2078
2079
2080
2081
2082
2083
2084
....
2265
2266
2267
2268
2269
2270
2271

2272
2273
2274
2275
2276
2277
2278
#define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
#define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
#define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
#define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */


/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
................................................................................
/*
** An instance of this object describes where to put of the results of
** a SELECT statement.
*/
struct SelectDest {
  u8 eDest;            /* How to dispose of the results.  On of SRT_* above. */
  char affSdst;        /* Affinity used when eDest==SRT_Set */

  int iSDParm;         /* A parameter used by the eDest disposal method */
  int iSdst;           /* Base register where results are written */
  int nSdst;           /* Number of registers allocated */
  ExprList *pOrderBy;  /* Key columns for SRT_Queue and SRT_DistQueue */
};

/*







>







 







>







2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
....
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
#define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
#define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
#define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
#define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
#define WHERE_OPEN_ONCE        0x0800 /* Open on first iteration only */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
................................................................................
/*
** An instance of this object describes where to put of the results of
** a SELECT statement.
*/
struct SelectDest {
  u8 eDest;            /* How to dispose of the results.  On of SRT_* above. */
  char affSdst;        /* Affinity used when eDest==SRT_Set */
  u16 wctrlFlags;      /* Extra flags for sqlite3WhereBegin() */
  int iSDParm;         /* A parameter used by the eDest disposal method */
  int iSdst;           /* Base register where results are written */
  int nSdst;           /* Number of registers allocated */
  ExprList *pOrderBy;  /* Key columns for SRT_Queue and SRT_DistQueue */
};

/*

Changes to src/where.c.

5669
5670
5671
5672
5673
5674
5675

5676
5677
5678
5679
5680
5681
5682
....
5693
5694
5695
5696
5697
5698
5699




5700
5701
5702
5703
5704
5705
5706
....
5736
5737
5738
5739
5740
5741
5742

5743
5744
5745
5746
5747
5748
5749
....
5893
5894
5895
5896
5897
5898
5899
5900
5901
5902
5903
5904
5905
5906
5907
  ** searching those tables.
  */
  notReady = ~(Bitmask)0;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;


    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    pLoop = pLevel->pWLoop;
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
      /* Do nothing */
................................................................................
    if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
         && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){
      int op = OP_OpenRead;
      if( pWInfo->okOnePass ){
        op = OP_OpenWrite;
        pWInfo->aiCurOnePass[0] = pTabItem->iCursor;
      };




      sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
      assert( pTabItem->iCursor==pLevel->iTabCur );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS-1 );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS );
      if( !pWInfo->okOnePass && pTab->nCol<BMS && HasRowid(pTab) ){
        Bitmask b = pTabItem->colUsed;
        int n = 0;
................................................................................
      pLevel->iIdxCur = iIndexCur;
      assert( pIx->pSchema==pTab->pSchema );
      assert( iIndexCur>=0 );
      sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
      sqlite3VdbeSetP4KeyInfo(pParse, pIx);
      VdbeComment((v, "%s", pIx->zName));
    }

    if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
    notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  }
  pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  if( db->mallocFailed ) goto whereBeginError;

  /* Generate the code to do the search.  Each iteration of the for
................................................................................
    /* Close all of the cursors that were opened by sqlite3WhereBegin.
    ** Except, do not close cursors that will be reused by the OR optimization
    ** (WHERE_OMIT_OPEN_CLOSE).  And do not close the OP_OpenWrite cursors
    ** created for the ONEPASS optimization.
    */
    if( (pTab->tabFlags & TF_Ephemeral)==0
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
    ){
      int ws = pLoop->wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & WHERE_INDEXED)!=0
       && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0 







>







 







>
>
>
>







 







>







 







|







5669
5670
5671
5672
5673
5674
5675
5676
5677
5678
5679
5680
5681
5682
5683
....
5694
5695
5696
5697
5698
5699
5700
5701
5702
5703
5704
5705
5706
5707
5708
5709
5710
5711
....
5741
5742
5743
5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
....
5899
5900
5901
5902
5903
5904
5905
5906
5907
5908
5909
5910
5911
5912
5913
  ** searching those tables.
  */
  notReady = ~(Bitmask)0;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;
    int addrOnce = 0;

    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    pLoop = pLevel->pWLoop;
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
      /* Do nothing */
................................................................................
    if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
         && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){
      int op = OP_OpenRead;
      if( pWInfo->okOnePass ){
        op = OP_OpenWrite;
        pWInfo->aiCurOnePass[0] = pTabItem->iCursor;
      };
      if( wctrlFlags & WHERE_OPEN_ONCE ){
        addrOnce = sqlite3CodeOnce(pParse);
        VdbeCoverage(v);
      }
      sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
      assert( pTabItem->iCursor==pLevel->iTabCur );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS-1 );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS );
      if( !pWInfo->okOnePass && pTab->nCol<BMS && HasRowid(pTab) ){
        Bitmask b = pTabItem->colUsed;
        int n = 0;
................................................................................
      pLevel->iIdxCur = iIndexCur;
      assert( pIx->pSchema==pTab->pSchema );
      assert( iIndexCur>=0 );
      sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
      sqlite3VdbeSetP4KeyInfo(pParse, pIx);
      VdbeComment((v, "%s", pIx->zName));
    }
    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
    if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
    notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  }
  pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  if( db->mallocFailed ) goto whereBeginError;

  /* Generate the code to do the search.  Each iteration of the for
................................................................................
    /* Close all of the cursors that were opened by sqlite3WhereBegin.
    ** Except, do not close cursors that will be reused by the OR optimization
    ** (WHERE_OMIT_OPEN_CLOSE).  And do not close the OP_OpenWrite cursors
    ** created for the ONEPASS optimization.
    */
    if( (pTab->tabFlags & TF_Ephemeral)==0
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & (WHERE_OMIT_OPEN_CLOSE|WHERE_OPEN_ONCE))==0
    ){
      int ws = pLoop->wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & WHERE_INDEXED)!=0
       && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0