It's not terrible, it's just incredibly underwhelming. It's a movie where occasionally things happen. Sometimes fantasy things happen. But somehow in the most boring way possible. Eventually the movie ended.
Honestly, the TOS and TNG theme songs were different from the DS9 and Voyager themes. The former being more powerful, driving themes. The latter being more somber, contemplative themes.
It makes sense to change it up every once in a while. And honestly the more grounded, hopeful theme of Enterprise felt right for the theme of the show, setting up the eventual foundation of the United Federation of Planets at the end (spoilers, sorry).
Different ships have different size requirements. There's lots of space in a Galaxy class starship, so the tubes can afford to be bigger (and thus easier for people to crawl through). The Defiant is a very utilitarian ship, so space is at a premium and thus the tubes are smaller.