Turning off the cloud service just does the syncing thing right? What about turning off the Apple Music service so that the only thing visible is your local content? That's what pisses me off the most.
Thought if I remember correctly, search was still showing it, which was a little annoying. But it depends on how much you search.
I'm currently using the service right now, so I can't really check if anything has changed since last year when I was doing it.
On MacOS I think it opens to the online home page, but I use it so infrequently I'm not sure. I pretty much only use it to buy music from iTunes.
I have over 8000 songs synced to my phone, 100% from local files on my computer.
I never activate the "library sync" BS that Apple tries to force on you, because historically it has replaced your copies with incorrect or "remastered" (AKA dynamically compressed to hell) versions from Apple servers.
I've even caught it switching "library sync" on without permission during an update.
"to find that content on the phone is difficult"
How? You tap Library, then Songs. There's all your music.