I was HHonors Diamond from '07 - '11 at a previous job which I left in early-'10, moved to a different industry '10 - late '13 with far less travel and dropped back to Silver, then moved back to my original industry in late '13 at which point I jumped quickly back to Gold through fast track, and now ~1 year later I'm back at Diamond on stays with another ~4-5 stays planned for this year.
I found that my treatment as Gold in '13-'14 was NOT all that different than my treatment as Diamond in '07 - '11, save for some high-profile upgrades to President's Suites on 1 or 2 night stays. I don't see many (any, really) aspirational properties, stays skew towards the HI/HGI end of the spectrum, and only hit big-city down-town properties if it's related to a convention or trade show (save for Chicago where my only customer with a down-town office resides in the Sears Tower). I've received plenty of suite upgrades at HIs where it's not a defined/guaranteed benefit. I've received suites upgrades at HGI and ES when I only booked a studio. I've received a couple of suite upgrades at HGIs which I never got as a Diamond back in '07-'11. I received a huge corner suite upgrade at Palmer House, but it wasn't exec floor so I still didn't get lounge access, but other than breakfast didn't have time to use it anyway. Strangely, it seems that airport HIs are some of the loosest with upgrades, while airport/downtown Hiltons & DTs are some of the tightest... I've not received a single upgrade at an airport Hilton this year.
As for comparisson to the other programs, I think HHonors is a great fit for anyone that's a moderately heavy traveler and books middle-of-the-road properties/rates. It's much easier to make top-tier elite than Marriott which, in my eyes, is the only real direct competitor in quality low/mid-level properties with a serviceable footprint. If you're staying at much lower-end properties, Choice Hotels lets you earn very quickly with their near-constant promos for a free night with every 2 stays (and some newer Comfort Suites aren't too bad). On the same low-end, IHG also affords for quick earnings with frequent/ongoing points promos but lacks much in the way elite recognition and I couldn't imagine spending 30-40 nights a year in HIEs or even Crown Plazas. At the higher-end, SPG and Hyatt have great top-tier recognition BUT their property footprint is MUCH less than Hilton and Marriott. Marriott seems like a reasonable analog to HHonors BUT the high Night requirements and no reduced Stay requirement puts it out of reach for me personally, plus I've never been overly impressed with their low-end offerings (though there seem to be plenty of them around).
If you're paying $100-$125/night at a lot of metro-but-not-downtown properties with 30-40 nights/year, I think HHonors at EITHER the Gold or Diamond level is tough to beat... you can earn more in other programs at the cost of Elite recognition, but (to me) I'd rather give up a bit of earnings potential to make those 30-40 nights/year a bit more comfortable.