THE SPY WHO LOVED ME is a very mixed bag - good in parts, and excellent in places, but it's also overlong, with stretches of boredom and/or silliness. It's severely overrated - mostly by non-hardcore Bond fans, though, I've found. No way is it the best film of the series, or even close. Neither is one of the worst. It's a solid middle-of-the-pack outing that's somehow managed to acquire a glittering reputation (probably because it's undeniably the iconic image of the Moore era at its commercial peak).
Weeeell, I think that depends on individual taste and what one considers a hardcore or non-hardcore Bond fan is.
I think TSWLM was one of the most visually impressive Bond movies, if not 'the most' in that respect. Of all Bonds ever made, the one that pops into my mind straight away as the movie with overlong stretches of boredom is "Thunderball" with its underwater fighting scene. Talking about the scene when you can easily go to the kitchen while it is playing, make a bag of popcorn, feed the cat, come back to the screen and never feel you have missed anything important. 
