Theme songs are important for broadcast, calling people to watch a show they like or interesting new viewers. As such bad theme songs can effect viewer numbers and help cut a marginal show. For example I quite liked enterprise, but that opening theme was terrible, and I imagine contributed to it's demise.
Since I don't watch broadcasts I just skip themes that annoy me, no biggie. Even the best themes can get annoying if your sitting down to watch a whole season.
Short is good, e.g. Breaking Bad. Different each time is good too, Banshee delivers a different set of images during each opening credits. The subconscious can process information while your doing something else, so the right opening credits could potentially be designed to affect your viewing experience, though I doubt banshee is anywhere near that well thought out.
Bonus topic: outro songs. Always appreciated.