Fossil SCM

Add additional markups to show inefficient queries when running in TH3_DEBUG mode.

drh 2010-04-02 19:28 trunk
Commit 251fd001a8c6e7af99500a8ca3ac38ad95577995
Parent 4bbb437f06ec406…
2 files changed +12 -8 +2 -2
~ src/db.c ~ src/vfile.c
M src/db.c
+12 -8
--- src/db.c
+++ src/db.c
@@ -54,10 +54,11 @@
5454
 */
5555
 struct Stmt {
5656
 Blob sql; /* The SQL for this statement */
5757
 sqlite3_stmt *pStmt; /* The results of sqlite3_prepare() */
5858
 Stmt *pNext, *pPrev; /* List of all unfinalized statements */
59
+ int nStep; /* Number of sqlite3_step() calls */
5960
 };
6061
 #endif /* INTERFACE */
6162
6263
 /*
6364
 ** Call this routine when a database error occurs.
@@ -197,10 +198,11 @@
197198
 zSql = blob_str(&pStmt->sql);
198199
 if( sqlite3_prepare_v2(g.db, zSql, -1, &pStmt->pStmt, 0)!=0 ){
199200
 db_err("%s\n%s", sqlite3_errmsg(g.db), zSql);
200201
 }
201202
 pStmt->pNext = pStmt->pPrev = 0;
203
+ pStmt->nStep = 0;
202204
 return 0;
203205
 }
204206
 int db_prepare(Stmt *pStmt, const char *zFormat, ...){
205207
 int rc;
206208
 va_list ap;
@@ -272,44 +274,46 @@
272274
 ** or SQLITE_OK if the statement finishes successfully.
273275
 */
274276
 int db_step(Stmt *pStmt){
275277
 int rc;
276278
 rc = sqlite3_step(pStmt->pStmt);
279
+ pStmt->nStep++;
277280
 return rc;
278281
 }
279282
280283
 /*
281284
 ** Print warnings if a query is inefficient.
282285
 */
283
-static void db_stats(sqlite3_stmt *pStmt){
286
+static void db_stats(Stmt *pStmt){
284287
 #ifdef FOSSIL_DEBUG
285288
 int c1, c2;
286
- const char *zSql = sqlite3_sql(pStmt);
289
+ const char *zSql = sqlite3_sql(pStmt->pStmt);
287290
 if( zSql==0 ) return;
288
- c1 = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, 1);
289
- c2 = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_SORT, 1);
290
- if( c1>5 && strstr(zSql,"/*scan*/")==0 ){
291
- fossil_warning("%d scan steps in [%s]", c1, zSql);
291
+ c1 = sqlite3_stmt_status(pStmt->pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, 1);
292
+ c2 = sqlite3_stmt_status(pStmt->pStmt, SQLITE_STMTSTATUS_SORT, 1);
293
+ if( c1>pStmt->nStep*4 && strstr(zSql,"/*scan*/")==0 ){
294
+ fossil_warning("%d scan steps for %d rows in [%s]", c1, pStmt->nStep, zSql);
292295
 }else if( c2 && strstr(zSql,"/*sort*/")==0 && strstr(zSql,"/*scan*/")==0 ){
293296
 fossil_warning("sort w/o index in [%s]", zSql);
294297
 }
298
+ pStmt->nStep = 0;
295299
 #endif
296300
 }
297301
298302
 /*
299303
 ** Reset or finalize a statement.
300304
 */
301305
 int db_reset(Stmt *pStmt){
302306
 int rc;
303
- db_stats(pStmt->pStmt);
307
+ db_stats(pStmt);
304308
 rc = sqlite3_reset(pStmt->pStmt);
305309
 db_check_result(rc);
306310
 return rc;
307311
 }
