Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -455,10 +455,11 @@ struct ShellState { sqlite3 *db; /* The database */ int echoOn; /* True to echo input commands */ int autoEQP; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */ int statsOn; /* True to display memory stats before each finalize */ + int loopCountersOn; /* True to display loop counters */ int outCount; /* Revert to stdout when reaching zero */ int cnt; /* Number of records displayed so far */ FILE *out; /* Write results here */ FILE *traceOut; /* Output for sqlite3_trace() */ int nErr; /* Number of errors seen */ @@ -1086,10 +1087,34 @@ if( zErrMsg ){ memcpy(zErrMsg, sqlite3_errmsg(db), nErrMsg); } return zErrMsg; } + +static void display_loop_counters( + sqlite3 *db, /* Database to query */ + ShellState *pArg /* Pointer to ShellState */ +){ +#if !defined(SQLITE_DEBUG) || !defined(SQLITE_ENABLE_LOOPCOUNTERS) + fprintf(pArg->out, + "No loop counters. " + "Rebuild with SQLITE_DEBUG and SQLITE_ENABLE_LOOPCOUNTERS" + ); +#else + int i; + for(i=0; 1; i++){ + int nTest; + int nVisit; + int iSub; + int iLoop; + const char *zLoop; + zLoop = sqlite3_stmt_loopcounter(pArg->pStmt, i, &iSub, &iLoop, &nTest, &nVisit); + if( zLoop==0 ) break; + fprintf(pArg->out, "loop %d.%d: (%d/%d) %s\n", iSub, iLoop, nVisit, nTest, zLoop); + } +#endif +} /* ** Display memory stats. */ static int display_stats( @@ -1420,10 +1445,15 @@ /* print usage stats if stats on */ if( pArg && pArg->statsOn ){ display_stats(db, pArg, 0); } + + /* print loop-counters if required */ + if( pArg && pArg->loopCountersOn ){ + display_loop_counters(db, pArg); + } /* Finalize the statement just executed. If this fails, save a ** copy of the error message. Otherwise, set zSql to point to the ** next statement to execute. */ rc2 = sqlite3_finalize(pStmt); @@ -2816,10 +2846,20 @@ const char *zFile = azArg[1]; output_file_close(p->pLog); p->pLog = output_file_open(zFile); } }else + + if( c=='l' && strncmp(azArg[0], "loopcounters", n)==0 ){ + if( nArg==2 ){ + p->loopCountersOn = booleanValue(azArg[1]); + }else{ + fprintf(stderr, "Usage: .loopcounters on|off\n"); + rc = 1; + } + }else + if( c=='m' && strncmp(azArg[0], "mode", n)==0 ){ const char *zMode = nArg>=2 ? azArg[1] : ""; int n2 = (int)strlen(zMode); int c2 = zMode[0]; @@ -4138,10 +4178,12 @@ data.echoOn = 1; }else if( strcmp(z,"-eqp")==0 ){ data.autoEQP = 1; }else if( strcmp(z,"-stats")==0 ){ data.statsOn = 1; + }else if( strcmp(z,"-loopcounters")==0 ){ + data.loopCountersOn = 1; }else if( strcmp(z,"-bail")==0 ){ bail_on_error = 1; }else if( strcmp(z,"-version")==0 ){ printf("%s %s\n", sqlite3_libversion(), sqlite3_sourceid()); return 0; Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -7404,10 +7404,33 @@ /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */ #define SQLITE_FAIL 3 /* #define SQLITE_ABORT 4 // Also an error code */ #define SQLITE_REPLACE 5 +/* +** This API function is only available if SQLite is compiled with both +** SQLITE_DEBUG and SQLITE_ENABLE_LOOPCOUNTERS defined. It is primarily +** useful with SELECT statements. +** +** Each call returns information corresponding to a single "loop" run as +** part of the statement. Each loop corresponds to a single "SEARCH" or "SCAN" +** row that would be returned by EXPLAIN QUERY PLAN on the same SQL statement. +** The second parameter - idx - determines which loop information is returned +** regarding. If idx is less than 0 or greater than or equal to the number of +** loops in the statement, NULL Is returned and the final values of the four +** output parameters are undefined. Otherwise, the return value points to a +** string describing the loop in question - the same string as would appear as +** the fourth column of EXPLAIN QUERY PLAN output. The first two integer output +** parameters are set to the values that would be returned as the first two +** columns of the same row of EQP output. +** +** The third output parameter is set to the number of rows that were visited +** by the loop the last time the statement was run. The fourth output parameter +** is set to the number of rows visited including those excluded by non-indexed +** WHERE terms. +*/ +const char *sqlite3_stmt_loopcounter(sqlite3_stmt*, int idx, int*, int*, int*, int*); /* ** Undo the hack that converts floating point types to integer for ** builds on processors without floating point support. Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -603,10 +603,13 @@ for(pc=p->pc; rc==SQLITE_OK; pc++){ assert( pc>=0 && pcnOp ); if( db->mallocFailed ) goto no_mem; #ifdef VDBE_PROFILE start = sqlite3Hwtime(); +#endif +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + if( p->pFrame==0 ) p->anExec[pc]++; #endif nVmStep++; pOp = &aOp[pc]; /* Only allow tracing if SQLITE_DEBUG is defined. Index: src/vdbe.h ================================================================== --- src/vdbe.h +++ src/vdbe.h @@ -279,7 +279,13 @@ # define VdbeCoverageIf(v,x) # define VdbeCoverageAlwaysTaken(v) # define VdbeCoverageNeverTaken(v) # define VDBE_OFFSET_LINENO(x) 0 #endif + +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) +void sqlite3VdbeLoopCounter(Vdbe*, int addrExplain, int addrTest, int addrBody); +#else +# define sqlite3VdbeLoopCounter(a,b,c,d) +#endif #endif Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -293,10 +293,17 @@ /* A bitfield type for use inside of structures. Always follow with :N where ** N is the number of bits. */ typedef unsigned bft; /* Bit Field Type */ +typedef struct LoopCounter LoopCounter; +struct LoopCounter { + int addrExplain; /* OP_Explain for loop */ + int addrTest; /* Address of non-indexed WHERE term tests */ + int addrBody; /* Address of loop body */ +}; + /* ** An instance of the virtual machine. This structure contains the complete ** state of the virtual machine. ** ** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare() @@ -365,10 +372,15 @@ u32 expmask; /* Binding to these vars invalidates VM */ SubProgram *pProgram; /* Linked list of all sub-programs used by VM */ int nOnceFlag; /* Size of array aOnceFlag[] */ u8 *aOnceFlag; /* Flags for OP_Once */ AuxData *pAuxData; /* Linked list of auxdata allocations */ +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + int *anExec; /* Number of times each op has been executed */ + int nLoop; /* Entries in aLoop[] */ + LoopCounter *aLoop; /* Loop definitions for sqlite3_stmt_loopcounter() */ +#endif }; /* ** The following are allowed values for Vdbe.magic */ Index: src/vdbeapi.c ================================================================== --- src/vdbeapi.c +++ src/vdbeapi.c @@ -480,10 +480,13 @@ #ifndef SQLITE_OMIT_TRACE if( db->xProfile && !db->init.busy ){ sqlite3OsCurrentTimeInt64(db->pVfs, &p->startTime); } #endif +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + memset(p->anExec, 0, sizeof(int) * p->nOp); +#endif db->nVdbeActive++; if( p->readOnly==0 ) db->nVdbeWrite++; if( p->bIsReader ) db->nVdbeRead++; p->pc = 0; @@ -1452,5 +1455,21 @@ Vdbe *pVdbe = (Vdbe*)pStmt; u32 v = pVdbe->aCounter[op]; if( resetFlag ) pVdbe->aCounter[op] = 0; return (int)v; } + +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) +const char *sqlite3_stmt_loopcounter( + sqlite3_stmt *pStmt, int idx, int *piSub, int *piLoop, int *pnTest, int *pnVisit +){ + Vdbe *p = (Vdbe*)pStmt; + if( idx>=p->nLoop || idx<0 ) return 0; + *piSub = p->aOp[p->aLoop[idx].addrExplain].p1; + *piLoop = p->aOp[p->aLoop[idx].addrExplain].p2; + *pnTest = p->anExec[ p->aLoop[idx].addrTest ]; + *pnVisit = p->anExec[ p->aLoop[idx].addrBody ]; + return p->aOp[p->aLoop[idx].addrExplain].p4.z; +} +#endif + + Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -595,10 +595,33 @@ p->nOp += nOp; } return addr; } +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) +void sqlite3VdbeLoopCounter( + Vdbe *p, + int addrExplain, + int addrTest, + int addrBody +){ + int nByte = (p->nLoop+1) * sizeof(LoopCounter); + if( addrTest>=0 ){ + p->aLoop = (LoopCounter*)sqlite3DbReallocOrFree(p->db, p->aLoop, nByte); + p->nLoop++; + } + if( p->aLoop ){ + LoopCounter *pNew = &p->aLoop[p->nLoop-1]; + pNew->addrExplain = addrExplain; + if( addrTest>=0 ){ + pNew->addrTest = addrTest; + pNew->addrBody = addrBody; + } + } +} +#endif + /* ** Change the value of the P1 operand for a specific instruction. ** This routine is useful when a large program is loaded from a ** static array using sqlite3VdbeAddOpList but we want to make a ** few minor changes to the program. @@ -1699,10 +1722,14 @@ p->pFree = sqlite3DbMallocZero(db, nByte); } zCsr = p->pFree; zEnd = &zCsr[nByte]; }while( nByte && !db->mallocFailed ); + +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + p->anExec = (int*)sqlite3DbMallocZero(db, sizeof(int) * p->nOp); +#endif p->nCursor = nCursor; p->nOnceFlag = nOnce; if( p->aVar ){ p->nVar = (ynVar)nVar; @@ -2677,10 +2704,14 @@ for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]); vdbeFreeOpArray(db, p->aOp, p->nOp); sqlite3DbFree(db, p->aColName); sqlite3DbFree(db, p->zSql); sqlite3DbFree(db, p->pFree); +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + sqlite3DbFree(db, p->aLoop); + sqlite3DbFree(db, p->anExec); +#endif } /* ** Delete an entire VDBE. */ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2906,21 +2906,23 @@ int bRev; /* True if we need to scan in reverse order */ WhereLevel *pLevel; /* The where level to be coded */ WhereLoop *pLoop; /* The WhereLoop object being coded */ WhereClause *pWC; /* Decomposition of the entire WHERE clause */ WhereTerm *pTerm; /* A WHERE clause term */ - Parse *pParse; /* Parsing context */ + Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db; /* Database connection */ - Vdbe *v; /* The prepared stmt under constructions */ + Vdbe *v = pParse->pVdbe; /* The prepared stmt under constructions */ struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + int addrExplain = sqlite3VdbeCurrentAddr(v)-1; + int addrTest; /* Address of non-indexed WHERE clause test */ +#endif - pParse = pWInfo->pParse; - v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; pLevel = &pWInfo->a[iLevel]; pLoop = pLevel->pWLoop; pTabItem = &pWInfo->pTabList->a[pLevel->iFrom]; @@ -3553,10 +3555,11 @@ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, wctrlFlags, iCovCur); assert( pSubWInfo || pParse->nErr || db->mallocFailed ); if( pSubWInfo ){ WhereLoop *pSubLoop; + sqlite3VdbeLoopCounter(v, sqlite3VdbeCurrentAddr(v), -1, -1); explainOneScan( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 ); /* This is the sub-WHERE clause body. First skip over ** duplicate rows from prior sub-WHERE clauses, and record the @@ -3685,10 +3688,14 @@ VdbeCoverageIf(v, bRev==0); VdbeCoverageIf(v, bRev!=0); pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; } } + +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_LOOPCOUNTERS) + addrTest = sqlite3VdbeCurrentAddr(v); +#endif /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. */ for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ @@ -3763,10 +3770,14 @@ assert( pTerm->pExpr ); sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL); pTerm->wtFlags |= TERM_CODED; } } + + if( pParse->pTriggerTab==0 && 0==(pLoop->wsFlags & WHERE_MULTI_OR) ){ + sqlite3VdbeLoopCounter(v, addrExplain, addrTest, sqlite3VdbeCurrentAddr(v)); + } return pLevel->notReady; } #ifdef WHERETRACE_ENABLED