The only Mega Dungeon I have liked is Undermountain. The first version, not the new piece of junk.
After that Castle Greyhawk was a bit too long. So is Temple of Elemental Evil.
I like Necromancers Tomb of Abysthor, Lost City of Barakus, and Rappan Athuk.
I think the real key is to have multiple reasons, and multiple things going on inn the Dungeon environment to make it much more dynamic.
When your going into a huge complex for one reason, and have to go through and explore the 500 rooms and fight the 3,000 creatures just to reach that one goal, thats when it gets bad.
Which is why I enjoyed Undermountain, Rappan Athuk, Tomb of Abysthor, and Lost City of Barakus. I had multiple reasons for the party to go in multiple times.
Tomb of Horrors was "just the right length" for a one goal dungeon.