Google Public DNS is designed to speed up browsing, but depending on the kind of content you want to access, it can often have the reverse effect.
Dan Warne at APC points out that while Google Public DNS (or other alternative DNS services such as OpenDNS) can speed up general browsing to sites by resolving domain names faster, it will often have a damaging effect on services like iTunes and Windows Update which use Akamai's load balancing technology to spread server load. Using Google for lookups will convince Akamai that you're actually located in the US. It will also (as several Lifehacker readers have noted in the past) play havoc with any unmetered content your ISP offers (such as iView access), since it won't be routed through the appropriate servers.
My own feeling is that it's rarely worth messing with your DNS settings unless you're trying to solve a very specific problem (such as a misbehaving router). Alternative DNS servers can help alleviate against DNS hacks, but if your PC security is up to spec, that's not going to be an issue either. Got your own approach to maximising performance with these kinds of services? Tell us in the comments.