It all depends on what you do with the system. In terms of CPU use, I mainly game, the other stuff don't take much processor power. I used to have Athlon 1.2Ghz, and was bottlenecking my x1650 tremendously. Then something hit me that I'm behind the times and I upgraded to Athlon XP Barton 2600+ just 3 months ago. I know, I was way behind the party.
I thought it was running pretty good compared to my old Thunderbird. Then a month later I got a chance to upgrade to Athlon 64 3200+, but that time didnt' really feel much faster. I think the A64 is about equivalent with x1650 I had.
Just a month ago I got a great deal to jump on the Core2Duo wagon, and now I have a E4500 running at 3.0Ghz, and also got x1950 Pro. Now it's x1950 Pro being the bottleneck. Unless I'm gaming though, for day to day work, it doesn't feel too faster, but I can feel that it does do things a split second faster.
I think the most tremendous difference was when I went from Tbird 1.2Ghz to AthlonXP 2.0, then going up 200mhz to Athlon64 didnt' do much. Even going up to 3.0Ghz doesn't feel that much faster EXCEPT in games. So you'll likely see a difference in games if you have a powerful enough card, your 8800 would be in that category. Mainly I do not upgrade within the same class if I can help it, because an extra few hundred Mhz isn't going to make a difference. Usually it's the architecture change that truly shines with processors.
Lasthitlarry, you already have 8800GTS, so you should stick with it, and upgrade the system, but i think you're better off getting a Phenom if you have AM2. If not, then get the best CPU you can get for939, or just ditch the 939 board and get Core2Duo. Overall though, my experience tells me that same class upgarde, ie. 3600+ to 5200+ wont' be enough to make your computing experience to feel like you have a new computer.