Fossil

Check-in [c7749bb0]
Login

Check-in [c7749bb0]

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

Overview
Comment:Use a CTE instead of querying the database in a loop when finding the start of a branch. This can bring significant speedup on some machines.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | start-of-branch-cte
Files: files | file ages | folders
SHA3-256: c7749bb0a397c8e8470d8cbb5b26f00e253e2c7e2390a981b0b7f1315fe022d7
User & Date: danield 2022-03-03 23:19:10
Original Comment: Use a CTE instead of querying the database in a loop when finding the start of a branch. This can bring significant speedup on some machines.
Context
2022-03-04
15:32
Fix an assertion failure. ... (check-in: d3625bd9 user: danield tags: start-of-branch-cte)
2022-03-03
23:19
Use a CTE instead of querying the database in a loop when finding the start of a branch. This can bring significant speedup on some machines. ... (check-in: c7749bb0 user: danield tags: start-of-branch-cte)
2022-03-02
02:00
Update the built-in SQLite to the latest trunk version that includes various performance enhancements. The purpose here is to test the recent SQLite enhancements in a real-world application. ... (check-in: ad744440 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/name.c.

︙︙
155
156
157
158
159
160
161


162
163
164






165
166



167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184

*/
int start_of_branch(int rid, int eType){
  Stmt q;
  int rc;
  int ans = rid;
  char *zBr = branch_of_rid(rid);
  db_prepare(&q,


    "SELECT pid, EXISTS(SELECT 1 FROM tagxref"
                       " WHERE tagid=%d AND tagtype>0"
                       "   AND value=%Q AND rid=plink.pid)"






    "  FROM plink"
    " WHERE cid=:cid AND isprim",



    TAG_BRANCH, zBr
  );
  fossil_free(zBr);
  do{
    db_reset(&q);
    db_bind_int(&q, ":cid", ans);
    rc = db_step(&q);
    if( rc!=SQLITE_ROW ) break;
    if( eType==1 && db_column_int(&q,1)==0 ) break;
    ans = db_column_int(&q, 0);
  }while( db_column_int(&q, 1)==1 && ans>0 );
  db_finalize(&q);
  if( eType==2 && ans>0 ){
    zBr = branch_of_rid(ans);
    ans = compute_youngest_ancestor_in_branch(rid, zBr);
    fossil_free(zBr);
  }
  return ans;








>
>
|
|
|
>
>
>
>
>
>
|
|
>
>
>
|


<
<
<
|
|
|
|
<








155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180



181
182
183
184

185
186
187
188
189
190
191

*/
int start_of_branch(int rid, int eType){
  Stmt q;
  int rc;
  int ans = rid;
  char *zBr = branch_of_rid(rid);
  db_prepare(&q,
    "WITH RECURSIVE"
    "  par(pid, ex, cnt) as ("
    "    SELECT pid, EXISTS(SELECT 1 FROM tagxref"
    "                        WHERE tagid=%d AND tagtype>0"
    "                          AND value=%Q AND rid=plink.pid), 1"
    "    FROM plink WHERE cid=%d AND isprim"
    "    UNION ALL "
    "    SELECT plink.pid, EXISTS(SELECT 1 FROM tagxref "
    "                              WHERE tagid=%d AND tagtype>0" 
    "                                AND value=%Q AND rid=plink.pid),"
    "           1+par.cnt"
    "      FROM plink, par"
    "     WHERE cid=par.pid AND isprim AND par.ex "
    "     LIMIT 100000 "
    "  )"
    " SELECT * FROM par WHERE ex=%d ORDER BY cnt DESC LIMIT 1",
    TAG_BRANCH, zBr, ans, TAG_BRANCH, zBr, eType%2
  );
  fossil_free(zBr);



  rc = db_step(&q);
  if( rc!=SQLITE_ROW ) return ans;

  ans = db_column_int(&q, 0);

  db_finalize(&q);
  if( eType==2 && ans>0 ){
    zBr = branch_of_rid(ans);
    ans = compute_youngest_ancestor_in_branch(rid, zBr);
    fossil_free(zBr);
  }
  return ans;
︙︙