I tried Avast some time ago and it totally chewed up all my system's resources but maybe the later versions are better or I didn't have it configured in the best way.I'm with that, which is why I use Avast, its currently sitting in memory using about 30 megs, the other 'security suites' will chew upto a hundred.
AVG (I have the latest free version) doesn't have any noticeable effect on my system performance in it's normal protection mode. It certainly does when It's running a system scan, which I do once a day but at night. Spyware doctor as well has a noticeable effect when it is running an active scan, which again I schedule during times when I'm not using the machine. It's normal protection mode works in the background with no hassles or performance degradation. ZoneAlarm is a great free firewall that has to learn for a few days and will give you lots of pop ups in the beginning, but after that is is just there and works great.
The one performance issue I have with both AVG and ZoneAlarm is that they cause my machine to take three or four minutes longer at startup. Other than that they work great.
As Buksi said:
There is no AV product that is far better or worse than any other, they all do the same thing. Just use what you like and what you're comfortable with.