The "Find Album Artwork" feature for iTunes is dependent on you having the correct tags. It uses the artist/album info (at the least) to look up the artwork from the iTunes store. Technically, if it's available in iTunes, there's artwork for it. You will just have to make sure the tags are correct and match up. If so, it will download 600x600 artwork. I've never had a problem with it retrieving artwork, and the cases where there was a problem, there was just a discrepancy between what the iTunes store had the artist listed as, compared to what I had tagged it as. The first example that comes to my mind is (hed)PE. There's like 20 different ways you can do that, but it doesn't matter-- the iTunes store has it officially listed under "(Hed) Planet Earth". It was not finding artwork for them... I did a quick look-up to find what would match, changed the artist name tag, and rechecked; boom... how you like that? Pulled it right down.
So if you're sloppy and you have all kinds of whack tags with missing, shoddy or incorrect info... you're gonna have bad luck with that feature. Personally, I'm very meticulous about keeping my music collection tagged properly. It's easy when you rip your own albums... just tag the stuff properly when you first rip the CD and you won't have to worry about it.
Personally, I have my whole CD collection ripped to FLAC with embedded CUE file. I use EAC to rip and create the CUE, then I use foobar2000 to embed the CUE file (which automatically will populate most of the tags)... and then I embed the artwork as well as keep a copy outside as folder.jpg so I get an album thumbnail on the folder... then I can transcode to any format/bitrate on the fly from the lossless source file. Transcoding to lossy formats to go on iDevices is very quick and it copies all of the tag data over, so you don't have any extra work to do. Have it transcode and dump to "Automatically add to iTunes" folder under the main iTunes folder and you don't even have to pull it over to iTunes.
There are lots of easy ways to manage your collection, but iTunes is not ideal, that's for sure... foobar2000 and certain plugins (and learning how to use it effectively to completely automate things that could take hours to do manually)-- that's the key to managing your collection effectively. If you don't already use it, you should be... it's insanely powerful (though very plain and simplistic looking if you leave it using the default look). That actually fooled me years back because I thought if it looks so old school and basic it's gotta be a POS. WRONG. Then I realized you can skin it to look like pretty much any way you want and the plugins add all kinds of extra functions if you need them.
Just my opinion, obviously. Point of it all is, though, the key to that function working in iTunes is CORRECT TAGGING. If you don't have that taken care of, yeah... look for another solution cause iTunes is going to make your life hell unless your music collection is already shaped up and proper.