SQLite

Check-in [eacbbd8849]
Login

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

Overview
Comment:Changes the formatting of ".scanstats on" in the shell so that the stats for subqueries are grouped together and occur after the main query.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: eacbbd8849db9b023eff15ef1cb42ec941299433
User & Date: drh 2014-11-06 12:08:21.237
Context
2014-11-06
12:17
On the ".scanstats on" output in the shell, initialize the estimated count for the first loop of each subquery to the actual loop count. (check-in: d1c51c8455 user: drh tags: scanstatus)
12:08
Changes the formatting of ".scanstats on" in the shell so that the stats for subqueries are grouped together and occur after the main query. (check-in: eacbbd8849 user: drh tags: scanstatus)
04:42
Add the SQLITE_SCANSTAT_SELECTID metric. Use it to improve the ".stmtscan on" output in the shell. (check-in: 64ad5761a8 user: drh tags: scanstatus)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.
︙︙
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200


1201
1202
1203
1204
1205
1206
1207
1208
1209
1210



1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224

1225
1226
1227
1228
1229

1230
1231
1232
1233
1234
1235
1236

** Display scan stats.
*/
static void display_scanstats(
  sqlite3 *db,                    /* Database to query */
  ShellState *pArg                /* Pointer to ShellState */
){
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int i;
  double *arEstLoop = 0;
  int nEstLoop = 0;
  fprintf(pArg->out, "-------- scanstats --------\n");


  for(i=0; 1; i++){
    sqlite3_stmt *p = pArg->pStmt;
    sqlite3_int64 nLoop, nVisit;
    double rEst, rLoop;
    int iSid;
    const char *zExplain;
    if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
      break;
    }
    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);



    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst);
    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
    if( iSid>=nEstLoop ){
      arEstLoop = sqlite3_realloc(arEstLoop, sizeof(arEstLoop[0])*(iSid+1) );
      while( nEstLoop<=iSid ) arEstLoop[nEstLoop++] = 1.0; 
    }
    if( iSid>=0 ){
      arEstLoop[iSid] *= rEst;
      rLoop = arEstLoop[iSid];
    }else{
      rLoop = rEst;
    }
    fprintf(pArg->out, "Loop %2d: \"%s\"\n", i, zExplain);

    fprintf(pArg->out, "        nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
        nLoop, nVisit, (sqlite3_int64)rLoop, rEst
    );
  }
  sqlite3_free(arEstLoop);

#else
  fprintf(pArg->out, "-------- scanstats --------\n");
  fprintf(pArg->out,
      "sqlite3_stmt_scanstatus() unavailable - "
      "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
  );
#endif








|
<
<

>
>
|
|
|
|
|
|
|
|
|
|
>
>
>
|
|
|
<
<
<
<
<
<
<
<
<
<
|
>
|
|
|
|
<
>








1190
1191
1192
1193
1194
1195
1196
1197


1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216










1217
1218
1219
1220
1221
1222

1223
1224
1225
1226
1227
1228
1229
1230

** Display scan stats.
*/
static void display_scanstats(
  sqlite3 *db,                    /* Database to query */
  ShellState *pArg                /* Pointer to ShellState */
){
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int i, k, n = 1;


  fprintf(pArg->out, "-------- scanstats --------\n");
  for(k=0; n>0; k++){
    double rEstLoop = 1.0;
    for(i=n=0; 1; i++){
      sqlite3_stmt *p = pArg->pStmt;
      sqlite3_int64 nLoop, nVisit;
      double rEst;
      int iSid;
      const char *zExplain;
      if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
        break;
      }
      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
      if( iSid!=k ) continue;
      if( n==0 && k>0 ) fprintf(pArg->out, "-------- subquery %d --------\n", k);
      n++;
      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst);
      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);










      fprintf(pArg->out, "Loop %2d: %s\n", n, zExplain);
      rEstLoop *= rEst;
      fprintf(pArg->out, "         nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
          nLoop, nVisit, (sqlite3_int64)rEstLoop, rEst
      );
    }

  }
#else
  fprintf(pArg->out, "-------- scanstats --------\n");
  fprintf(pArg->out,
      "sqlite3_stmt_scanstatus() unavailable - "
      "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
  );
#endif
︙︙