Fossil

Check-in [f044cf2a]
Login

Check-in [f044cf2a]

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

Overview
Comment:More aggressive reuse of prepared statements for improved performance.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f044cf2a91b5906fa0ffc82de961b4d124910c37ffc4024628e9f1879f152c4b
User & Date: drh 2020-11-21 19:46:36
Notes On How To Locate SQL Statements That Are Candidates For Reuse

With this check-in, the --sqltrace option adds a comment at the end of each SQL statement of the form "Nth run" where N is the number of prior executions of the same statement. If you capture the --sqltrace output (which is on stderr instead of stdout) you can then search for all "1st run" queries and look for duplicates.

   ./fossil test-http --sqltrace <some-request.txt 2>sql-out.txt
   grep ' 1st run' sql-out.txt | sort | open -f
Context
2020-11-21
21:04
Performance optimization in the htmlize() utility routine. ... (check-in: 4c1d38f9 user: drh tags: trunk)
19:46
More aggressive reuse of prepared statements for improved performance. ... (check-in: f044cf2a user: drh tags: trunk)
14:34
Further performance improvements to the internal printf() implementation. ... (check-in: 3ad620df user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/db.c.

2774
2775
2776
2777
2778
2779
2780


2781

2782
2783
2784
2785
2786
2787
2788
    }
    return 0;
  }
  if( zArg[0]=='-' ) return 0;
  if( m & SQLITE_TRACE_PROFILE ){
    sqlite3_int64 nNano = *(sqlite3_int64*)pX;
    double rMillisec = 0.000001 * nNano;


    sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms */\n", rMillisec);

  }else{
    zEnd[0] = '\n';
    zEnd[1] = 0;
  }
  zSql = sqlite3_expanded_sql(pStmt);
  n = (int)strlen(zSql);
  fossil_trace("%s%s%s", zSql, (n>0 && zSql[n-1]==';') ? "" : ";", zEnd);







>
>
|
>







2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
    }
    return 0;
  }
  if( zArg[0]=='-' ) return 0;
  if( m & SQLITE_TRACE_PROFILE ){
    sqlite3_int64 nNano = *(sqlite3_int64*)pX;
    double rMillisec = 0.000001 * nNano;
    int nRun = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_RUN, 0);
    int nVmStep = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_VM_STEP, 1);
    sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms, %r run, %d vm-steps */\n",
        rMillisec, nRun, nVmStep);
  }else{
    zEnd[0] = '\n';
    zEnd[1] = 0;
  }
  zSql = sqlite3_expanded_sql(pStmt);
  n = (int)strlen(zSql);
  fossil_trace("%s%s%s", zSql, (n>0 && zSql[n-1]==';') ? "" : ";", zEnd);
3087
3088
3089
3090
3091
3092
3093


3094



3095


3096


3097
3098
3099





