You’re right about this… it’s a many-to-one relationship, which I didn’t consider. I need to ponder this a bit more.
However, I am certain that we cut the time in half on the expensive query. Excerpted from the log above:
2023-06-14 13:16:37 -07 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExecuteNonQuery]: ExecuteNonQuery: INSERT INTO "DeletedBlock" ("Hash", "Size", "VolumeID") SELECT "Hash", "Size", "VolumeID" FROM "Block" WHERE "ID" NOT IN (SELECT DISTINCT "BlockID" AS "BlockID" FROM "BlocksetEntry" UNION SELECT DISTINCT "ID" FROM "Block", "BlocklistHash" WHERE "Block"."Hash" = "BlocklistHash"."Hash") took 0:00:41:31.935
...
2023-06-14 13:58:37 -07 - [Profiling-Timer.Finished-Duplicati.Library.Main.Database.ExtensionMethods-ExecuteNonQuery]: ExecuteNonQuery: DELETE FROM "Block" WHERE "ID" NOT IN (SELECT DISTINCT "BlockID" FROM "BlocksetEntry" UNION SELECT DISTINCT "ID" FROM "Block", "BlocklistHash" WHERE "Block"."Hash" = "BlocklistHash"."Hash") took 0:00:42:00.328
Those are the same query… the first time we’re inserting the results into DeletedBlock, and the second time we’re deleting the results. Both instances take ~40 minutes to execute in this case. In oracle, you could do this:
DECLARE
cursor BLOCKS is
SELECT "Hash", "Size", "VolumeID", "ID" FROM "Block" WHERE "ID" NOT IN (SELECT DISTINCT "BlockID" AS "BlockID" FROM "BlocksetEntry" UNION SELECT DISTINCT "ID" FROM "Block", "BlocklistHash" WHERE "Block"."Hash" = "BlocklistHash"."Hash";
BEGIN
for i in BLOCKS loop
INSERT INTO "DeletedBlock" ("Hash", "Size", "VolumeID")
values (i.Hash, i.Size, i.VolumeID);
delete Block
where ID = i.ID;
end loop;
END;
I don’t know what the equivalent would be in sqlite, or if it’s even possible directly in sqlite. If it’s not possible, then you could certainly do the same thing with the code that’s controlling the whole thing.