For me, usually the first 30 or so hours of a game are magical while you're still learning the mechanics, exploration is rewarding and dangerous, etc. Then usually quite suddenly, as if someone flicked a switch, things start going downhill pretty steeply.
One poster mentioned that the gameplay loops stay pretty much the same during the whole length of a single game, and after iteration number X things just start to get boring. It also doesn't help that the stories in most games are pretty meh, so once you're bored with the mechanics, there's little reason to keep playing.
So I guess I fall into the shorter is better camp. Right now I'm about to quit Morrowind, I really have to force myself to keep playing after about 50-60 hours. I'm at the third trial but have seen almost all settlements, can beat pretty much anybody, and I'm swimming in money... so what's the point really? The main quest is pretty unremarkable, and it just feels a like a drag. Probably I haven't seen everything the game has to offer, but the time investment/enjoyment ratio is too low for me to keep going, everything is happening so damned slowly...
Games that I played recently and found to be of the perfect length was Eye of the Beholder, Banner Saga and Grimrock 1. So yeah, I guess between 20 and 30 hours is the sweet spot for me.
It's not that I'm against long games, they just simply fail to keep up my interest. ELEX was quite good, but it took me 6 months to finish (overall I spent 110 hours on it, but in 1 month bursts with 1-2 month pauses inbetween). Same story, I was really hooked in the first 30-40 hours, it was the best thing ever, then it just got exponentially less and less fun after I could beat pretty much anybody and there was not much point in leveling up anymore.
Witcher 1 was quite alright lengthwise, maybe it could have been a bit shorter but it was almost perfect.
Divine Divinity - far too long
Nehrim - I quite liked this game and spent like 100 hours on it, but cutting the content down to 50-70% would have made it a much better game
I guess I just like to be able to finish a game in a month, in general.