Fossil SCM

Performance improvement by caching prepared statements when computing ancestors and descendents of a check-in.

drh 2011-05-12 01:08 trunk
Commit dcc68b46b29a096b6a01976bbdc902bbc345acf7
Parent 64aa186ae4bc41c…
1 file changed +26 -11
~ src/descendants.c
M src/descendants.c
+26 -11
--- src/descendants.c
+++ src/descendants.c
@@ -159,60 +159,75 @@
159159
 ** the "ok" table.
160160
 */
161161
 void compute_ancestors(int rid, int N){
162162
 Bag seen;
163163
 PQueue queue;
164
+ Stmt ins;
165
+ Stmt q;
164166
 bag_init(&seen);
165167
 pqueue_init(&queue);
166168
 bag_insert(&seen, rid);
167169
 pqueue_insert(&queue, rid, 0.0, 0);
170
+ db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171
+ db_prepare(&q,
172
+ "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173
+ " WHERE a.cid=:rid"
174
+ );
168175
 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
169
- Stmt q;
170
- db_multi_exec("INSERT OR IGNORE INTO ok VALUES(%d)", rid);
171
- db_prepare(&q,
172
- "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173
- " WHERE a.cid=%d", rid
174
- );
176
+ db_bind_int(&ins, ":rid", rid);
177
+ db_step(&ins);
178
+ db_reset(&ins);
179
+ db_bind_int(&q, ":rid", rid);
175180
 while( db_step(&q)==SQLITE_ROW ){
176181
 int pid = db_column_int(&q, 0);
177182
 double mtime = db_column_double(&q, 1);
178183
 if( bag_insert(&seen, pid) ){
179184
 pqueue_insert(&queue, pid, -mtime, 0);
180185
 }
181186
 }
182
- db_finalize(&q);
187
+ db_reset(&q);
183188
 }
184189
 bag_clear(&seen);
185190
 pqueue_clear(&queue);
191
+ db_finalize(&ins);
192
+ db_finalize(&q);
186193
 }
187194
188195
 /*
189196
 ** Load the record ID rid and up to N-1 closest descendants into
190197
 ** the "ok" table.
191198
 */
192199
 void compute_descendants(int rid, int N){
193200
 Bag seen;
194201
 PQueue queue;
202
+ Stmt ins;
203
+ Stmt q;
204
+
195205
 bag_init(&seen);
196206
 pqueue_init(&queue);
197207
 bag_insert(&seen, rid);
198208
 pqueue_insert(&queue, rid, 0.0, 0);
209
+ db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
210
+ db_prepare(&q, "SELECT cid, mtime FROM plink WHERE pid=:rid");
199211
 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
200
- Stmt q;
201
- db_multi_exec("INSERT OR IGNORE INTO ok VALUES(%d)", rid);
202
- db_prepare(&q,"SELECT cid, mtime FROM plink WHERE pid=%d", rid);
212
+ db_bind_int(&ins, ":rid", rid);
213
+ db_step(&ins);
214
+ db_reset(&ins);
215
+ db_bind_int(&q, ":rid", rid);
203216
 while( db_step(&q)==SQLITE_ROW ){
204217
 int pid = db_column_int(&q, 0);
205218
 double mtime = db_column_double(&q, 1);
206219
 if( bag_insert(&seen, pid) ){
207220
 pqueue_insert(&queue, pid, mtime, 0);
208221
 }
209222
 }
210
- db_finalize(&q);
223
+ db_reset(&q);
211224
 }
212225
 bag_clear(&seen);
213226
 pqueue_clear(&queue);
227
+ db_finalize(&ins);
228
+ db_finalize(&q);
214229
 }
