The answer to my issue may be obvious to some but it wasn't to me and therefore may not be obvious to others too. Yes, the new Macs may be extraordinarily fast and able to run multiple 8k timelines concurrently ... but only if they're running off the same SSD. And that SSD better be humungous. HDDs just don't cut it, even with Thunderbolt 4 connectivity. The performance improvement from a 2017 Intel iMac to a M1 Ultra Mac Studio using the same HDD was minimal. I eventually came up with 3 options to fix my issue:
Option 1: Don't create optimised video files on import. Only optimise the media you need for a specific edit and then delete the optimised files once the edit is completed. This way you may be able to contain the size of your library to fit on the SSD of your Mac and get all the benefits associated with the faster chips and drive. Depending on your original media you may even be able to edit without optimising, although it seems export will be much slower. Certainly don't shoot in ProRes - the files will be too big for any SSD. This gets a but messy and adds extra work rather than working off a fully optimised media but it solves the HDD dilemma.
Option 2: Keep the original and optimised media on the HDD and the Library files on the Mac SSD. This seemed to speed up the editing process and at least meant all the optimised media was retained. Less hassle than Option 1 but slower responses times as the HDD is still being accessed over Thunderbolt 4.
Option 3: Buy a 8TB SSD, stick it in an enclosure and link it to the Mac via Thunderbolt 4. Then just run your Library from the external SSD. Very expensive but the improvement in editing speeds has been enormous. I can finally see why the Mac Studio M1 Ultra is the beast it's meant to be. Still a little slower than using an internal SSD but cheaper than buying the 8TB SSD from Apple and I now have the equivalent of 12TB of SSD. I can now edit as I want to, albeit at a huge additional cost.
Good job I'm not filming in 8K ...