Fossil SCM

More aggressive reuse of prepared statements for improved performance.

drh 2020-11-21 19:46 trunk
Commit f044cf2a91b5906fa0ffc82de961b4d124910c37ffc4024628e9f1879f152c4b
Parent 3ad620df0058144…
3 files changed +31 -7 +9 -5 +18 -6
~ src/db.c ~ src/interwiki.c ~ src/timeline.c
M src/db.c
+31 -7
--- src/db.c
+++ src/db.c
@@ -2776,11 +2776,14 @@
27762776
 }
27772777
 if( zArg[0]=='-' ) return 0;
27782778
 if( m & SQLITE_TRACE_PROFILE ){
27792779
 sqlite3_int64 nNano = *(sqlite3_int64*)pX;
27802780
 double rMillisec = 0.000001 * nNano;
2781
- sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms */\n", rMillisec);
2781
+ int nRun = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_RUN, 0);
2782
+ int nVmStep = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_VM_STEP, 1);
2783
+ sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms, %r run, %d vm-steps */\n",
2784
+ rMillisec, nRun, nVmStep);
27822785
 }else{
27832786
 zEnd[0] = '\n';
27842787
 zEnd[1] = 0;
27852788
 }
27862789
 zSql = sqlite3_expanded_sql(pStmt);
@@ -3089,16 +3092,30 @@
30893092
 */