215230
216231
 /*
217232
 ** COMMAND: descendants
218233
 **
219234
--- src/descendants.c
+++ src/descendants.c
@@ -159,60 +159,75 @@
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N){
162 Bag seen;
163 PQueue queue;
 
 
164 bag_init(&seen);
165 pqueue_init(&queue);
166 bag_insert(&seen, rid);
167 pqueue_insert(&queue, rid, 0.0, 0);
 
 
 
 
 
168 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
169 Stmt q;
170 db_multi_exec("INSERT OR IGNORE INTO ok VALUES(%d)", rid);
171 db_prepare(&q,
172 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173 " WHERE a.cid=%d", rid
174 );
175 while( db_step(&q)==SQLITE_ROW ){
176 int pid = db_column_int(&q, 0);
177 double mtime = db_column_double(&q, 1);
178 if( bag_insert(&seen, pid) ){
179 pqueue_insert(&queue, pid, -mtime, 0);
180 }
181 }
182 db_finalize(&q);
183 }
184 bag_clear(&seen);
185 pqueue_clear(&queue);
 
 
186 }
187
188 /*
189 ** Load the record ID rid and up to N-1 closest descendants into
190 ** the "ok" table.
191 */
192 void compute_descendants(int rid, int N){
193 Bag seen;
194 PQueue queue;
 
 
 
195 bag_init(&seen);
196 pqueue_init(&queue);
197 bag_insert(&seen, rid);
198 pqueue_insert(&queue, rid, 0.0, 0);
 
 
199 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
200 Stmt q;
201 db_multi_exec("INSERT OR IGNORE INTO ok VALUES(%d)", rid);
202 db_prepare(&q,"SELECT cid, mtime FROM plink WHERE pid=%d", rid);
 
203 while( db_step(&q)==SQLITE_ROW ){
204 int pid = db_column_int(&q, 0);
205 double mtime = db_column_double(&q, 1);
206 if( bag_insert(&seen, pid) ){
207 pqueue_insert(&queue, pid, mtime, 0);
208 }
209 }
210 db_finalize(&q);
211 }
212 bag_clear(&seen);
213 pqueue_clear(&queue);
 
 
214 }
215
216 /*
217 ** COMMAND: descendants
218 **
219
--- src/descendants.c
+++ src/descendants.c
@@ -159,60 +159,75 @@
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N){
162 Bag seen;
163 PQueue queue;
164 Stmt ins;
165 Stmt q;
166 bag_init(&seen);
167 pqueue_init(&queue);
168 bag_insert(&seen, rid);
169 pqueue_insert(&queue, rid, 0.0, 0);
170 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171 db_prepare(&q,
172 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173 " WHERE a.cid=:rid"
174 );
175 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
176 db_bind_int(&ins, ":rid", rid);
177 db_step(&ins);
178 db_reset(&ins);
179 db_bind_int(&q, ":rid", rid);
 
 
180 while( db_step(&q)==SQLITE_ROW ){
181 int pid = db_column_int(&q, 0);
182 double mtime = db_column_double(&q, 1);
183 if( bag_insert(&seen, pid) ){
184 pqueue_insert(&queue, pid, -mtime, 0);
185 }
186 }
187 db_reset(&q);
188 }
189 bag_clear(&seen);
190 pqueue_clear(&queue);
191 db_finalize(&ins);
192 db_finalize(&q);
193 }
194
195 /*
196 ** Load the record ID rid and up to N-1 closest descendants into
197 ** the "ok" table.
198 */
199 void compute_descendants(int rid, int N){
200 Bag seen;
201 PQueue queue;
202 Stmt ins;
203 Stmt q;
204
205 bag_init(&seen);
206 pqueue_init(&queue);
207 bag_insert(&seen, rid);
208 pqueue_insert(&queue, rid, 0.0, 0);
209 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
210 db_prepare(&q, "SELECT cid, mtime FROM plink WHERE pid=:rid");
211 while( (N--)>0 && (rid = pqueue_extract(&queue, 0))!=0 ){
212 db_bind_int(&ins, ":rid", rid);
213 db_step(&ins);
214 db_reset(&ins);
215 db_bind_int(&q, ":rid", rid);
216 while( db_step(&q)==SQLITE_ROW ){
217 int pid = db_column_int(&q, 0);
218 double mtime = db_column_double(&q, 1);
219 if( bag_insert(&seen, pid) ){
220 pqueue_insert(&queue, pid, mtime, 0);
221 }
222 }
223 db_reset(&q);
224 }
225 bag_clear(&seen);
226 pqueue_clear(&queue);
227 db_finalize(&ins);
228 db_finalize(&q);
229 }
230
231 /*
232 ** COMMAND: descendants
233 **
234

Keyboard Shortcuts

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