I've found just the opposite with avast, honestly. I swear by that program. Since installing it I have had zero infections. I have even disabled avast and run other, paid anti-virus and malware removal programs and they haven't found anything on my computer. I would say that most viruses are written to get past Norton than anything else, since almost every prebuilt computer in the world comes with that installed, and the average computer user barely knows how to double-click IE let alone update their protection to something other than Norton.
It's not just that viruses are written to get past Norton, there've been a few documented cases of Norton being written to ignore particular bits of spyware from corporations Symantec got a bit too friendly with.
I don't have links at the moment, but it's still a resource hog without all that.
AVG is reasonably friendly but not terribly light-weight and does have some problems now with viruses getting through. It used to be top-of-the-line, though.
Avast is quite nice and usually works alright.
I'm using Comodo at the moment and it's caught the few things that've tried to sneak in. I haven't been using it long enough to recommend it, but it seems alright.
One important thing is that installing an AV program after you have viruses usually doesn't work. Some viruses will block anti-virus websites, detect the installation and sabotage the program, or even replace it with a copy of the virus. If you know you're infected, use an online scanner to clean your system first, then install an AV locally once you're clean.