30903093
 char *db_get(const char *zName, const char *zDefault){
30913094
 char *z = 0;
30923095
 const Setting *pSetting = db_find_setting(zName, 0);
30933096
 if( g.repositoryOpen ){
3094
- z = db_text(0, "SELECT value FROM config WHERE name=%Q", zName);
3097
+ static Stmt q1;
3098
+ const char *zRes;
3099
+ db_static_prepare(&q1, "SELECT value FROM config WHERE name=$n");
3100
+ db_bind_text(&q1, "$n", zName);
3101
+ if( db_step(&q1)==SQLITE_ROW && (zRes = db_column_text(&q1,0))!=0 ){
3102
+ z = fossil_strdup(zRes);
3103
+ }
3104
+ db_reset(&q1);
30953105
 }
30963106
 if( z==0 && g.zConfigDbName ){
3107
+ static Stmt q2;
3108
+ const char *zRes;
30973109
 db_swap_connections();
3098
- z = db_text(0, "SELECT value FROM global_config WHERE name=%Q", zName);
3110
+ db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
30993111
 db_swap_connections();
3112
+ db_bind_text(&q2, "$n", zName);
3113
+ if( db_step(&q2)==SQLITE_ROW && (zRes = db_column_text(&q2,0))!=0 ){
3114
+ z = fossil_strdup(zRes);
3115
+ }
3116
+ db_reset(&q2);
31003117
 }
31013118
 if( pSetting!=0 && pSetting->versionable ){
31023119
 /* This is a versionable setting, try and get the info from a
31033120
 ** checked out file */
31043121
 char * zZ = z;
@@ -3174,24 +3191,31 @@
31743191
 }
31753192
 int db_get_int(const char *zName, int dflt){
31763193
 int v = dflt;
31773194
 int rc;
31783195
 if( g.repositoryOpen ){
3179
- Stmt q;
3180
- db_prepare(&q, "SELECT value FROM config WHERE name=%Q", zName);
3196
+ static Stmt q;
3197
+ db_static_prepare(&q, "SELECT value FROM config WHERE name=$n");
3198
+ db_bind_text(&q, "$n", zName);
31813199
 rc = db_step(&q);
31823200
 if( rc==SQLITE_ROW ){
31833201
 v = db_column_int(&q, 0);
31843202
 }
3185
- db_finalize(&q);
3203
+ db_reset(&q);
31863204
 }else{
31873205
 rc = SQLITE_DONE;
31883206
 }
31893207
 if( rc==SQLITE_DONE && g.zConfigDbName ){
3208
+ static Stmt q2;
31903209
 db_swap_connections();
3191
- v = db_int(dflt, "SELECT value FROM global_config WHERE name=%Q", zName);
3210
+ db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
31923211
 db_swap_connections();
3212
+ db_bind_text(&q2, "$n", zName);
3213
+ if( db_step(&q2)==SQLITE_ROW ){
3214
+ v = db_column_int(&q2, 0);
3215
+ }
3216
+ db_reset(&q2);
31933217
 }
31943218
 return v;
31953219
 }
31963220
 void db_set_int(const char *zName, int value, int globalFlag){
31973221
 db_assert_protection_off_or_not_sensitive(zName);
31983222
--- src/db.c
+++ src/db.c
@@ -2776,11 +2776,14 @@
2776 }
2777 if( zArg[0]=='-' ) return 0;
2778 if( m & SQLITE_TRACE_PROFILE ){
2779 sqlite3_int64 nNano = *(sqlite3_int64*)pX;
2780 double rMillisec = 0.000001 * nNano;
2781 sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms */\n", rMillisec);
 
 
 
2782 }else{
2783 zEnd[0] = '\n';
2784 zEnd[1] = 0;
2785 }
2786 zSql = sqlite3_expanded_sql(pStmt);
@@ -3089,16 +3092,30 @@
3089 */
3090 char *db_get(const char *zName, const char *zDefault){
3091 char *z = 0;
3092 const Setting *pSetting = db_find_setting(zName, 0);
3093 if( g.repositoryOpen ){
3094 z = db_text(0, "SELECT value FROM config WHERE name=%Q", zName);
 
 
 
 
 
 
 
3095 }
3096 if( z==0 && g.zConfigDbName ){
 
 
3097 db_swap_connections();
3098 z = db_text(0, "SELECT value FROM global_config WHERE name=%Q", zName);
3099 db_swap_connections();
 
 
 
 
 
3100 }
3101 if( pSetting!=0 && pSetting->versionable ){
3102 /* This is a versionable setting, try and get the info from a
3103 ** checked out file */
3104 char * zZ = z;
@@ -3174,24 +3191,31 @@
3174 }
3175 int db_get_int(const char *zName, int dflt){
3176 int v = dflt;
3177 int rc;
3178 if( g.repositoryOpen ){
3179 Stmt q;
3180 db_prepare(&q, "SELECT value FROM config WHERE name=%Q", zName);
 
3181 rc = db_step(&q);
3182 if( rc==SQLITE_ROW ){
3183 v = db_column_int(&q, 0);
3184 }
3185 db_finalize(&q);
3186 }else{
3187 rc = SQLITE_DONE;
3188 }
3189 if( rc==SQLITE_DONE && g.zConfigDbName ){
 
3190 db_swap_connections();
3191 v = db_int(dflt, "SELECT value FROM global_config WHERE name=%Q", zName);
3192 db_swap_connections();
 
 
 
 
 
3193 }
3194 return v;
3195 }
3196 void db_set_int(const char *zName, int value, int globalFlag){
3197 db_assert_protection_off_or_not_sensitive(zName);
3198
--- src/db.c
+++ src/db.c
@@ -2776,11 +2776,14 @@
2776 }
2777 if( zArg[0]=='-' ) return 0;
2778 if( m & SQLITE_TRACE_PROFILE ){
2779 sqlite3_int64 nNano = *(sqlite3_int64*)pX;
2780 double rMillisec = 0.000001 * nNano;
2781 int nRun = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_RUN, 0);
2782 int nVmStep = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_VM_STEP, 1);
2783 sqlite3_snprintf(sizeof(zEnd),zEnd," /* %.3fms, %r run, %d vm-steps */\n",
2784 rMillisec, nRun, nVmStep);
2785 }else{
2786 zEnd[0] = '\n';
2787 zEnd[1] = 0;
2788 }
2789 zSql = sqlite3_expanded_sql(pStmt);
@@ -3089,16 +3092,30 @@
3092 */
3093 char *db_get(const char *zName, const char *zDefault){
3094 char *z = 0;
3095 const Setting *pSetting = db_find_setting(zName, 0);
3096 if( g.repositoryOpen ){
3097 static Stmt q1;
3098 const char *zRes;
3099 db_static_prepare(&q1, "SELECT value FROM config WHERE name=$n");
3100 db_bind_text(&q1, "$n", zName);
3101 if( db_step(&q1)==SQLITE_ROW && (zRes = db_column_text(&q1,0))!=0 ){
3102 z = fossil_strdup(zRes);
3103 }
3104 db_reset(&q1);
3105 }
3106 if( z==0 && g.zConfigDbName ){
3107 static Stmt q2;
3108 const char *zRes;
3109 db_swap_connections();
3110 db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
3111 db_swap_connections();
3112 db_bind_text(&q2, "$n", zName);
3113 if( db_step(&q2)==SQLITE_ROW && (zRes = db_column_text(&q2,0))!=0 ){
3114 z = fossil_strdup(zRes);
3115 }
3116 db_reset(&q2);
3117 }
3118 if( pSetting!=0 && pSetting->versionable ){
3119 /* This is a versionable setting, try and get the info from a
3120 ** checked out file */
3121 char * zZ = z;
@@ -3174,24 +3191,31 @@
3191 }
3192 int db_get_int(const char *zName, int dflt){
3193 int v = dflt;
3194 int rc;
3195 if( g.repositoryOpen ){
3196 static Stmt q;
3197 db_static_prepare(&q, "SELECT value FROM config WHERE name=$n");
3198 db_bind_text(&q, "$n", zName);
3199 rc = db_step(&q);
3200 if( rc==SQLITE_ROW ){
3201 v = db_column_int(&q, 0);
3202 }
3203 db_reset(&q);
3204 }else{
3205 rc = SQLITE_DONE;
3206 }
3207 if( rc==SQLITE_DONE && g.zConfigDbName ){
3208 static Stmt q2;
3209 db_swap_connections();
3210 db_static_prepare(&q2, "SELECT value FROM global_config WHERE name=$n");
3211 db_swap_connections();
3212 db_bind_text(&q2, "$n", zName);
3213 if( db_step(&q2)==SQLITE_ROW ){
3214 v = db_column_int(&q2, 0);
3215 }
3216 db_reset(&q2);
3217 }
3218 return v;
3219 }
3220 void db_set_int(const char *zName, int value, int globalFlag){
3221 db_assert_protection_off_or_not_sensitive(zName);
3222
M src/interwiki.c
+9 -5
--- src/interwiki.c
+++ src/interwiki.c
@@ -74,23 +74,26 @@
7474
 int nCode;
7575
 int i;
7676
 const char *zPage;
7777
 int nPage;
7878
 char *zUrl = 0;
79
- Stmt q;
79
+ char *zName;
80
+ static Stmt q;
8081
 for(i=0; fossil_isalnum(zTarget[i]); i++){}
8182
 if( zTarget[i]!=':' ) return 0;
8283
 nCode = i;
8384
 if( nCode==4 && strncmp(zTarget,"wiki",4)==0 ) return 0;
8485
 zPage = zTarget + nCode + 1;
8586
 nPage = (int)strlen(zPage);
86
- db_prepare(&q,
87
+ db_static_prepare(&q,
8788
 "SELECT json_extract(value,'$.base'),"
8889
 " json_extract(value,'$.hash'),"
8990
 " json_extract(value,'$.wiki')"
90
- " FROM config WHERE name=lower('interwiki:%.*q')",
91
- nCode, zTarget);
91
+ " FROM config WHERE name=lower($name)"
92
+ );
93
+ zName = mprintf("interwiki:%.*s", nCode, zTarget);
94
+ db_bind_text(&q, "$name", zName);
9295
 while( db_step(&q)==SQLITE_ROW ){
9396
 const char *zBase = db_column_text(&q,0);
9497
 if( zBase==0 || zBase[0]==0 ) break;
9598
 if( nPage==0 || zPage[0]=='/' ){
9699
 /* Path */
@@ -108,11 +111,12 @@
108111
 zUrl = mprintf("%s%s%s", zBase, zWiki, zPage);
109112
 }
110113
 }
111114
 break;
112115
 }
113
- db_finalize(&q);
116
+ db_reset(&q);
117
+ free(zName);
114118
 return zUrl;
115119
 }
