Personally, I liked the way they handled it in the 1996 animated series, at least in the first episode. It should be painful, because obviously that sort of thing would be incredibly painful, and at the same time I liked how they showed Bruce resisting and ultimately losing. Something like that would be fine by me. And in The Avengers the first transformation was slow and extremely painful because he was resisting, but the second one was consensual and so a lot faster and less painful, so including elements of that would be good, too.
As for this debate on whether or not the Hulk is a release of tension or not... I say yes and no. Indeed, in some ways it's a release, as anyone who has bottled up their anger and then unleashed it can attest to. Getting really angry about something at work and then coming home to punch the crap out of a punching bag does feel pretty good, lol. However, if the transformation is due to some other adrenaline-causing event, say fear instead of anger, then it's probably less fun. At the same time, it'd still be painful and scary because Bruce would be losing control and afraid of hurting others.
Like another said, the Hulk is more than just a physical transformation, but a psychological one, too. I like the idea of transforming into the Hulk being both frightening (because a)you're losing control of your body and b)you might hurt/kill someone, especially someone you care about) and a guilty pleasure for Bruce, because in some ways it's also dropping the mask and letting out his repressed anger.
Also, I imagine transforming into the Hulk would not only be all these above things, but a little like being put under or falling asleep. Have you ever been given anesthesia or just been really really tired and tried staying awake? It's hard, and I feel it must feel a little like that for Bruce except instead of passing out or falling asleep he blacks out and turns into the Hulk.