Library stalling when clicking on events/opening projects

joema wrote:

DaveM wrote:

...I recall that Tangier Clarke had a similar issue with multicam clips (MCs) turning grey (and some clips inside of the MCs). I couldn't find any mention of whether he had found a solution, or not.

I don't remember that specific symptom. He's had multiple performance issues which I've worked on a lot. I currently believe the source of his issues are different from James' case. XCode Instruments traces I took while running his library implied it was a slowdown related to FCP single-thread processing of complex timeline metadata. However at that time I didn't have the understanding of this new "huge XML / huge SQL table" problem.

I'm about 70% certain the two problems have different sources, but I need to go back and check Tangier's library again.

Sorry, but I think I confused things.

The multicam issue I mentioned is just a side-effect of James's effort to "de-clutter" his Library. It isn't directly related to the spurious rows of repeated metadata or James initial issue...

Joe, thanks for posting the link to the screenshots of the database tables. When you look at the number of items at the bottom of the last two screenshots, and compare them to the number of items for "CUT 7" shown in the FCP Timeline Index (1453), there is a stark difference. Of course, I may be misunderstanding things...

DaveM wrote:

When you look at the number of items at the bottom of the last two screenshots, and compare them to the number of items for "CUT 7" shown in the FCP Timeline Index (1453), there is a stark difference. Of course, I may be misunderstanding things...

Yes, agreed. The last two screen shots were a count of unique values of those items in two different columns of the ZCOLLECTION table. The total rows in that table was about 5 million. 

Each clip, each effect and each edit will usually cause several rows to be inserted to the tables ZCOLLECTION and ZCOLLECTIONMD. There is also a table Z_3CHILDCOLLECTIONS that apparently tracks parent/child clip relationship.

 It would not be uncommon for a project with 1400 clips to have 200k rows in those tables. But I've never seen any project produce 5 million rows.

I added two more tables to this page. joema.smugmug.com/FCP-Hang-Metadata-Study/n-F53QqF/

I haven't before studied the Z_3CHILDCOLLECTIONS table but this one seems abnormal. One value was repeated over 600k times. This might imply some exponential increase in parent/child mapping. It might be related to having complex projects with lots of compound clips, then doing a lot of snapshots. Those snapshots sever the parent/child relationship, causing lots of new database writes to create standalone metadata (hence larger SQL tables) for the now-orphaned clips. But I'm not sure that by itself would be enough to cause this.

I did some more tests today. There is definitely an issue if you have lots of compound and/or multicam clips, then take serial project snapshots -- IOW create snapshot 1 from project 1, then open snapshot 1 and edit that, then create snapshot 2 from snapshot 1, etc. This causes exponential growth in the SQL database. Within a few cycles it will cause rapid onset of a performance problem.

This mainly happens if doing sequential snapshots of projects that contain compound clips and or multicam clips. In general compound clips are more common than multicam clips. If the compound clips are multiply nested, it will happen faster.

Given this understanding, my advice is do not use nested compound clips, and under no conditions take serial snapshots of a project containing those. Rather use the command "duplicate project".  If a snapshot is needed, take one of the current project and continue working on that project, not the snapshot. 

joema wrote:

Given this understanding, my advice is do not use nested compound clips, and under no conditions take serial snapshots of a project containing those. Rather use the command "duplicate project". If a snapshot is needed, take one of the current project and continue working on that project, not the snapshot.

Thanks for this, Joe.  You never cease to amaze.