116120
117121
 /*
118122
 ** If hyperlink target zTarget begins with an interwiki tag that ought
119123
--- src/interwiki.c
+++ src/interwiki.c
@@ -74,23 +74,26 @@
74 int nCode;
75 int i;
76 const char *zPage;
77 int nPage;
78 char *zUrl = 0;
79 Stmt q;
 
80 for(i=0; fossil_isalnum(zTarget[i]); i++){}
81 if( zTarget[i]!=':' ) return 0;
82 nCode = i;
83 if( nCode==4 && strncmp(zTarget,"wiki",4)==0 ) return 0;
84 zPage = zTarget + nCode + 1;
85 nPage = (int)strlen(zPage);
86 db_prepare(&q,
87 "SELECT json_extract(value,'$.base'),"
88 " json_extract(value,'$.hash'),"
89 " json_extract(value,'$.wiki')"
90 " FROM config WHERE name=lower('interwiki:%.*q')",
91 nCode, zTarget);
 
 
92 while( db_step(&q)==SQLITE_ROW ){
93 const char *zBase = db_column_text(&q,0);
94 if( zBase==0 || zBase[0]==0 ) break;
95 if( nPage==0 || zPage[0]=='/' ){
96 /* Path */
@@ -108,11 +111,12 @@
108 zUrl = mprintf("%s%s%s", zBase, zWiki, zPage);
109 }
110 }
111 break;
112 }
113 db_finalize(&q);
 
