Every time I think I've talked myself into trying a chorus or something at church to change it up a little bit, add some variety, I watch this video and wonder...
I have a tuner, a compressor (that I really don't need as it's set so that I rarely even see a light go on) and a Radial JDI. It's been working of years. But then I get bored, or someone in the band gets new stuff and I feel left out.
SteveC, Adamixoye has a very good point. Case in point, toddefunk, tobyMac's bassist. Uses a slew of effects live and in the studio, and they are heard (at least on their Live albums). People can hear your effects, it does have some to do with how your church PA and your bass rig are set up. It also has to do with how the sound techs mix your team together, and how you ultimately sound within that mix.
Lastly, some effects can be heard, some may go unnoticed but it ultimately boils down to how what you're doing is contributing to the overall song composition. Example, one of my favorite YouTube bass players, Valter Kabas. He's got a mean skill for slap bass. I've listened to his tunes for years and never noticed that he used effects. The most obvious of which was a chorus effect. I had assumed his Tobias Growler had an amazing tone, which it does. It all mixed so well with his Micheal Jackson covers that it never even occurred to me that he was using effects in the first. Fact is he was, and it made his recordings sound that much better, the overall composition of the songs were that much tighter, polished, and better.
In my own personal experience, people notice when I break out the markbass super synth. The worship team loves it and the crowds hear it and enjoy it too. Ironically, in the song Sing Sing Sing, I pan to the neck pickup and kick on my OBD-3 for a really fun, upbeat, rock tone. Some think that's the guitarist, but if it helps the overall song composition, which I believe it does, then that's okay because that's what's important.