3100
3101
3102
3103
3104
3105
3106
** setting is returned instead.  If zName is a versioned setting, then
** versioned value takes priority.
*/
char *db_get(const char *zName, const char *zDefault){
  char *z = 0;
  const Setting *pSetting = db_find_setting(zName, 0);
  if( g.repositoryOpen ){


    z = db_text(0, "SELECT value FROM config WHERE name=%Q", zName);



  }


  if( z==0 && g.zConfigDbName ){


    db_swap_connections();
    z = db_text(0, "SELECT value FROM global_config WHERE name=%Q", zName);
    db_swap_connections();





  }
  if( pSetting!=0 && pSetting->versionable ){
    /* This is a versionable setting, try and get the info from a
    ** checked out file */
    char * zZ = z;
    z = db_get_versioned(zName, z);
    if(zZ != z){







>
>
|
>
>
>
|
>
>

>
>

|

>
>
>
>
>







3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
** setting is returned instead.  If zName is a versioned setting, then
** versioned value takes priority.
*/
char *db_get(const char *zName, const char *zDefault){
  char *z = 0;
  const Setting *pSetting = db_find_setting(zName, 0);
  if( g.repositoryOpen ){
    static Stmt q1;
    const char *zRes;
    db_static_prepare(&q1, "SELECT value FROM config WHERE name=$n");
    db_bind_text(&q1, "$n", zName);
    if( db_step(&q1)==SQLITE_ROW && (zRes = db_column_text(&q1,0))!=0 ){
      z = fossil_strdup(zRes);
    }
    db_reset(&q1);
  }
  if( z==0 && g.zConfigDbName ){
    static Stmt q2;
    const char *zRes;
    db_swap_connections();
    db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
    db_swap_connections();
    db_bind_text(&q2, "$n", zName);
    if( db_step(&q2)==SQLITE_ROW && (zRes = db_column_text(&q2,0))!=0 ){
      z = fossil_strdup(zRes);
    }
    db_reset(&q2);
  }
  if( pSetting!=0 && pSetting->versionable ){
    /* This is a versionable setting, try and get the info from a
    ** checked out file */
    char * zZ = z;
    z = db_get_versioned(zName, z);
    if(zZ != z){
3172
3173
3174
3175
3176
3177
3178
3179
3180

3181
3182
3183
3184
3185
3186
3187
3188
3189

3190
3191
3192





3193
3194
3195
3196
3197
3198
3199
  }
  return rc;
}
int db_get_int(const char *zName, int dflt){
  int v = dflt;
  int rc;
  if( g.repositoryOpen ){
    Stmt q;
    db_prepare(&q, "SELECT value FROM config WHERE name=%Q", zName);

    rc = db_step(&q);
    if( rc==SQLITE_ROW ){
      v = db_column_int(&q, 0);
    }
    db_finalize(&q);
  }else{
    rc = SQLITE_DONE;
  }
  if( rc==SQLITE_DONE && g.zConfigDbName ){

    db_swap_connections();
    v = db_int(dflt, "SELECT value FROM global_config WHERE name=%Q", zName);
    db_swap_connections();





  }
  return v;
}
void db_set_int(const char *zName, int value, int globalFlag){
  db_assert_protection_off_or_not_sensitive(zName);
  db_unprotect(PROTECT_CONFIG);
  if( globalFlag ){







|
|
>




|




>

|

>
>
>
>
>







3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
  }
  return rc;
}
int db_get_int(const char *zName, int dflt){
  int v = dflt;
  int rc;
  if( g.repositoryOpen ){
    static Stmt q;
    db_static_prepare(&q, "SELECT value FROM config WHERE name=$n");
    db_bind_text(&q, "$n", zName);
    rc = db_step(&q);
    if( rc==SQLITE_ROW ){
      v = db_column_int(&q, 0);
    }
    db_reset(&q);
  }else{
    rc = SQLITE_DONE;
  }
  if( rc==SQLITE_DONE && g.zConfigDbName ){
    static Stmt q2;
    db_swap_connections();
    db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
    db_swap_connections();
    db_bind_text(&q2, "$n", zName);
    if( db_step(&q2)==SQLITE_ROW ){
      v = db_column_int(&q2, 0);
    }
    db_reset(&q2);
  }
  return v;
}
void db_set_int(const char *zName, int value, int globalFlag){
  db_assert_protection_off_or_not_sensitive(zName);
  db_unprotect(PROTECT_CONFIG);
  if( globalFlag ){

Changes to src/interwiki.c.

72
73
74
75
76
77
78

79
80
81
82
83
84
85
86
87
88
89
90

91

92
93
94
95
96
97
98
*/
char *interwiki_url(const char *zTarget){
  int nCode;
  int i;
  const char *zPage;
  int nPage;
  char *zUrl = 0;

  Stmt q;
  for(i=0; fossil_isalnum(zTarget[i]); i++){}
  if( zTarget[i]!=':' ) return 0;
  nCode = i;
  if( nCode==4 && strncmp(zTarget,"wiki",4)==0 ) return 0;
  zPage = zTarget + nCode + 1;
  nPage = (int)strlen(zPage);
  db_prepare(&q, 
     "SELECT json_extract(value,'$.base'),"
           " json_extract(value,'$.hash'),"
           " json_extract(value,'$.wiki')"
     " FROM config WHERE name=lower('interwiki:%.*q')",

     nCode, zTarget);

  while( db_step(&q)==SQLITE_ROW ){
    const char *zBase = db_column_text(&q,0);
    if( zBase==0 || zBase[0]==0 ) break;
    if( nPage==0 || zPage[0]=='/' ){
      /* Path */
      zUrl = mprintf("%s%s", zBase, zPage);
    }else if( nPage>=4 && validate16(zPage,nPage) ){







>
|






|



|
>
|
>







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
*/
char *interwiki_url(const char *zTarget){
  int nCode;
  int i;
  const char *zPage;
  int nPage;
  char *zUrl = 0;
  char *zName;
  static Stmt q;
  for(i=0; fossil_isalnum(zTarget[i]); i++){}
  if( zTarget[i]!=':' ) return 0;
  nCode = i;
  if( nCode==4 && strncmp(zTarget,"wiki",4)==0 ) return 0;
  zPage = zTarget + nCode + 1;
  nPage = (int)strlen(zPage);
  db_static_prepare(&q, 
     "SELECT json_extract(value,'$.base'),"
           " json_extract(value,'$.hash'),"
           " json_extract(value,'$.wiki')"
     " FROM config WHERE name=lower($name)"
  );
  zName = mprintf("interwiki:%.*s", nCode, zTarget);
  db_bind_text(&q, "$name", zName);
  while( db_step(&q)==SQLITE_ROW ){
    const char *zBase = db_column_text(&q,0);
    if( zBase==0 || zBase[0]==0 ) break;
    if( nPage==0 || zPage[0]=='/' ){
      /* Path */
      zUrl = mprintf("%s%s", zBase, zPage);
    }else if( nPage>=4 && validate16(zPage,nPage) ){
106
107
108
109
110
111
112
113

114
115
116
117
118
119
120
      const char *zWiki = db_column_text(&q,2);
      if( zWiki && zWiki[0] ){
        zUrl = mprintf("%s%s%s", zBase, zWiki, zPage);
      }
    }
    break;
  }
  db_finalize(&q);

  return zUrl;
}

/*
** If hyperlink target zTarget begins with an interwiki tag that ought
** to be excluded from display, then return the number of characters in
** that tag.







|
>







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
      const char *zWiki = db_column_text(&q,2);
      if( zWiki && zWiki[0] ){
        zUrl = mprintf("%s%s%s", zBase, zWiki, zPage);
      }
    }
    break;
  }
  db_reset(&q);
  free(zName);
  return zUrl;
}

/*
** If hyperlink target zTarget begins with an interwiki tag that ought
** to be excluded from display, then return the number of characters in
** that tag.

Changes to src/timeline.c.

222
223
224
225
226
227
228
















229
230
231
232
233
234
235
/*
** Return a new timelineTable id.
*/
int timeline_tableid(void){
  static int id = 0;
  return id++;
}

















/*
** Output a timeline in the web format given a query.  The query
** should return these columns:
**
**    0.  rid
**    1.  artifact hash







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
/*
** Return a new timelineTable id.
*/
int timeline_tableid(void){
  static int id = 0;
  return id++;
}

/*
** Return true if the checking identified by "rid" has a valid "closed"
** tag.
*/
static int has_closed_tag(int rid){
  static Stmt q;
  int res = 0;
  db_static_prepare(&q,
     "SELECT 1 FROM tagxref WHERE rid=$rid AND tagid=%d AND tagtype>0",
     TAG_CLOSED);
  db_bind_int(&q, "$rid", rid);
  res = db_step(&q)==SQLITE_ROW;
  db_reset(&q);
  return res;
}

/*
** Output a timeline in the web format given a query.  The query
** should return these columns:
**
**    0.  rid
**    1.  artifact hash
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
    }else{
      @ <span class='timeline%s(zStyle)Comment'>
    }
    if( (tmFlags & TIMELINE_CLASSIC)!=0 ){
      if( zType[0]=='c' ){
        hyperlink_to_version(zUuid);
        if( isLeaf ){
          if( db_exists("SELECT 1 FROM tagxref"
                        " WHERE rid=%d AND tagid=%d AND tagtype>0",
                        rid, TAG_CLOSED) ){
            @ <span class="timelineLeaf">Closed-Leaf:</span>
          }else{
            @ <span class="timelineLeaf">Leaf:</span>
          }
        }
      }else if( zType[0]=='e' && tagid ){
        hyperlink_to_event_tagid(tagid<0?-tagid:tagid);







|
<
<







568
569
570
571
572
573
574
575


576
577
578
579
580
581
582
    }else{
      @ <span class='timeline%s(zStyle)Comment'>
    }
    if( (tmFlags & TIMELINE_CLASSIC)!=0 ){
      if( zType[0]=='c' ){
        hyperlink_to_version(zUuid);
        if( isLeaf ){
          if( has_closed_tag(rid) ){


            @ <span class="timelineLeaf">Closed-Leaf:</span>
          }else{
            @ <span class="timelineLeaf">Leaf:</span>
          }
        }
      }else if( zType[0]=='e' && tagid ){
        hyperlink_to_event_tagid(tagid<0?-tagid:tagid);
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
    if( (tmFlags & (TIMELINE_CLASSIC|TIMELINE_VERBOSE|TIMELINE_COMPACT))!=0 ){
      cgi_printf("(");
    }

    if( (tmFlags & TIMELINE_CLASSIC)==0 ){
      if( zType[0]=='c' ){
        if( isLeaf ){
          if( db_exists("SELECT 1 FROM tagxref"
                        " WHERE rid=%d AND tagid=%d AND tagtype>0",
                        rid, TAG_CLOSED) ){
            @ <span class='timelineLeaf'>Closed-Leaf</span>
          }else{
            @ <span class='timelineLeaf'>Leaf</span>
          }
        }
        cgi_printf("check-in:&nbsp;%z%S</a> ",href("%R/info/%!S",zUuid),zUuid);
      }else if( zType[0]=='e' && tagid ){







|
<
<







667
668
669
670
671
672
673
674


675
676
677
678
679
680
681
    if( (tmFlags & (TIMELINE_CLASSIC|TIMELINE_VERBOSE|TIMELINE_COMPACT))!=0 ){
      cgi_printf("(");
    }

    if( (tmFlags & TIMELINE_CLASSIC)==0 ){
      if( zType[0]=='c' ){
        if( isLeaf ){
          if( has_closed_tag(rid) ){


            @ <span class='timelineLeaf'>Closed-Leaf</span>
          }else{
            @ <span class='timelineLeaf'>Leaf</span>
          }
        }
        cgi_printf("check-in:&nbsp;%z%S</a> ",href("%R/info/%!S",zUuid),zUuid);
      }else if( zType[0]=='e' && tagid ){