I do think the fact that sex or sexual desire is not part of a relationship should not lessen the value of that connection or make anyone take it less seriously.
But the opposite is true as well, imho. I don't consider anyone's love less beautiful just because they also want to bang the person they're in love with.
With Sherlock and Irene, my personal impression is that they do have a lot of sexual tension but I can't really imagine them meeting up just to have sex, I think they'd both find that way too boring. My headcanon is that they have a standing affair where they play intricate, dangerous games with more or less overt sexual undertones but hardly ever touch each other.
As for John, there's zero evidence on screen for me that he's into Sherlock sexually and I take his word for it that he's not gay. And I still see his and Sherlock's as a love story and one of my favorite love stories too.
If some fans think it would be a better story if the boys went to bed with each other, that's fine with me, and I feel sorry for them that they were perhaps disappointed. But just for myself, I'm perfectly happy with the way things turned out.