114 return zUrl;
115 }
116
117 /*
118 ** If hyperlink target zTarget begins with an interwiki tag that ought
119
--- src/interwiki.c
+++ src/interwiki.c
@@ -74,23 +74,26 @@
74 int nCode;
75 int i;
76 const char *zPage;
77 int nPage;
78 char *zUrl = 0;
79 char *zName;
80 static Stmt q;
81 for(i=0; fossil_isalnum(zTarget[i]); i++){}
82 if( zTarget[i]!=':' ) return 0;
83 nCode = i;
84 if( nCode==4 && strncmp(zTarget,"wiki",4)==0 ) return 0;
85 zPage = zTarget + nCode + 1;
86 nPage = (int)strlen(zPage);
87 db_static_prepare(&q,
88 "SELECT json_extract(value,'$.base'),"
89 " json_extract(value,'$.hash'),"
90 " json_extract(value,'$.wiki')"
91 " FROM config WHERE name=lower($name)"
92 );
93 zName = mprintf("interwiki:%.*s", nCode, zTarget);
94 db_bind_text(&q, "$name", zName);
95 while( db_step(&q)==SQLITE_ROW ){
96 const char *zBase = db_column_text(&q,0);
97 if( zBase==0 || zBase[0]==0 ) break;
98 if( nPage==0 || zPage[0]=='/' ){
99 /* Path */
@@ -108,11 +111,12 @@
111 zUrl = mprintf("%s%s%s", zBase, zWiki, zPage);
112 }
113 }
114 break;
115 }
116 db_reset(&q);
117 free(zName);
118 return zUrl;
119 }
120
121 /*
122 ** If hyperlink target zTarget begins with an interwiki tag that ought
123
M src/timeline.c
+18 -6
--- src/timeline.c
+++ src/timeline.c
@@ -224,10 +224,26 @@
224224
 */
225225
 int timeline_tableid(void){
226226
 static int id = 0;
227227
 return id++;
228228
 }
