I've got a quad core 2.93ghz i7 workstation, which is essentially 8 cores, with the right software. I've also got an 11.6" MacBook Air, which is equipped with a 1.4ghz dual-core CPU. The workstation currently has standard HHDs, and the MacBook Air has an SSD. For 90% of the stuff I do (Photoshop, MS Office, Web, Flash, etc.), my MacBook Air blows the desktop away. With the SSD, it's just snappy and responsive. The thing wakes up and is online, after sleeping, in about two seconds. And I can honestly say that there's rarely a time when I've thought "I wish my 1.4ghz CPU was faster. The fact is that most of the computers we have today are plenty fast enough to do what we need to do. If anything, today's desktop computers are I/O, and not CPU-bound. So, getting a fast SSD will make a big difference.
Having said that, there are times when a faster processor does make a big difference. I rip a lot of video and audio, and my little MacBook Air is ill-equipped for that sort of thing. While converting a Blu-Ray movie might take my quad/eight core i7 10 minutes, it could take my little MacBook Air several hours.
10 cores is interesting for servers, especially blades. If it's power/heat efficient, it can help reduce the power consumption and footprint for datacenters. However, that sort of system really requires a fast bus & I/O, to keep up. We may be a decade away of getting there with today's desktops. Besides, the software will have to be writen to take advantage of it. On the server side, especially with virtuialization, that's pretty much here today. On the desktop side, no idea when.