Yes, the Dolly Zoom (you'll recognize it when you see it) varies in effectiveness, just like every other cinematographic technique, on whether it actually does something for the story, conveys an effect, an internal disorientation or sudden shifting of the world.
There are some pretty awful examples here — but some spectacular ones (including Hitchcock's use in Vertigo, the Jaws one that it's also named after, the shot from Fellowship of the Ring, and, of course, the last one shown).
Full article: http://www.slashfilm.com/evolution-of-the-dolly-zoom-shot-video-essay/
(h/t +Les Jenkins)