Dave: Thanks; I overlooked the "offset" value incrementing by 6/25s in each instance. What you did gives me some ideas to pursue tomorrow. E.g, for the "BBCSW_AMR_POST CLIENT CUT 7" project's CurrentVersion.fcpevent file (which contains the SQLite database) I will export the .csv of the ZCOLLECTION table. Then I'll run the repair option, then re-export that table and use Beyond Compare to diff those and see what it fixed.
The utility I use to examine the SQLite databases is SQLPro for SQLite. Unfortunately, most of the tables within CurrentVersion.fcpevent are difficult to interpret, and one contains binary data, but ZCOLLECTION has some vaguely understandable items.:
Yes, with BBEdit I don't think I could go through those gigantic XML files.
Agreed, it takes a long time to load. Now I know why: the tables are gigantic and vastly disproportionate to the timeline size and complexity. This examination is actually a useful endeavor.
James, that's a good point. Unlike project duplicates, snapshots freeze the state of all compound and multicam clips in a project. Obviously, that state information must be stored somewhere. That includes every edit and every effect on every clip. But for compound & multicam clips, a snapshot severs the inheritance to the parent clips, so that likely entails more state information stored (in the SQL database). It is possible there's an exponential performance cost if making snapshots of complex timelines which contain lots of compound and/or multicam clips.
There is no question the stack traces show the FCP thread accessing the SQLite database is bogged down. I did many different spindumps during beachball periods when opening events in your library. The SQLite database thread was bogged down in each case, and the UI thread was apparently waiting on it, thus causing the beachball.
I have that library on the internal SSD of my M1 Ultra Mac Studio. Even then it is very slow to open events or do searches.
Merely examining the number of projects, and length and superficial complexity of the timelines would not reveal the problem cause in an obvious way. In this case a tip-off is the slow opening.
I have never internally examined the before & after SQL state following a snapshot, but I'll do that tomorrow and try to quantify the parameters and boundaries.
James, if the other projects *and* snapshots are deleted, it's possible it might get faster. However I'm not sure what cleanup and garbage collection algorithms are involved for the SQL tables. I will also examine that tomorrow.