I bought MW2 on Thursday and I've finished the Single Player campaign just this afternoon. I think it took about 3 4hr sessions. Needless to say, not very long, but not many shooters are. Not to fussed on the storyline myself, which may just be because I never bought the first one.
It's a fairly pretty game, the only PS3 game I've seen that plays in 1080p other than Wipeout HD. There's a huge variety of landscapes too, and mission variety is fairly good too.
Multiplayer is where most people find the fun though. TBH, it's not revolutionary, but it does have a fair amount of depth. Mostly it's the upgrading perks and completing challenges that will occupy your time. If you don't know anybody you'd like to play with or against though, it might not be worth it.