308312
 int db_finalize(Stmt *pStmt){
309313
 int rc;
310
- db_stats(pStmt->pStmt);
314
+ db_stats(pStmt);
311315
 blob_reset(&pStmt->sql);
312316
 rc = sqlite3_finalize(pStmt->pStmt);
313317
 db_check_result(rc);
314318
 pStmt->pStmt = 0;
315319
 if( pStmt->pNext ){
316320
--- src/db.c
+++ src/db.c
@@ -54,10 +54,11 @@
54 */
55 struct Stmt {
56 Blob sql; /* The SQL for this statement */
57 sqlite3_stmt *pStmt; /* The results of sqlite3_prepare() */
58 Stmt *pNext, *pPrev; /* List of all unfinalized statements */
 
59 };
60 #endif /* INTERFACE */
61
62 /*
63 ** Call this routine when a database error occurs.
@@ -197,10 +198,11 @@
197 zSql = blob_str(&pStmt->sql);
198 if( sqlite3_prepare_v2(g.db, zSql, -1, &pStmt->pStmt, 0)!=0 ){
199 db_err("%s\n%s", sqlite3_errmsg(g.db), zSql);
200 }
201 pStmt->pNext = pStmt->pPrev = 0;
 
202 return 0;
203 }
204 int db_prepare(Stmt *pStmt, const char *zFormat, ...){
205 int rc;
206 va_list ap;
@@ -272,44 +274,46 @@
272 ** or SQLITE_OK if the statement finishes successfully.
273 */
274 int db_step(Stmt *pStmt){
275 int rc;
276 rc = sqlite3_step(pStmt->pStmt);
 
277 return rc;
278 }
279
280 /*
281 ** Print warnings if a query is inefficient.
282 */
283 static void db_stats(sqlite3_stmt *pStmt){
284 #ifdef FOSSIL_DEBUG
285 int c1, c2;
286 const char *zSql = sqlite3_sql(pStmt);
287 if( zSql==0 ) return;
288 c1 = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, 1);
289 c2 = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_SORT, 1);
290 if( c1>5 && strstr(zSql,"/*scan*/")==0 ){
291 fossil_warning("%d scan steps in [%s]", c1, zSql);
292 }else if( c2 && strstr(zSql,"/*sort*/")==0 && strstr(zSql,"/*scan*/")==0 ){
293 fossil_warning("sort w/o index in [%s]", zSql);
294 }
 
295 #endif
296 }
297
298 /*
299 ** Reset or finalize a statement.
300 */
301 int db_reset(Stmt *pStmt){
302 int rc;
303 db_stats(pStmt->pStmt);
304 rc = sqlite3_reset(pStmt->pStmt);
305 db_check_result(rc);
306 return rc;
307 }
308 int db_finalize(Stmt *pStmt){
309 int rc;
310 db_stats(pStmt->pStmt);
311 blob_reset(&pStmt->sql);
312 rc = sqlite3_finalize(pStmt->pStmt);
313 db_check_result(rc);
314 pStmt->pStmt = 0;
315 if( pStmt->pNext ){
316
--- src/db.c
+++ src/db.c
@@ -54,10 +54,11 @@
54 */
55 struct Stmt {
56 Blob sql; /* The SQL for this statement */
57 sqlite3_stmt *pStmt; /* The results of sqlite3_prepare() */
58 Stmt *pNext, *pPrev; /* List of all unfinalized statements */
59 int nStep; /* Number of sqlite3_step() calls */
60 };
61 #endif /* INTERFACE */
62
63 /*
64 ** Call this routine when a database error occurs.
@@ -197,10 +198,11 @@
198 zSql = blob_str(&pStmt->sql);
199 if( sqlite3_prepare_v2(g.db, zSql, -1, &pStmt->pStmt, 0)!=0 ){
200 db_err("%s\n%s", sqlite3_errmsg(g.db), zSql);
201 }
202 pStmt->pNext = pStmt->pPrev = 0;
203 pStmt->nStep = 0;
204 return 0;
205 }
206 int db_prepare(Stmt *pStmt, const char *zFormat, ...){
207 int rc;
208 va_list ap;
@@ -272,44 +274,46 @@
274 ** or SQLITE_OK if the statement finishes successfully.
275 */
276 int db_step(Stmt *pStmt){
277 int rc;
278 rc = sqlite3_step(pStmt->pStmt);
279 pStmt->nStep++;
280 return rc;
281 }
282
283 /*
284 ** Print warnings if a query is inefficient.
285 */
286 static void db_stats(Stmt *pStmt){
287 #ifdef FOSSIL_DEBUG
288 int c1, c2;
289 const char *zSql = sqlite3_sql(pStmt->pStmt);
290 if( zSql==0 ) return;
291 c1 = sqlite3_stmt_status(pStmt->pStmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, 1);
292 c2 = sqlite3_stmt_status(pStmt->pStmt, SQLITE_STMTSTATUS_SORT, 1);
293 if( c1>pStmt->nStep*4 && strstr(zSql,"/*scan*/")==0 ){
294 fossil_warning("%d scan steps for %d rows in [%s]", c1, pStmt->nStep, zSql);
295 }else if( c2 && strstr(zSql,"/*sort*/")==0 && strstr(zSql,"/*scan*/")==0 ){
296 fossil_warning("sort w/o index in [%s]", zSql);
297 }
298 pStmt->nStep = 0;
299 #endif
300 }
301
302 /*
303 ** Reset or finalize a statement.
304 */
305 int db_reset(Stmt *pStmt){
306 int rc;
307 db_stats(pStmt);
308 rc = sqlite3_reset(pStmt->pStmt);
309 db_check_result(rc);
310 return rc;
311 }
312 int db_finalize(Stmt *pStmt){
313 int rc;
314 db_stats(pStmt);
315 blob_reset(&pStmt->sql);
316 rc = sqlite3_finalize(pStmt->pStmt);
317 db_check_result(rc);
318 pStmt->pStmt = 0;
319 if( pStmt->pNext ){
320
M src/vfile.c
+2 -2
--- src/vfile.c
+++ src/vfile.c
@@ -324,11 +324,11 @@
324324
325325
 db_must_be_within_tree();
326326
 db_prepare(&q,
327327
 "SELECT %Q || pathname, pathname, file_is_selected(id), rid FROM vfile"
328328
 " WHERE NOT deleted AND vid=%d"
329
- " ORDER BY pathname",
329
+ " ORDER BY pathname /*scan*/",
330330
 g.zLocalRoot, vid
331331
 );
332332
 md5sum_init();
333333
 while( db_step(&q)==SQLITE_ROW ){
334334
 const char *zFullpath = db_column_text(&q, 0);
@@ -386,11 +386,11 @@
386386
387387
 db_must_be_within_tree();
388388
389389
 db_prepare(&q, "SELECT pathname, rid FROM vfile"
390390
 " WHERE NOT deleted AND rid>0 AND vid=%d"
391
- " ORDER BY pathname",
391
+ " ORDER BY pathname /*scan*/",
392392
 vid);
393393
 blob_zero(&file);
394394
 md5sum_init();
395395
 while( db_step(&q)==SQLITE_ROW ){
396396
 const char *zName = db_column_text(&q, 0);
397397
--- src/vfile.c
+++ src/vfile.c
@@ -324,11 +324,11 @@
324
325 db_must_be_within_tree();
326 db_prepare(&q,
327 "SELECT %Q || pathname, pathname, file_is_selected(id), rid FROM vfile"
328 " WHERE NOT deleted AND vid=%d"
329 " ORDER BY pathname",
330 g.zLocalRoot, vid
331 );
332 md5sum_init();
333 while( db_step(&q)==SQLITE_ROW ){
334 const char *zFullpath = db_column_text(&q, 0);
@@ -386,11 +386,11 @@
386
387 db_must_be_within_tree();
388
389 db_prepare(&q, "SELECT pathname, rid FROM vfile"
390 " WHERE NOT deleted AND rid>0 AND vid=%d"
391 " ORDER BY pathname",
392 vid);
393 blob_zero(&file);
394 md5sum_init();
395 while( db_step(&q)==SQLITE_ROW ){
396 const char *zName = db_column_text(&q, 0);
397
--- src/vfile.c
+++ src/vfile.c
@@ -324,11 +324,11 @@
324
325 db_must_be_within_tree();
326 db_prepare(&q,
327 "SELECT %Q || pathname, pathname, file_is_selected(id), rid FROM vfile"
328 " WHERE NOT deleted AND vid=%d"
329 " ORDER BY pathname /*scan*/",
330 g.zLocalRoot, vid
331 );
332 md5sum_init();
333 while( db_step(&q)==SQLITE_ROW ){
334 const char *zFullpath = db_column_text(&q, 0);
@@ -386,11 +386,11 @@
386
387 db_must_be_within_tree();
388
389 db_prepare(&q, "SELECT pathname, rid FROM vfile"
390 " WHERE NOT deleted AND rid>0 AND vid=%d"
391 " ORDER BY pathname /*scan*/",
392 vid);
393 blob_zero(&file);
394 md5sum_init();
395 while( db_step(&q)==SQLITE_ROW ){
396 const char *zName = db_column_text(&q, 0);
397

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button