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.
I've only ever used Apple Music with local content. On iOS the only indication to me that non-local content is even possible is the radio tab at the bottom of the screen.
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.
Sadly it doesn't quite go away even when you've turned everything off: https://davids.town/dear-apple-please-fix-ios-music
That's what I meant by cloud services. It hides the Apple Music part so it defaults to your local stuff.
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.