aboutsummaryrefslogtreecommitdiff
path: root/xsig/src
diff options
context:
space:
mode:
authorGravatar Chris Xiong <chirs241097@gmail.com> 2022-09-27 00:50:35 -0400
committerGravatar Chris Xiong <chirs241097@gmail.com> 2022-09-27 00:50:35 -0400
commita2922bb8ba3b9388380f2b48c426680f23298512 (patch)
tree081197989a27e1d69cb42e12bac1eb40df33140a /xsig/src
parent617adf4090fbaac3a3b2d943548eeb6516fb9bdd (diff)
downloaddeduper-a2922bb8ba3b9388380f2b48c426680f23298512.tar.xz
1000x performance improvement for loading database.
So huge that it doesn't even sound real. I need to learn to write proper sql.
Diffstat (limited to 'xsig/src')
-rw-r--r--xsig/src/signature_db.cpp36
1 files changed, 19 insertions, 17 deletions
diff --git a/xsig/src/signature_db.cpp b/xsig/src/signature_db.cpp
index 105106b..907e815 100644
--- a/xsig/src/signature_db.cpp
+++ b/xsig/src/signature_db.cpp
@@ -588,30 +588,32 @@ void signature_db::group_similar()
std::vector<std::vector<size_t>> signature_db::groups_get()
{
- sqlite3_stmt *sto = nullptr;
- sqlite3_stmt *sti = nullptr;
- sqlite3_prepare_v2(p->db, "select distinct parent from dspar;", -1, &sto, 0);
- sqlite3_prepare_v2(p->db, "select id from dspar where parent = ?;", -1, &sti, 0);
+ sqlite3_stmt *st = nullptr;
+ sqlite3_prepare_v2(p->db, R"sql(
+ select id, dspar.parent, cnt from dspar
+ inner join (select parent, count(parent) as cnt from dspar group by parent) c
+ on dspar.parent = c.parent
+ where cnt > 1 order by dspar.parent;
+ )sql", -1, &st, 0);
std::vector<std::vector<size_t>> ret;
+ std::vector<size_t> cur;
+ size_t last_par = ~size_t(0);
while (1)
{
- int r = sqlite3_step(sto);
+ int r = sqlite3_step(st);
if (r != SQLITE_ROW) break;
- size_t dpar = (size_t)sqlite3_column_int(sto, 0);
- sqlite3_bind_int(sti, 1, dpar);
- std::vector<size_t> v;
- while (1)
+ size_t id = (size_t)sqlite3_column_int(st, 0);
+ size_t par = (size_t)sqlite3_column_int(st, 1);
+ if (par != last_par)
{
- int ri = sqlite3_step(sti);
- if (ri != SQLITE_ROW) break;
- size_t id = (size_t)sqlite3_column_int(sti, 0);
- v.push_back(id);
+ if (!cur.empty()) ret.push_back(cur);
+ cur.clear();
+ last_par = par;
}
- ret.push_back(v);
- sqlite3_reset(sti);
+ cur.push_back(id);
}
- sqlite3_finalize(sto);
- sqlite3_finalize(sti);
+ if (!cur.empty()) ret.push_back(cur);
+ sqlite3_finalize(st);
return ret;
}