The most important thing to reduce latency is the FSB speed, if you do everything in the box, this won't matter as such.
If you use a lot of soft synths you will notice a difference with a faster CPU, with the newer cores as someone pointed out it doesn't matter about Ghz as these processors can do a lot more cycles due to their large multipliers, but I assume you are still on the old intel sockets, and since you state you only want to upgrade the processor, that means you are stuck with the socket you have, a lot of people round here recommend the Q6600, I do too. I don't have any recommendations for an AMD socket.
But back to your original question, you will definitely see a difference with upgrading the CPU, imo this is the most important part of the machine (even more so with production work)