From cb0540f94a3f6103e5f8fc58fbad25a366c4e2f5 Mon Sep 17 00:00:00 2001 From: drh Date: Wed, 26 Feb 2014 19:05:53 +0000 Subject: [PATCH] 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. FossilOrigin-Name: 3ad687b7aa908b0e914be48cc8a21af5d07e52d9 --- manifest | 21 ++++++++++++--------- manifest.uuid | 2 +- src/expr.c | 1 + src/select.c | 5 ++++- src/sqliteInt.h | 2 ++ src/where.c | 8 +++++++- 6 files changed, 27 insertions(+), 12 deletions(-) diff --git a/manifest b/manifest index b398e0004c..764f8806ad 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C In\sthe\scommand-line\sshell\sfor\sCSV\simport,\sif\sthe\slines\sare\s\\r\\n\sterminated\nand\sthe\slast\sfield\sis\sblank,\smake\ssure\san\sempty\sstring\sand\snot\sa\s"\\r"\sstring\nis\simported. -D 2014-02-26T13:53:34.384 +C Only\srun\sthe\sOP_OpenRead\sopcodes\sfor\sa\scorrelated\ssubquery\sonce,\son\sthe\sinitial\niteration.\sKeep\sthe\scursor\sopen\sfor\ssubsequent\sruns.\sThis\swas\ssuppose\sto\sbe\sa\nperformance\senhancement,\sbut\sit\sis\sdifficult\sto\scome\sup\swith\sa\squery\swhere\nis\smakes\sa\ssignificant\sdifference.\s\sHence,\sthe\schange\sis\sgetting\sparked\nin\sa\sbranch. +D 2014-02-26T19:05:53.265 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 2ef13430cd359f7b361bb863504e227b25cc7f81 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -172,7 +172,7 @@ F src/complete.c dc1d136c0feee03c2f7550bafc0d29075e36deac F src/ctime.c 77779efbe78dd678d84bfb4fc2e87b6b6ad8dccd F src/date.c 593c744b2623971e45affd0bde347631bdfa4625 F src/delete.c cdd57149543bb28304d8f717c243f2a86b1fc280 -F src/expr.c 014b8087a15c4c314bdd798cb1cb0b32693f8b40 +F src/expr.c 0b84f62d653a74e1aa9f7fed39d65be74a32a125 F src/fault.c 160a0c015b6c2629d3899ed2daf63d75754a32bb F src/fkey.c 5269ef07b100763134f71b889327c333bd0989cf F src/func.c f4499b39d66b71825514334ce67b32ff14bd19f5 @@ -216,12 +216,12 @@ F src/printf.c 85d07756e45d7496d19439dcae3e6e9e0090f269 F src/random.c d10c1f85b6709ca97278428fd5db5bbb9c74eece F src/resolve.c ca8b99d894164435f5c55cb304c1b8121705c51e F src/rowset.c 64655f1a627c9c212d9ab497899e7424a34222e0 -F src/select.c 28bff39f9bc5ec618b0719fe3f7b4be9f88b6f02 +F src/select.c c959b4d9eb9b1e1adac37a5f792c9d6d3a7e561d F src/shell.c ca2b066ab31793532530e0c2fcf79e99628f3b2b F src/sqlite.h.in a2ef671f92747a5a1c8a47bad5c585a8dd9eca80 F src/sqlite3.rc 11094cc6a157a028b301a9f06b3d03089ea37c3e F src/sqlite3ext.h 886f5a34de171002ad46fae8c36a7d8051c190fc -F src/sqliteInt.h 46dfbe0b58282421188a6c25b6c0c0fae18e0134 +F src/sqliteInt.h 036e187f2b667a8315b949ebe0ed904ba1ba07a9 F src/sqliteLimit.h 164b0e6749d31e0daa1a4589a169d31c0dec7b3d F src/status.c 7ac05a5c7017d0b9f0b4bcd701228b784f987158 F src/table.c 2cd62736f845d82200acfa1287e33feb3c15d62e @@ -290,7 +290,7 @@ F src/vtab.c 21b932841e51ebd7d075e2d0ad1415dce8d2d5fd F src/wal.c 76e7fc6de229bea8b30bb2539110f03a494dc3a8 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c 11edb74d587bc87b33ca96a5173e3ec1b8389e45 -F src/where.c 6042e1a377cf7dc72c10493269ed75e276275cd8 +F src/where.c 655b8fc1858f58268945886648f98ca855c78dad F src/whereInt.h 921f935af8b684ffb49705610bda7284db1db138 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 @@ -1152,7 +1152,10 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh d1a6de74685f360ab718efda6265994b99bbea01 F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff -P dca1945aeb3fb005263f9be00ee8e72b966ae303 -R 4bbcd8ca02f23a972c97ceb5ec0d3c0c +P 9c2e7612cd137895e13ee872e668ce1216719d85 +R e4da2fed75cedad3cb42445a5f266f50 +T *branch * open-only-once +T *sym-open-only-once * +T -sym-trunk * U drh -Z 998297c355a76316139497aa81dafb02 +Z e719204d25b226ef45e13c0c8ac591b5 diff --git a/manifest.uuid b/manifest.uuid index b18a32bd5f..0e78a81af7 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -9c2e7612cd137895e13ee872e668ce1216719d85 \ No newline at end of file +3ad687b7aa908b0e914be48cc8a21af5d07e52d9 \ No newline at end of file diff --git a/src/expr.c b/src/expr.c index 722a77db73..de295af4fe 100644 --- a/src/expr.c +++ b/src/expr.c @@ -1868,6 +1868,7 @@ int sqlite3CodeSubselect( 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; } diff --git a/src/select.c b/src/select.c index 93947d56b2..05a89eba07 100644 --- a/src/select.c +++ b/src/select.c @@ -37,6 +37,7 @@ static void clearSelect(sqlite3 *db, Select *p){ */ void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){ pDest->eDest = (u8)eDest; + pDest->wctrlFlags = 0; pDest->iSDParm = iParm; pDest->affSdst = 0; pDest->iSdst = 0; @@ -1927,6 +1928,7 @@ static void generateWithRecursiveQuery( ** 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; @@ -4727,7 +4729,8 @@ int sqlite3Select( if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ - u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0); + u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0) + | pDest->wctrlFlags; /* Begin the database scan. */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList, diff --git a/src/sqliteInt.h b/src/sqliteInt.h index c7f2941d5d..eaf6b400e4 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -2075,6 +2075,7 @@ struct SrcList { #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() */ @@ -2269,6 +2270,7 @@ struct Select { 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 */ diff --git a/src/where.c b/src/where.c index a5dd7b59e7..ec0bc5b05c 100644 --- a/src/where.c +++ b/src/where.c @@ -5673,6 +5673,7 @@ WhereInfo *sqlite3WhereBegin( 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; @@ -5697,6 +5698,10 @@ WhereInfo *sqlite3WhereBegin( 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 ); @@ -5740,6 +5745,7 @@ WhereInfo *sqlite3WhereBegin( 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); } @@ -5897,7 +5903,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ */ if( (pTab->tabFlags & TF_Ephemeral)==0 && pTab->pSelect==0 - && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 + && (pWInfo->wctrlFlags & (WHERE_OMIT_OPEN_CLOSE|WHERE_OPEN_ONCE))==0 ){ int ws = pLoop->wsFlags; if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){ -- 2.39.5