Whatever you're doing, Natural Waterfalls is probably an amazing addition. And come to think of it, Natural Waterfalls claims to have a water mod built in that I haven't tried out yet.
I used RW2 for a while, then liked water for enb, and I tried cathedral water and liked it too. There's synthesis patches for Water for ENB and RW2 so you can auto patch your load order.
Simplicity of Sea by wskeever is my choice right now. I'm running it on top of Cathedral Waters with CW's .esp disabled.
Feels like the best of both RW2 and Water for ENB with no performance hit to speak of. It's missing RW2's effects like waterfall splashes and beach waves, but it's a million times more compatible with everything. AND it's got Water for ENB's excellent colors, while having somewhat better effects than it (I really don't like Water for ENB's waterfalls).
Here's a random pic that shows how SoS looks in unflattering conditions with no ENB, and I think it looks pretty great: https://i.imgur.com/gj4C0NY.jpg
It's been a hot second since I modded Skyrim, but Realistic Water 2 was always my pick! I used several other Cathedral mods though (Landscapes and Weathers) – Cathedral Water not making cell edits is certainly an appealing feature too.