Slow backup -especially deleting unwanted files and creating of bug report

Support
5

When you say “disabled reporting” do you mean the “Usage statistics” in the main menu settings? If so, that’s a different beast.

Usage statistics are automatic (hence the need to be able to disable) while the bug report is only created with user interaction so it should always work when requested, even if usage statistics are set to “None”.

Thanks for catching those! With a 467G source and the standard block size of 100kb your database probably has almost 4.9 million records keeping track of blocks.

I expect this is why the ExecuteScalarInt64 call took over 32 minutes to run. Looking at the code, it appears that whole step is solely to determine if a user has changed the block size.

While an important thing to check, that much time spent checking it makes no sense. I’m not big into SQLite so I could be wrong but I’m guessing that Select * could be replaced with something like SELECT EXISTS(SELECT "Size" FROM "Block" WHERE "Size" > ? LIMIT 1).

Not selecting “*” might speed things up right off the bat, but if not then this probably only improves performance when a block that is NOT size ? exists. In that case, it might make sense to instead add an index of Size (ordered by size).

Ugh - I just realized something… @kenkendk, the GetBlocksLargerThan() method is being used from Utility.cs to check if the block size has been changed - but it only checks for an INCREASE in size! What happens if I change to a smaller block size?

It looks to hopefully improve performance AND fix this loophole we might need something like this:
SELECT EXISTS(SELECT "Size" FROM "Block" WHERE "Size" != ? LIMIT 1)

The ExecuteNonQuery command is recording blocks that are flagged for deletion. Again, I’d have to poke around but I think this SQL could be optimized in a few ways including:

  • remove the DISTINCT (it’s likely more expensive then just returning all records) for the NOT IN check
  • rewriting the NOT IN bit as a NOT EXISTS (I think meaning sqlite won’t be trying to generate a temp table full of IDs to then be checked against NOT IN)
  • rewriting the whole INSERT as an INSERT OR IGNORE INTO "DeletedBlock" without the NOT IN check & joins (note that this assumes the joins are there solely to check if the record has already been inserted, assumes “Size” is the same if “Hash” is the same, and it doesn’t matter if “VolumeID” is duplicated)