/ Check-in [478c34b9]
Login

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

Overview
Comment:Initial implementation of an optimization that attempts to reuse the same materialization of a view on a self-join of the view.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | materialized-view-reuse
Files: files | file ages | folders
SHA3-256:478c34b9a8b5127d13024e10307aa832f160b89720c46424dd17555bd36f590d
User & Date: drh 2017-05-01 15:15:41
Context
2017-05-01
16:37
Minor bug fixes and performance enhancement. check-in: b2aae559 user: drh tags: materialized-view-reuse
15:15
Initial implementation of an optimization that attempts to reuse the same materialization of a view on a self-join of the view. check-in: 478c34b9 user: drh tags: materialized-view-reuse
2017-04-29
19:29
Add a single testcase() macro to the subquery processing logic. check-in: 4e1df76e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4874   4874           pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
  4875   4875       );
  4876   4876     }
  4877   4877   }
  4878   4878   #else
  4879   4879   # define explainSimpleCount(a,b,c)
  4880   4880   #endif
         4881  +
         4882  +/*
         4883  +** Check to see if the pThis entry of pTabList is a self-join of a prior view.
         4884  +** If it is, then return the SrcList_item for the prior view.  If it is not,
         4885  +** then return 0.
         4886  +*/
         4887  +static struct SrcList_item *isSelfJoinView(
         4888  +  SrcList *pTabList,           /* Search for self-joins in this FROM clause */
         4889  +  struct SrcList_item *pThis   /* Search for prior reference to this subquery */
         4890  +){
         4891  +  struct SrcList_item *pItem;
         4892  +  for(pItem = pTabList->a; pItem<pThis; pItem++){
         4893  +    if( pItem->pSelect==0 ) continue;
         4894  +    if( pItem->fg.viaCoroutine ) continue;
         4895  +    if( sqlite3StrICmp(pItem->zName, pThis->zName)==0 ) return pItem;
         4896  +  }
         4897  +  return 0;
         4898  +}
  4881   4899   
  4882   4900   /*
  4883   4901   ** Generate code for the SELECT statement given in the p argument.  
  4884   4902   **
  4885   4903   ** The results are returned according to the SelectDest structure.
  4886   4904   ** See comments in sqliteInt.h for further information.
  4887   4905   **
................................................................................
  5109   5127         ** the content of this subquery.  pItem->addrFillSub will point
  5110   5128         ** to the address of the generated subroutine.  pItem->regReturn
  5111   5129         ** is a register allocated to hold the subroutine return address
  5112   5130         */
  5113   5131         int topAddr;
  5114   5132         int onceAddr = 0;
  5115   5133         int retAddr;
         5134  +      struct SrcList_item *pPrior;
         5135  +
  5116   5136         assert( pItem->addrFillSub==0 );
  5117   5137         pItem->regReturn = ++pParse->nMem;
  5118   5138         topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
  5119   5139         pItem->addrFillSub = topAddr+1;
  5120   5140         if( pItem->fg.isCorrelated==0 ){
  5121   5141           /* If the subquery is not correlated and if we are not inside of
  5122   5142           ** a trigger, then we only need to compute the value of the subquery
  5123   5143           ** once. */
  5124   5144           onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  5125   5145           VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
  5126   5146         }else{
  5127   5147           VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
  5128   5148         }
  5129         -      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  5130         -      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  5131         -      sqlite3Select(pParse, pSub, &dest);
         5149  +      pPrior = isSelfJoinView(pTabList, pItem);
         5150  +      if( pPrior ){
         5151  +        sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
         5152  +      }else{
         5153  +        sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
         5154  +        explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
         5155  +        sqlite3Select(pParse, pSub, &dest);
         5156  +      }
  5132   5157         pItem->pTab->nRowLogEst = pSub->nSelectRow;
  5133   5158         if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
  5134   5159         retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
  5135   5160         VdbeComment((v, "end %s", pItem->pTab->zName));
  5136   5161         sqlite3VdbeChangeP1(v, topAddr, retAddr);
  5137   5162         sqlite3ClearTempRegCache(pParse);
  5138   5163       }

Changes to src/vdbe.c.

  3535   3535     testcase( pOp->p2 & OPFLAG_SEEKEQ );
  3536   3536   #endif
  3537   3537     sqlite3BtreeCursorHintFlags(pCur->uc.pCursor,
  3538   3538                                  (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
  3539   3539     if( rc ) goto abort_due_to_error;
  3540   3540     break;
  3541   3541   }
         3542  +
         3543  +/* Opcode: OpenDup P1 P2 * * *
         3544  +**
         3545  +** Open a new cursor P1 that points to the same ephemeral table as
         3546  +** cursor P2.  The P2 cursor must have been opened by a prior OP_OpenEphemeral
         3547  +** opcode.  Only ephemeral cursors may be duplicated.
         3548  +**
         3549  +** Duplicate ephemeral cursors are used for self-joins of materialized views.
         3550  +*/
         3551  +case OP_OpenDup: {
         3552  +  VdbeCursor *pOrig;    /* The original cursor to be duplicated */
         3553  +  VdbeCursor *pCx;      /* The new cursor */
         3554  +
         3555  +  pOrig = p->apCsr[pOp->p2];
         3556  +  assert( pOrig->pBtx!=0 );  /* Only ephemeral cursors can be duplicated */
         3557  +
         3558  +  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
         3559  +  if( pCx==0 ) goto no_mem;
         3560  +  pCx->nullRow = 1;
         3561  +  pCx->isEphemeral = 1;
         3562  +  pCx->pKeyInfo = pOrig->pKeyInfo;
         3563  +  pCx->isTable = pOrig->isTable;
         3564  +  rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR,
         3565  +                          pCx->pKeyInfo, pCx->uc.pCursor);
         3566  +  if( rc ) goto abort_due_to_error;
         3567  +  break;
         3568  +}
         3569  +
  3542   3570   
  3543   3571   /* Opcode: OpenEphemeral P1 P2 * P4 P5
  3544   3572   ** Synopsis: nColumn=P2
  3545   3573   **
  3546   3574   ** Open a new cursor P1 to a transient table.
  3547   3575   ** The cursor is always opened read/write even if 
  3548   3576   ** the main database is read-only.  The ephemeral

Changes to src/vdbeaux.c.

  2035   2035         break;
  2036   2036       }
  2037   2037       case CURTYPE_BTREE: {
  2038   2038         if( pCx->pBtx ){
  2039   2039           sqlite3BtreeClose(pCx->pBtx);
  2040   2040           /* The pCx->pCursor will be close automatically, if it exists, by
  2041   2041           ** the call above. */
  2042         -      }else{
         2042  +      }else if( !pCx->isEphemeral ){
  2043   2043           assert( pCx->uc.pCursor!=0 );
  2044   2044           sqlite3BtreeCloseCursor(pCx->uc.pCursor);
  2045   2045         }
  2046   2046         break;
  2047   2047       }
  2048   2048   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2049   2049       case CURTYPE_VTAB: {