229
+
230
+/*
231
+** Return true if the checking identified by "rid" has a valid "closed"
232
+** tag.
233
+*/
234
+static int has_closed_tag(int rid){
235
+ static Stmt q;
236
+ int res = 0;
237
+ db_static_prepare(&q,
238
+ "SELECT 1 FROM tagxref WHERE rid=$rid AND tagid=%d AND tagtype>0",
239
+ TAG_CLOSED);
240
+ db_bind_int(&q, "$rid", rid);
241
+ res = db_step(&q)==SQLITE_ROW;
242
+ db_reset(&q);
243
+ return res;
244
+}
229245
230246
 /*
231247
 ** Output a timeline in the web format given a query. The query
232248
 ** should return these columns:
233249
 **
@@ -554,13 +570,11 @@
554570
 }
555571
 if( (tmFlags & TIMELINE_CLASSIC)!=0 ){
556572
 if( zType[0]=='c' ){
557573
 hyperlink_to_version(zUuid);
558574
 if( isLeaf ){
559
- if( db_exists("SELECT 1 FROM tagxref"
560
- " WHERE rid=%d AND tagid=%d AND tagtype>0",
561
- rid, TAG_CLOSED) ){
575
+ if( has_closed_tag(rid) ){
562576
 @ <span class="timelineLeaf">Closed-Leaf:</span>
563577
 }else{
564578
 @ <span class="timelineLeaf">Leaf:</span>
565579
 }
566580
 }
@@ -655,13 +669,11 @@
655669
 }
656670
657671
 if( (tmFlags & TIMELINE_CLASSIC)==0 ){
658672
 if( zType[0]=='c' ){
659673
 if( isLeaf ){
660
- if( db_exists("SELECT 1 FROM tagxref"
661
- " WHERE rid=%d AND tagid=%d AND tagtype>0",
662
- rid, TAG_CLOSED) ){
674
+ if( has_closed_tag(rid) ){
663675
 @ <span class='timelineLeaf'>Closed-Leaf</span>
664676
 }else{
665677
 @ <span class='timelineLeaf'>Leaf</span>
666678
 }
667679
 }
668680
--- src/timeline.c
+++ src/timeline.c
@@ -224,10 +224,26 @@
224 */
225 int timeline_tableid(void){
226 static int id = 0;
227 return id++;
228 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
229
230 /*
231 ** Output a timeline in the web format given a query. The query
232 ** should return these columns:
233 **
@@ -554,13 +570,11 @@
554 }
555 if( (tmFlags & TIMELINE_CLASSIC)!=0 ){
556 if( zType[0]=='c' ){
557 hyperlink_to_version(zUuid);
558 if( isLeaf ){
559 if( db_exists("SELECT 1 FROM tagxref"
560 " WHERE rid=%d AND tagid=%d AND tagtype>0",
561 rid, TAG_CLOSED) ){
562 @ <span class="timelineLeaf">Closed-Leaf:</span>
563 }else{
564 @ <span class="timelineLeaf">Leaf:</span>
565 }
566 }
@@ -655,13 +669,11 @@
655 }
656
657 if( (tmFlags & TIMELINE_CLASSIC)==0 ){
658 if( zType[0]=='c' ){
659 if( isLeaf ){
660 if( db_exists("SELECT 1 FROM tagxref"
661 " WHERE rid=%d AND tagid=%d AND tagtype>0",
662 rid, TAG_CLOSED) ){
663 @ <span class='timelineLeaf'>Closed-Leaf</span>
664 }else{
665 @ <span class='timelineLeaf'>Leaf</span>
666 }
667 }
668
--- src/timeline.c
+++ src/timeline.c
@@ -224,10 +224,26 @@
224 */
225 int timeline_tableid(void){
226 static int id = 0;
227 return id++;
228 }
229
230 /*
231 ** Return true if the checking identified by "rid" has a valid "closed"
232 ** tag.
233 */
234 static int has_closed_tag(int rid){
235 static Stmt q;
236 int res = 0;
237 db_static_prepare(&q,
238 "SELECT 1 FROM tagxref WHERE rid=$rid AND tagid=%d AND tagtype>0",
239 TAG_CLOSED);
240 db_bind_int(&q, "$rid", rid);
241 res = db_step(&q)==SQLITE_ROW;
242 db_reset(&q);
243 return res;
244 }
245
246 /*
247 ** Output a timeline in the web format given a query. The query
248 ** should return these columns:
249 **
@@ -554,13 +570,11 @@
570 }
571 if( (tmFlags & TIMELINE_CLASSIC)!=0 ){
572 if( zType[0]=='c' ){
573 hyperlink_to_version(zUuid);
574 if( isLeaf ){
575 if( has_closed_tag(rid) ){
 
 
576 @ <span class="timelineLeaf">Closed-Leaf:</span>
577 }else{
578 @ <span class="timelineLeaf">Leaf:</span>
579 }
580 }
@@ -655,13 +669,11 @@
669 }
670
671 if( (tmFlags & TIMELINE_CLASSIC)==0 ){
672 if( zType[0]=='c' ){
673 if( isLeaf ){
674 if( has_closed_tag(rid) ){
 
 
675 @ <span class='timelineLeaf'>Closed-Leaf</span>
676 }else{
677 @ <span class='timelineLeaf'>Leaf</span>
678 }
679 }
680

Keyboard Shortcuts

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