I'd say it depends on the sort of riding. A stiff frame is ideal for technical comp riding and gives you full confidence in wheel placement and instant power transmission but won't necessarily be overly 'comfortable'- it would be a sharp tool, built for a purpose. However, for a more rounded frame for use on streets and just general trials riding, I think a degree of flex is both forgiving (to components and the body) and also more 'comfortable'. Obviously you don't want it being so flexy so as to be unstable but a little 'give' in certain directions wouldn't be bad.
I guess that's part of the reasoning behind curvy tubes- a very stiff rear end for instant power transmission and confidence on the back wheel but flex and a little 'give' up front.