/ Check-in [180be266]
Login

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

Overview
Comment:Avoid allocating excessive registers for the PARTITION BY expressions when processing window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 180be266238e18c01f8bd52c75dd9aa3e26e553620258141cd95189a0ae59ddb
User & Date: dan 2019-03-13 17:20:27
Wiki:window-functions
Context
2019-03-13
17:31
Merge latest trunk changes into this branch. check-in: 0b904517 user: dan tags: window-functions
17:20
Avoid allocating excessive registers for the PARTITION BY expressions when processing window functions. check-in: 180be266 user: dan tags: window-functions
15:29
Remove rows from the ephemeral table used by window functions once they are no longer required. check-in: 6ad55319 user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  3567   3567     Expr *pFilter;          /* The FILTER expression */
  3568   3568     FuncDef *pFunc;         /* The function */
  3569   3569     int iEphCsr;            /* Partition buffer or Peer buffer */
  3570   3570     int regAccum;
  3571   3571     int regResult;
  3572   3572     int csrApp;             /* Function cursor (used by min/max) */
  3573   3573     int regApp;             /* Function register (also used by min/max) */
  3574         -  int regPart;            /* First in a set of registers holding PARTITION BY
  3575         -                          ** and ORDER BY values for the window */
         3574  +  int regPart;            /* Array of registers for PARTITION BY values */
  3576   3575     Expr *pOwner;           /* Expression object this window is attached to */
  3577   3576     int nBufferCol;         /* Number of columns in buffer table */
  3578   3577     int iArgCol;            /* Offset of first argument for this function */
  3579   3578     int regFirst;
  3580   3579   };
  3581   3580   
  3582   3581   #ifndef SQLITE_OMIT_WINDOWFUNC

Changes to src/window.c.

  1114   1114   ** This is called by code in select.c before it calls sqlite3WhereBegin()
  1115   1115   ** to begin iterating through the sub-query results. It is used to allocate
  1116   1116   ** and initialize registers and cursors used by sqlite3WindowCodeStep().
  1117   1117   */
  1118   1118   void sqlite3WindowCodeInit(Parse *pParse, Window *pMWin){
  1119   1119     Window *pWin;
  1120   1120     Vdbe *v = sqlite3GetVdbe(pParse);
  1121         -  int nPart = (pMWin->pPartition ? pMWin->pPartition->nExpr : 0);
  1122         -  nPart += (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
  1123         -  if( nPart ){
         1121  +
         1122  +  /* Allocate registers to use for PARTITION BY values, if any. Initialize
         1123  +  ** said registers to NULL.  */
         1124  +  if( pMWin->pPartition ){
         1125  +    int nExpr = pMWin->pPartition->nExpr;
  1124   1126       pMWin->regPart = pParse->nMem+1;
  1125         -    pParse->nMem += nPart;
  1126         -    sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nPart-1);
         1127  +    pParse->nMem += nExpr;
         1128  +    sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
  1127   1129     }
  1128   1130   
  1129   1131     pMWin->regFirst = ++pParse->nMem;
  1130   1132     sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  1131   1133   
  1132   1134     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1133   1135       FuncDef *p = pWin->pFunc;
................................................................................
  2107   2109   **         }
  2108   2110   **         RETURN_ROW
  2109   2111   **       }
  2110   2112   **       while( !eof csrCurrent ){
  2111   2113   **         RETURN_ROW
  2112   2114   **       }
  2113   2115   **
         2116  +** The text above leaves out many details. Refer to the code and comments
         2117  +** below for a more complete picture.
  2114   2118   */
  2115   2119   void sqlite3WindowCodeStep(
  2116   2120     Parse *pParse,                  /* Parse context */
  2117   2121     Select *p,                      /* Rewritten SELECT statement */
  2118   2122     WhereInfo *pWInfo,              /* Context returned by sqlite3WhereBegin() */
  2119   2123     int regGosub,                   /* Register for OP_Gosub */
  2120   2124     int addrGosub                   /* OP_Gosub here to return each row */
................................................................................
  2298   2302       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
  2299   2303       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
  2300   2304     }
  2301   2305   
  2302   2306     sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  2303   2307     sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
  2304   2308   
  2305         -  /* Begin generating SECOND_ROW_CODE */
  2306         -  VdbeModuleComment((pParse->pVdbe, "Begin WindowCodeStep.SECOND_ROW"));
  2307   2309     sqlite3VdbeJumpHere(v, addrIfNot);
  2308   2310     if( regPeer ){
  2309   2311       windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
  2310   2312     }
  2311   2313     if( pMWin->eStart==TK_FOLLOWING ){
  2312   2314       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  2313   2315       if( pMWin->eEnd!=TK_UNBOUNDED ){
................................................................................
  2350   2352           if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
  2351   2353           windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  2352   2354           windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2353   2355           if( regEnd ) sqlite3VdbeJumpHere(v, addr);
  2354   2356         }
  2355   2357       }
  2356   2358     }
  2357         -  VdbeModuleComment((pParse->pVdbe, "End WindowCodeStep.SECOND_ROW"));
  2358   2359   
  2359   2360     /* End of the main input loop */
  2360   2361     sqlite3VdbeResolveLabel(v, lblWhereEnd);
  2361   2362     sqlite3WhereEnd(pWInfo);
  2362   2363   
  2363   2364     /* Fall through */
  2364   2365     if( pMWin->pPartition ){
  2365   2366       addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
  2366   2367       sqlite3VdbeJumpHere(v, addrGosubFlush);
  2367   2368     }
  2368   2369   
  2369         -  VdbeModuleComment((pParse->pVdbe, "Begin WindowCodeStep.FLUSH"));
  2370   2370     addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  2371   2371     if( pMWin->eEnd==TK_PRECEDING ){
  2372   2372       windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
  2373   2373       windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  2374   2374     }else if( pMWin->eStart==TK_FOLLOWING ){
  2375   2375       int addrStart;
  2376   2376       int addrBreak1;
................................................................................
  2409   2409       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  2410   2410       sqlite3VdbeJumpHere(v, addrBreak);
  2411   2411     }
  2412   2412     sqlite3VdbeJumpHere(v, addrEmpty);
  2413   2413   
  2414   2414     sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  2415   2415     sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  2416         -  VdbeModuleComment((pParse->pVdbe, "End WindowCodeStep.FLUSH"));
  2417   2416     if( pMWin->pPartition ){
  2418   2417       sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
  2419   2418       sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  2420   2419     }
  2421   2420   }
  2422   2421   
  2423   2422   #endif /* SQLITE_OMIT_WINDOWFUNC */