If a movie is going to have a title sequence, it needs to be interesting and actually introduce the story. I absolutely despise James Bond opening sequences because it's just 3-4 minutes of random visuals that serve no purpose. As much as I liked "Scott Pilgrim vs. The World," the title sequence is annoying. I don't want to watch 3 minutes of people's names.
Whatever you think of the movie, I have to say that I really enjoyed "Star Trek: Insurrection's" title sequence. It helped establish the setting, while simultaneously crediting the people involved.
What about First Contact? Without the title sequence we wouldn't have that awesome music setting up the mood and the very first scene would lose impact.