That's what we're all told to expect. It's not really the case. Is there some loss? Yes, of course. Is it bad, or even noticeable? Everyone who is not a video geek would not notice a thing. Heck, even I do not notice anything amiss, except for scenes with a lot of motion. This is on large high-quality screens at the optimal viewing distance.
As far as I can tell, it's not the graininess that eats up the bit budget, it's motion, and complex fine patterns. Moderately grainy sources such as average film stock don't seem to have a large visual impact. But what does have an impact is either lots of motion in the field, or complex "islamic-art-like" patterns, or especially both together.
I've put even 3 h 45 min of film stock on a single-layer DVD, at 1080p24. Again, the vast majority of people could not tell the difference. Low motion scenes were perfect. There was some artifacting on high motion segments, but it's not the ugly, blocky, unnatural stuff you see on MPEG2. It was blending into the nearby shapes, more flowing and organic-like.
2 hours or less and I can't tell the difference from the source material at all (I haven't done A/B comparisons). Around 3 hours I can tell - although it's pretty minimal. The threshold must be somewhere in between. I would only think of splitting it, or using double-layer DVD, somewhere near 4 hours.
I use the latest x264, with MeGUI, and the AVCHD profile as defined in MeGUI, with the Film tuning, and 2-pass encoding in order to hit a pre-defined file size.
I remember the days when I was transcoding 480i DV tapes onto SVCD, with mpeg2enc. We've come a long way. :)